I'm sorry, I missed a JOIN on the second variation. It is:
SELECT u.id, u.name, u.imageURL, u.bio,
CASE
WHEN f.friend_id IS NOT NULL THEN 'isFriend'
WHEN s.to_id IS NOT NULL THEN 'hasSentRequest'
WHEN r.to_id IS NOT NULL THEN 'hasReceivedRequest'
ELSE 'none'
END AS 'friendStatus',
(SELECT COUNT(1) AS d
FROM friends f1
JOIN friends f2 ON f1.fiend_id = f2.friend_id
WHERE f1.user_id = 33 AND f2.user_id = u.id)
FROM users u
*LEFT OUTER JOIN friends f ON f.user_id = 33 AND f.friend_id = u.id
<http://u.id>*
LEFT OUTER JOIN friend_requests s ON s.to_id = 33 AND s.from_id = u.id
LEFT OUTER JOIN friend_requests r ON r.to_id = u.id AND r.from_id = 33
WHERE u.id != 33 AND u.name LIKE '%John%' ORDER BY u.name;
On Tue, Dec 9, 2014 at 10:15 AM, Robert DiFalco <robert.difa...@gmail.com>
wrote:
> I have users, friends, and friend_requests. I need a query that
> essentially returns a summary containing:
>
> * user (name, imageURL, bio, ...)
> * Friend status (relative to an active user)
> * Is the user a friend of the active user?
> * Has the user sent a friend request to the active user?
> * Has the user received a friend request from the active user?
> * # of mutualFriends
> * Exclude the active user from the result set.
>
> So I have mocked this up two ways but both have complicated query plans
> that will be problematic with large data sets. I'm thinking that my lack of
> deep SQL knowledge is making me miss the obvious choice.
>
> Here's my two query examples:
>
> SELECT u.id, u.name, u.imageURL, u.bio,
> CASE
> WHEN EXISTS(SELECT 1 FROM friends f WHERE f.user_id = 33 AND
> f.friend_id = u.id) THEN 'isFriend'
> WHEN EXISTS(SELECT 1 FROM friend_requests s WHERE s.to_id = 33 AND
> s.from_id = u.id) THEN 'hasSentRequest'
> WHEN EXISTS(SELECT 1 FROM friend_requests r WHERE r.to_id = u.id
> AND r.from_id = 33) THEN 'hasReceivedRequest'
> ELSE 'none'
> END AS "friendStatus",
> (SELECT COUNT(1)
> FROM friends f1
> JOIN friends f2 ON f1.friend_id = f2.friend_id
> WHERE f1.user_id = 33 AND f2.user_id = u.id) AS mutualFriends
> FROM users u
> WHERE u.id != 33 AND u.name LIKE 'John%' ORDER BY u.name;
>
> SELECT u.id, u.name, u.imageURL, u.bio,
> CASE
> WHEN f.friend_id IS NOT NULL THEN 'isFriend'
> WHEN s.to_id IS NOT NULL THEN 'hasSentRequest'
> WHEN r.to_id IS NOT NULL THEN 'hasReceivedRequest'
> ELSE 'none'
> END AS 'friendStatus',
> (SELECT COUNT(1) AS d
> FROM friends f1
> JOIN friends f2 ON f1.fiend_id = f2.friend_id
> WHERE f1.user_id = 33 AND f2.user_id = u.id)
> FROM users u
> LEFT OUTER JOIN friend_requests s ON s.to_id = 33 AND s.from_id = u.id
> LEFT OUTER JOIN friend_requests r ON r.to_id = u.id AND r.from_id = 33
> WHERE u.id != 33 AND u.name LIKE 'John%' ORDER BY u.name;
>
> 33 is just the id of the active user I am using for testing. The WHERE
> clause could be anything. I'm just using "u.name" here but I'm more
> concerned about the construction of the result set than the WHERE clause.
> These have more or less similar query plans, nothing that would change
> things factorially. Is this the best I can do or am I missing the obvious?
>
> Here are the tables:
>
>
> CREATE TABLE users (
> id BIGINT,
> name VARCHAR,
> imageURL VARCHAR
> created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> phone_natl BIGINT, /* National Phone Number */
> country_e164 SMALLINT, /* E164 country code */
> email VARCHAR(255),
> PRIMARY KEY (id),
> UNIQUE (email),
> UNIQUE (phone_natl, country_e164)
> );
>
>
> CREATE TABLE friends (
> user_id BIGINT,
> friend_id BIGINT,
> PRIMARY KEY (user_id, user_id),
> FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
> FOREIGN KEY (friend_id) REFERENCES users(id) ON DELETE CASCADE
> );
> CREATE INDEX idx_friends_friend ON friends(friend_id);
>
> CREATE TABLE friend_requests (
> from_id BIGINT,
> to_id BIGINT,
> created TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
> PRIMARY KEY (from_id, user_id),
> FOREIGN KEY (from_id) REFERENCES users(id) ON DELETE CASCADE,
> FOREIGN KEY (to_id) REFERENCES users(id) ON DELETE CASCADE
> );
> CREATE INDEX idx_friend_requests_to ON friend_requests(to_id);
>
> Let me know if you guys need anything else.
>
>
