How to loop Mysql based on a query

Question

I wonder if it is possible to create an event in the Mysql database, where every 5 minutes the database makes a query, where it returns all the order ids and based on each id I perform another query and the result of this query, insert into another table of the database.

With each id above, perform this query below by subtitling the order_id of WHERE Orders.order_id LIKE order_id for each id of the above query.

SELECT orders.order_id, orders.user_id, orders.email, products.url, SUM(plugin_table.product_unavailable) AS product_unavailable, SUM(plugin_table.added_to_cart) AS added_to_cart, SUM(plugin_table.ordered) AS ordered, SUM(plugin_table.plugin_imported) AS plugin_imported, SUM(plugin_table.price) AS price, SUM(plugin_table.recommendation_done) AS recommendation_done, SUM(plugin_table.recommended_size) AS recommended_size, SUM(plugin_table.plugin_opened) AS plugin_opened FROM new_intelligence.aggr_orders orders JOIN new_intelligence.aggr_products products JOIN new_intelligence.raw_plugin_usage plugin_table FORCE INDEX (SESSION_USER) ON plugin_table.product_id = products.product_id WHERE orders.order_id LIKE order_id AND plugin_table.domain = orders.domain AND plugin_table.user_id = orders.user_id GROUP BY products.url;

And the result of the above query insert into another table.

Answer 1

I didn’t quite understand what you need to do with the loop, but you could use some CURSOR in a PROCEDURE, since you would need to go through the result of a query.

About the event you can create one this way

DELIMITER $

CREATE EVENT `ev_nome_event`
    ON SCHEDULE EVERY 5 MINUTE
    STARTS '2019-03-02 00:05:00'
    ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Comentário'
    DO BEGIN
        -- Chamar a procedure que fará todo o processo;
    END$

After creating the event, you have to make sure that the event_scheduler variable is ON, you can check using:

SHOW VARIABLES;

And to connect the events:

SET GLOBAL event_scheduler = 1;

Answer 2

I ended up solving this problem the following way:

Note: I am using Mysql 5.6

DELIMITER |

CREATE EVENT aggr_all_details_of_orders  

ON SCHEDULE EVERY 5 MINUTE STARTS NOW() 

ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Join all order details and 
insert in the table aggr_all_orders_details' 

DO BEGIN

    DECLARE id_orders varchar(250);
    DECLARE order_domain varchar(250);
    DECLARE done INT DEFAULT FALSE;

    DECLARE cursor_order_detail CURSOR FOR 
        SELECT order_id, domain FROM new_intelligence.aggr_orders;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cursor_order_detail;

        run_orders: LOOP

            FETCH cursor_order_detail INTO id_orders, order_domain;

                IF done THEN
                    LEAVE run_orders;
                END IF;

                INSERT INTO new_intelligence.aggr_all_orders_details
                (
                 order_id,
                 product_id,
                 user_id, 
                 session_id,
                 email,
                 domain,
                 update_date,
                 recommended_size,
                 price,
                 recommendation_done,
                 product_unavailable, 
                 added_to_cart,
                 ordered,
                 plugin_imported,
                 plugin_opened
                )
                SELECT 
                    orders.order_id,
                    products.product_id,
                    orders.user_id,
                    orders.session_id,
                    orders.email,
                    orders.domain,
                    orders.update_date,
                    GROUP_CONCAT(plugin_table.recommended_size SEPARATOR ';') AS recommended_size,
                    SUM(plugin_table.price) AS price,
                    IF( SUM(plugin_table.recommendation_done) > 0, 1, 0) AS recommendation_done,
                    IF( SUM(plugin_table.product_unavailable) > 0, 1, 0) AS product_unavailable,
                    IF( SUM(plugin_table.added_to_cart) > 0, 1, 0) AS added_to_cart,
                    IF( SUM(plugin_table.ordered) > 0, 1, 0) AS ordered,
                    IF( SUM(plugin_table.plugin_imported) > 0, 1, 0) AS plugin_imported,
                    IF( SUM(plugin_table.plugin_opened) > 0, 1, 0) AS plugin_opened
                FROM
                    new_intelligence.aggr_orders orders
                        JOIN
                    new_intelligence.aggr_products products
                        JOIN
                    new_intelligence.raw_plugin_usage plugin_table FORCE INDEX (SESSION_USER) ON plugin_table.product_id = products.product_id
                WHERE
                orders.order_id LIKE id_orders
                AND plugin_table.domain = orders.domain
                AND plugin_table.user_id = orders.user_id
                AND orders.domain = order_domain
                GROUP BY products.url; 

        END LOOP;

    CLOSE cursor_order_detail;

    DEALLOCATE PREPARE cursor_order_detail;

    END |

    DELIMITER ;