On Tue, Dec 9, 2014 at 4:18 PM, Robert DiFalco <robert.difa...@gmail.com> wrote:
> 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. >> >> > Hello Robert, none of your schemas worked for me, here's a clean version CREATE TABLE users ( id BIGINT, name VARCHAR, imageURL VARCHAR, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, phone_natl BIGINT, country_e164 SMALLINT, 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, friend_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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (from_id, to_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); I may look into the query later but here's some thoughts * you want an index to speed up name searches on user.name, I suggest pg_trgm + ILIKE as a starting point. * you really expect more than a billion users? If not (or not in the near future) use INT instead. This will save you a significant amount of memory and eventually buy you time later. * as long as you don't hit the disk for the queries you'll be fine, so make sure you have enough memory or use read-slaves with smaller working sets.
