Merging two complex queries.

Question

What I ended up doing was taking two SQL queries and using the array_intersect() in PHP to filter out the results:

$sql1 = 'SELECT z.*, u.username, u.user_colour, u.username_clean, u.user_avatar, u.user_avatar_type
    FROM ' . ZEBRA_TABLE . ' z, ' . USERS_TABLE . ' u
    WHERE (( z.user_id = ' . $user->data['user_id'] . '
        AND z.friend = 1
        AND u.user_id = z.zebra_id )
            OR ( z.zebra_id = ' . $user->data['user_id'] . '
                AND z.friend = 1
                AND u.user_id = z.user_id ))
    ORDER BY u.username_clean ASC';

$sql2 = 'SELECT z.*, u.username, u.user_colour, u.username_clean, u.user_avatar, u.user_avatar_type
    FROM ' . ZEBRA_TABLE . ' z, ' . USERS_TABLE . ' u
    WHERE (( z.user_id = ' . $user_id . '
        AND z.friend = 1
        AND u.user_id = z.zebra_id )
            OR ( z.zebra_id = ' . $user_id . '
                AND z.friend = 1
                AND u.user_id = z.user_id ))
    ORDER BY u.username_clean ASC';

The structure of both queries are the same and the only difference is $user->data['user_id] (first person) is replaced with $user_id (second person) in the second query. I want to retrieve friends that both users have in common. Could anyone merge this into a single query so that I don't have to use two queries and call array_intersect()?

Answer 1

Well, you could always just subquery both:

$sql = 'SELECT a.* 
    FROM ('.$sql1.') AS a 
    JOIN ('.$sql2.') AS b ON a.user_id = b.user_id AND a.username = b.username';

You may want to add u.user_id to the field list of both queries u.user_id AS u_user_id then change the second join clause from a.username = b.username to a.u_user_id = b.u_user_id...

EDIT: Now that I really look at it closer, those two queries are almost identical... Why not just do something like this (replace the where clause to this):

WHERE z.friend = 1 
   AND (
       ( z.user_id = '.$user_id.' AND u.user_id = z.zebra_id )
        OR
       (z.zebra_id = '.$user_id.' AND u.user_id = z.user_id )
   ) AND (
       ( z.user_id = '.$user->data['user_id'].' AND u.user_id = z.zebra_id )
        OR
       (z.zebra_id = '.$user->data['user_id'].' AND u.user_id = z.user_id )
   ) 

That should give you the result of both queries intersected, and be faster since it can optimize better (hopefully)...

Oh, and they are in different where blocks because there's a few cases where z.user_id matches $user_id, but z.zebra_id matches $user->data['user_id']... So rather than list all the permutations, I just layed it out like this...

Answer 2

You could select users who are friends with both users by linking the user table to the zebra table twice:

SELECT u.username, u.user_colour, u.username_clean, u.user_avatar, u.user_avatar_type 
FROM users u
JOIN zebra z1 ON z1.friend=1 AND (
                   (u.user_id = z1.user_id AND z1.zebra_id = @user_id1)
                   OR (u.user_id = z1.zebra_id AND z1.user_id = @user_id1)
                 )
JOIN zebra z2 ON z2.friend=1 AND (
                   (u.user_id = z2.user_id AND z2.zebra_id = @user_id2)
                   OR (u.user_id = z2.zebra_id AND z2.user_id = @user_id2)
                 )
ORDER BY u.username_clean ASC

The JOIN takes all the rows from the users table, and all the rows from the zebra table, and looks for the combinations that satisfy the ON clause. In this case, the first join finds all users who are friends with @user_id1, the second join further restricts it to users who are also friends with @user_id2.

This query will perform much faster than using subqueries will. The query would be even faster if the zebra table stored friendships in both directions, allowing you to take more advantage of table indexes, and you could remove the OR portion of the ON clauses:

SELECT u.username, u.user_colour, u.username_clean, u.user_avatar, u.user_avatar_type 
FROM users u
JOIN zebra z1 ON u.user_id = z1.user_id AND z1.friend=1 AND z1.zebra_id = @user_id1
JOIN zebra z2 ON u.user_id = z2.user_id AND z2.friend=1 AND z2.zebra_id = @user_id2
ORDER BY u.username_clean ASC