5rHHYVu8

1SELECT 'SELECT * FROM event_task';
2SELECT * from event_task;
3SELECT 'SELECT event_id FROM event_task';
4SELECT event_id FROM event_task;
5SELECT 'SELECT * FROM event_task WHERE event_id = 1';
6SELECT * FROM event_task WHERE event_id = 1;
7SELECT 'end select';
8	
9mysql> call get_user_events(1);
10+--------------------------+
11| SELECT * FROM event_task |
12+--------------------------+
13| SELECT * FROM event_task |
14+--------------------------+
151 row in set (0.01 sec)
16
17+----+----------+---------+--------+----------+---------------------+---------------------+
18| id | event_id | user_id | task   | complete | created_at          | updated_at          |
19+----+----------+---------+--------+----------+---------------------+---------------------+
20|  1 |        1 |       1 | stuff  |        0 | 2012-08-30 00:00:00 | 2012-08-30 00:00:00 |
21|  2 |        1 |       2 | stuff2 |        1 | 2012-08-30 00:00:00 | 2012-08-30 00:00:00 |
22+----+----------+---------+--------+----------+---------------------+---------------------+
232 rows in set (0.01 sec)
24
25+---------------------------------+
26| SELECT event_id FROM event_task |
27+---------------------------------+
28| SELECT event_id FROM event_task |
29+---------------------------------+
301 row in set (0.01 sec)
31
32+----------+
33| event_id |
34+----------+
35|     NULL |
36|     NULL |
37+----------+
382 rows in set (0.01 sec)
39
40+---------------------------------------------+
41| SELECT * FROM event_task WHERE event_id = 1 |
42+---------------------------------------------+
43| SELECT * FROM event_task WHERE event_id = 1 |
44+---------------------------------------------+
451 row in set (0.01 sec)
46
47Empty set (0.01 sec)
48
49+------------+
50| end select |
51+------------+
52| end select |
53+------------+
541 row in set (0.01 sec)
55	
56SELECT 'SELECT user_id';
57SELECT user_id;
58	
59mysql> call get_user_events(1);
60+----------------+
61| SELECT user_id |
62+----------------+
63| SELECT user_id |
64+----------------+
651 row in set (0.00 sec)
66
67+---------+
68| user_id |
69+---------+
70|       1 |
71+---------+
721 row in set (0.00 sec)
73	
74DELIMITER $$
75DROP PROCEDURE IF EXISTS get_user_events$$
76CREATE PROCEDURE get_user_events (IN user_id int) 
77/*
78gets the user's events for the default page
79grabs: image location, event title, date, location, number of tasks completed / total, and updates
80*/
81BEGIN
82    DECLARE done TINYINT(1) DEFAULT FALSE;
83    DECLARE loop_cnt INT DEFAULT 0;
84    DECLARE num_rows INT DEFAULT 0;
85    DECLARE event_id INT;
86
87    DECLARE update_cur CURSOR FOR
88        SELECT DISTINCT id FROM aggregate;
89
90    DECLARE CONTINUE HANDLER FOR NOT FOUND
91        SET done = TRUE;
92
93    DROP TABLE IF EXISTS tasks;
94    DROP TABLE IF EXISTS aggregate;
95    DROP TABLE IF EXISTS updates;
96    CREATE TEMPORARY TABLE tasks (id int, event_id int, complete tinyint(1));
97    CREATE TEMPORARY TABLE aggregate (id int, picture_location varchar(255), name varchar(255), date timestamp, 
98        place varchar(255), num_tasks int, tasks_complete int, status_update varchar(5000));
99    CREATE TEMPORARY TABLE updates (event_id int, status_update varchar(255));
100
101    /*get basic event information*/
102    INSERT INTO aggregate (id, picture_location, name, date, place)
103    SELECT e.id,
104           e.picture_location, 
105           e.name, 
106           ed.date,
107           ep.place
108    FROM event e INNER JOIN event_user_map eum ON e.id = eum.event_id
109                 LEFT JOIN event_place ep ON e.id = ep.event_id AND ep.vote_final = 1
110                 LEFT JOIN event_date ed ON e.id = ed.event_id AND ed.vote_final = 1
111    WHERE eum.user_id = user_id;
112
113    /*grab the list of tasks so we can work with a small subset*/
114    INSERT INTO tasks 
115        SELECT id, event_id, complete 
116        FROM event_task 
117        WHERE event_id IN (SELECT DISTINCT id FROM aggregate);
118
119    SELECT 'SELECT * FROM event_task';
120    SELECT * from event_task;
121    SELECT 'SELECT event_id FROM event_task';
122    SELECT event_id FROM event_task;
123    SELECT 'SELECT * FROM event_task WHERE event_id = 1';
124    SELECT * FROM event_task WHERE event_id = 1;
125    SELECT 'SELECT event_id FROM (SELECT * FROM event_task) as A';
126    SELECT event_id FROM (SELECT * FROM event_task) as A;
127    SELECT 'end select';
128
129    /*gets tasks*/
130    UPDATE aggregate SET num_tasks = (SELECT COUNT(*) FROM tasks WHERE event_id = id);
131    UPDATE aggregate SET tasks_complete = (SELECT COUNT(*) FROM tasks WHERE event_id = id AND complete = 1);
132
133    /*get updates*/
134    OPEN update_cur;
135
136    read_loop: LOOP
137        FETCH update_cur INTO event_id;
138        IF done THEN
139            LEAVE read_loop;
140        END IF;
141
142        INSERT INTO updates
143            SELECT event_id, status_update FROM event_update ORDER BY updated_at limit 3;
144    END LOOP;
145
146    UPDATE aggregate SET status_update = (SELECT group_concat(status_update) FROM updates u WHERE u.event_id = id);
147
148    SELECT * FROM aggregate;
149
150END$$
151DELIMITER ;