CODEWITHSUNDEEP
phpsqlmysql
Chris Garson
Chris Garson

Reputation: 199

MySQL Subquery Reuse

Hey folks. I have a rather unwieldy sql query:

SELECT username, users.photo_url, fp, dp,users.vid, 
       GLength(LineStringFromWKB(LineString(AsBinary(PointFromText('POINT({$geolat} {$geolong})')), 
                                            AsBinary(location)))) AS distance
  FROM users 
    INNER JOIN venues ON users.vid = venues.vid
    LEFT JOIN deflects ON users.username = deflects.defender
  WHERE username NOT LIKE '{$mysql['username']}'
    AND username NOT LIKE '{$users['king']['username']}'
    AND venues.location IS NOT NULL
  HAVING 
    (distance <= (
      SELECT MAX(distance) AS max_distance 
        FROM (
            SELECT
                GLength(LineStringFromWKB(LineString(AsBinary(PointFromText('POINT({$geolat} {$geolong})')), 
                                                   AsBinary(location))))
                AS distance
              FROM users 
                INNER JOIN venues ON users.vid = venues.vid
                LEFT JOIN deflects ON users.username = deflects.defender
              WHERE users.fp = 0
                AND username NOT LIKE '{$mysql['username']}'
                AND username NOT LIKE '{$users['king']['username']}'
                AND venues.location IS NOT NULL
                AND deflects.dp IS NULL
              ORDER BY distance LIMIT 5
          ) AS unfrozen)
     OR vid = '{$vid}')
  ORDER BY distance

Right now I reuse a lot of the same query twice - in particular, I'd like to avoid doing that distance computation more than once - but I can't figure out how to do this. I'm using MySQL, so I don't think common table expressions are an option. Also, I ran into some trouble with temporary tables. Is there a way to phrase this query in such a way that I can reuse the distance computation?

Also, I'm aware that the way I'm calculating distance doesn't give true distance from the given geolat, geolong, but it's close enough for my purposes.

EDIT: And... here's what I got working, based almost entirely on Richard's response below:

SELECT username, distance, photo_url, vid, fp, dp
  FROM (
    SELECT username, photo_url, vid, fp, dp,
        @d := distance AS distance,
        @c :=   if(fp = 0
                   AND dp IS NULL
                   AND @d>=@max, @c+1, @c),
        @max := if(fp = 0
                   AND dp IS NULL
                   AND @d>=@max
                   AND @c <= 5, @d, @max)
    FROM (SELECT @max:=0, @d:=null, @c:=0) AS MaxTally
      INNER JOIN (
          SELECT username, photo_url, users.vid, users.fp, deflects.dp,
                 GLength(LineStringFromWKB(LineString(AsBinary(PointFromText('POINT({$geolat} {$geolong})')),
                                                      AsBinary(location))))
                 AS distance
            FROM users
              INNER JOIN venues ON users.vid = venues.vid
              LEFT JOIN deflects ON users.username = deflects.defender
            WHERE username NOT LIKE '{$mysql['username']}'
              AND username NOT LIKE '{$users['king']['username']}'
              AND venues.location IS NOT NULL
            ORDER BY distance
        ) AllUsers
  ) AllUsersWithMaxTally
  WHERE vid = '{$vid}' OR distance <= @max
  ORDER BY distance

Thanks Richard!

Upvotes: 3

Views: 644

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

Pseudocode - I'll fix the code later if required, but this may get you started enough to get to the answer yourself. MySQL allows you to do CRAZY things!

SELECT
    username,
    distance
FROM
(
SELECT
    username,
    @d:=distance AS distance,
    @c   := if(fp = 0
               AND dp IS NULL
               AND @d>=@max, @c+1, @c),
    @max := if(fp = 0
               AND dp IS NULL
               AND @d>=@max
               AND @c <= 5, @d, @max) MaxOf5Dist
FROM (select @max:=-1000, @d:=null, @c:=0) M
INNER JOIN (
SELECT
    username, # others taken out for brevity
    users.fp, deflects.dp,
    GLength(LineStringFromWKB(LineString(AsBinary(
            PointFromText('POINT({$geolat} {$geolong})')), AsBinary(location))))
            AS distance
FROM users
CROSS JOIN venues ON users.vid = venues.vid
LEFT JOIN deflects ON users.username = deflects.defender
WHERE username NOT LIKE '{$mysql['username']}'
  AND username NOT LIKE '{$users['king']['username']}'
  AND venues.location IS NOT NULL
ORDER BY distance
) X
) Y
WHERE vid = '{$vid}' OR distance <= MaxOf5Dist
ORDER BY distance

Upvotes: 2

swedishhousemafia
swedishhousemafia

Reputation: 129

Use Stored Procedures.. http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Upvotes: 0

Related Questions