CODEWITHSUNDEEP
mysqlsqlstored-procedures
Austin White
Austin White

Reputation: 148

Combine Results from MySQL Stored Procedure

I have a stored procedure that looks up a user's preferences based on the fact that they can select multiple locations that they want to search in. So, for each location, the query needs to lookup results within the area. Currently, the procedure only returns 1 result. Also, I would like to be able to implement some kind of ranking system in order to sort the results as one combined feed. I realize that if I'm using a while loop, that will group the results by location, which I do not want. What is the best way to restructure this procedure so that results from 3 queries are meshed and allow me the flexibility of ordering the results?

DELIMITER $$

    DROP PROCEDURE IF EXISTS `geodist` $$
    CREATE PROCEDURE geodist (IN userid INT) BEGIN
    DECLARE mylon DOUBLE;  DECLARE mylat DOUBLE;
    DECLARE lon1 FLOAT;  DECLARE lon2 FLOAT;
    DECLARE lat1 FLOAT; DECLARE lat2 FLOAT;
    DECLARE dist INT;
    DECLARE no_more_locations INT; DECLARE l_location_count INT;

    -- get the original lon and lat for the userid
    DECLARE location_cursor CURSOR FOR
        SELECT geo_lat, geo_long, distance
        FROM activity_location_preferences
        INNER JOIN activity_preferences ON activity_location_preferences.preference_id = activity_preferences.id
        WHERE activity_preferences.user_id = userid
        ORDER BY activity_preferences.id ASC
        LIMIT 3;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_locations = 1;

    SET no_more_locations = 0;
    OPEN location_cursor;
    location_loop:WHILE(no_more_locations = 0) DO
        FETCH location_cursor INTO mylat, mylon, dist;
        IF no_more_locations = 1 THEN
            LEAVE location_loop;
        END IF;
        SET l_location_count = l_location_count+1;

        -- calculate lon and lat for the rectangle:
        SET lon1 = mylon - dist / abs(cos(radians(mylat)) * 69);
        SET lon2 = mylon + dist / abs(cos(radians(mylat)) * 69);
        SET lat1 = mylat - (dist / 69);
        SET lat2 = mylat + (dist / 69);

        -- run the query:
        SELECT `id`, `user_id`, `activity`, `geo_lat`, `geo_long`, `data`, `date_created`,  
        7912 * ASIN(SQRT(POWER(SIN((mylat - activity_logs.geo_lat) * pi()/180 / 2), 2) +  
        COS(mylat * pi()/180) *  COS(activity_logs.geo_lat * pi()/180) *  POWER(SIN((mylon - activity_logs.geo_long) * pi()/180 / 2), 2)  )) as distance
        FROM activity_logs
        WHERE 1
        AND activity_logs.geo_long BETWEEN lon1 AND lon2 
        AND activity_logs.geo_lat BETWEEN lat1 AND lat2
        HAVING distance < dist ORDER BY date_created DESC LIMIT 100;

    END WHILE location_loop;
    CLOSE location_cursor;
    SET no_more_locations = 0;

END $$

DELIMITER ;

Upvotes: 4

Views: 2692

Answers (1)

JohnL
JohnL

Reputation: 216

The easiest way that comes to mind is to create a temp table to store the 3 results and then query the 3 results from the temp table as your final result from the procedure.

Another way would be to rewrite all the queries into one query with a subquery, and that is the way I probably would do it myself if I was writing this from scratch as it would be faster than using a temp table.

Upvotes: 1

Related Questions