Welcome Guest, Not a member yet? Register   Sign In
SQL help for users and friends
#1

[eluser]bapobap[/eluser]
Hi there,

I was wondering if anyone can help. I have a users table, then a friends table. In the friends table, I record the users ID and the user ID of the persons friend in the friends table, the table looks like this:

Code:
id
user_id
friend_id
created

There are the different types of relationship, mutual friends (both users have added each other to the table), following (the user has friended another but the other hasn't reciprocated) and followers (users who have friended the user but haven't been friended back).

I'm looking for three succinct SQL queries to produce the results (without any PHP code to merge the data), which I believe should be possible, though it's way out of my expertise range, my brain hurts thinking about it. I would only be able to provide one variable for each query, which is the user id of the user in question. Using a JOIN, I can then find the username etc of each user.

If anyone thinks they can help, please do, though if you think you can help but don't have the time to spare I'd be willing to pay for your time.

Thanks anyone!
#2

[eluser]Teks[/eluser]
SQL is not my forte, but I'll try to give you a hand.

Until you get more comfortable with SQL itself, I would try to steer clear off JOIN queries. You will find, that (although not as succinct) you can accomplish the same result if you just carefully build your SELECT statements, step by step. More importantly, the different database program and engines give different levels of upport for the different types of JOIN queries, so it is easier to get a consistent result across all database backends if you use the simplest form possible.

Simple SELECT statements, by themselves, are not that hard to understand. In its most basic form - which everyone can grasp - it is:

SELECT list of table fields FROM table name WHERE field operator value

* list of table fields is generally in the format column_name, or if more than one table is involved, table_name.column_name. An asterix (*) can be used when you wish to include all fields of a table
* table name should list the tables that the fields in list of table fields belong to
* field should be the name of one of the fields in one of the tables listed in the FROM clause

This will return a table, containing the fields you requested, and including only the records matching the filter specified in the WHERE part of the statement.

So, let's suppose that you have a table named "Users" in your database, with the following fields:

* id -> int, primary key
* username -> string, not null, indexed
* email -> string, not null, indexed
* first_name -> string
* last_name -> string

And let us suppose that you wanted to find a user whose id is 25:

Code:
SELECT * FROM Users WHERE id=25

If you are dealing with strings, you should enclose your value in single quotes. So, if you were looking for all user whose first name is "john", you could use:

Code:
SELECT * from Users WHERE first_name='john'

One of the most useful operators for strings is the LIKE operator, which allows you to use expressions in your search. For instance, if you wanted to find all users who had an email address in hotmail - that is, all users whose 'email' ends in "hotmail.com":

Code:
SELECT * from Users WHERE email LIKE '%hotmail.com'

In your example, however, you have 2 tables that relate to each other. Your second table, "Friends", if I understood correctly, is like this:

* id -> int, primary key
* user_id -> int, foreign key (= id from table Users), not null.
* friend_id -> int, foreign key (= id from table Users), not null
* created -> datetime

It seems to me, that a record in the "Friends" table indicates that the user shown in "user_id" has 'tagged' the user in "friend_id" as a friend. Now, let's say that you need to find all people that a user whose id is 25 has tagged as friends:

Code:
SELECT * FROM Friends WHERE user_id=25

This would give you a table containing all the fields from the "Friends" table. What you really want, however, is the fields from the Users table - username, email, etc. You can do that, just by listing the tables and columns you want specifically, like this:

Code:
SELECT Users.username, Users.email, Friends.created FROM Users, Friends WHERE Friends.user_id=25

However, SQL has a 'smarter' way of doing this.

The 'S' in SQL stands for 'structured' - and this means that you can structure your queries by using the results of one query as part of another. The results of a SELECT query make up a table, and you can use that table pretty much anywhere in another SELECT query. This gives us a way to nest one query inside another - and at the same time create devilishly complicated statements.

I'll show you how that works, by introducing the very important IN operator:

Code:
Query 1 - "People User 25 Has Tagged":

SELECT * FROM Users WHERE id IN (SELECT friend_id FROM Friends WHERE user_id=25)

SQL will first evaluate the part in brackets - which is a SELECT statement, that will generate a single-column table of ids, out of the Friends table. The IN operator will then compare the id of every record in the Users table, with the entire result table. If it finds a match, then the record is included.

So, how about the opposite? How could you get a listing of all people who have tagged user 25 as their friend?

Code:
Query 2 - "People Who Have Tagged User 25":

SELECT * FROM Users WHERE id IN (SELECT user_id FROM Friends WHERE friend_id=25)

And last of all, how about if you wanted to get a list of the people who user 25 has tagged as friends, and who have *also* reciprocated, and tagged user 25 as their friend, too?

The 2 nested select sub-queries from above, each gives us a separate list of ids. If you think about it, what we are looking for is all the ids that appear in the first list, and which also appear in the second list. We want an intersection of both lists. We can get this intersection, like this:

Code:
Query 3 - "People Who Have Reciprocally Tagged User 25":

SELECT * FROM Users WHERE id IN (
SELECT friend_id FROM Friends WHERE user_id=25
INTERSECT
SELECT user_id FROM Friends WHERE friend_id=25)

I do not know if this is totally correct, or whether it will work for your specific database implementation, but I hope it might help steer you in the right direction.
#3

[eluser]bapobap[/eluser]
Hi Teks,

I haven't tried your solutions yet but thank you very much for taking the time to give me such a detailed and complete response. JOIN's are about as far as I go, I'd never heard of IN or INTERSECT until now. I'll have to read more about them!

Maybe I'm not understanding correctly but can you give any tips on how to remove from the results? For instance, the People Who Have Tagged User 25 example. Would that produce a result of every user ID who had tagged user 25 as their friend? Is it then possible that in that list, that user 25 has tagged a few of them back, making them a mutual friend? How would I make sure that the results show people who have tagged user 25 as their friend but where there definitely isn't a record anywhere in the table going the other way? That seems to be like an INTERSECT but maybe like the negation of an INTERSECT? The same for the "People User 25 Has Tagged", in the results, some of those user IDs could have "friended" user 25, so I'd need to remove them from the results.

Really appreciate it Teks, at least now I know I really didn't know how to achieve this and can look into IN and INTERSECT.
#4

[eluser]ray73864[/eluser]
for that you will want to look at left and right joins
#5

[eluser]Teks[/eluser]
[quote author="bapobap" date="1228057317"]
[...]
Maybe I'm not understanding correctly but can you give any tips on how to remove from the results? For instance, the People Who Have Tagged User 25 example. Would that produce a result of every user ID who had tagged user 25 as their friend?
[/quote]

Yes, "Query 2" above should give you a list of everyone who has tagged user 25 as a friend.

Quote:Is it then possible that in that list, that user 25 has tagged a few of them back, making them a mutual friend?

Yes, that is correct - the list will contain 'reciprocal' friends as well.

Quote:How would I make sure that the results show people who have tagged user 25 as their friend but where there definitely isn't a record anywhere in the table going the other way? That seems to be like an INTERSECT but maybe like the negation of an INTERSECT?

So, you need the list from Query 2 to exclude the friends who are reciprocal. We know that Query 3 above should give you a list of the people who have a reciprocal relationship with user 25. So, in a sense, what you need is:

[table of results from Query 2] - [table of results from Query 3]

In SQL, just like INTERSECT, there is another great operator, called EXCEPT, which allows you to subtract one table from another. Both tables need to be identical - ie., have the same fields. If you do "tableA EXCEPT tableB" then you get tableA minus any records that also exist in tableB:
Code:
Query 4 - "People Who Have Tagged User 25 - And Have NOT Been Tagged Back by User 25":

SELECT * FROM Users WHERE id IN (SELECT user_id FROM Friends WHERE friend_id=25)
EXCEPT
(SELECT * FROM Users WHERE id IN (
SELECT friend_id FROM Friends WHERE user_id=25
INTERSECT
SELECT user_id FROM Friends WHERE friend_id=25))

And finally:
Quote:The same for the "People User 25 Has Tagged", in the results, some of those user IDs could have "friended" user 25, so I'd need to remove them from the results.

This sounds like [table of results from Query 1] - [table of results from Query 3] - again, using EXCEPT, just like above:

Code:
Query 5 - "People User 25 Has Tagged - And Have NOT Tagged User 25 Back":

SELECT * FROM Users WHERE id IN (SELECT friend_id FROM Friends WHERE user_id=25)
EXCEPT
(SELECT * FROM Users WHERE id IN (
SELECT friend_id FROM Friends WHERE user_id=25
INTERSECT
SELECT user_id FROM Friends WHERE friend_id=25))

Depending on your database implementation, you may have to play around with the brackets around the SELECT statements, to get things to evaluate in the correct order - but the major databases (MySQL, SQLite, PostgreSQL) should understand the statements above, as they are. Again, I can't guarantee that it will work - haven't tried it myself - but hope for the best for you. Let us know how it turns out.
#6

[eluser]bapobap[/eluser]
Great Teks, thanks so much, your advice has been very helpful, hopefully others will find it useful too.




Theme © iAndrew 2016 - Forum software by © MyBB