SQL help (possible a join)

[callumacrae]

I have two tables:

feed:
- id
- user_id
- content

friends:
- id
- user_id
- user2_id

Say (for example) that friends contains the following data:

0, 1, 4
1, 1, 6
2, 1, 12
3, 6, 1
3, 4, 1
3, 1, 15
3, 12, 6

feed contains the following data:

0, 1, 'Lorem Ipsum'
1, 2, 'Hello world!'
2, 1, 'What?'
3, 4, 'Bla'
4, 7, 'Test'

I want to get all items from the feed that are from friends. For example, if the current user id is 1, I want to get all the feed info from users 4, 6, 12 and 15 (and themselves):

0, 1, 'Lorem Ipsum'
2, 1, 'What?'
3, 4, 'Bla'


How would I do this using SQL? It should be portable SQL, usable in MySQL, Firebird etc.

~Callum

 

[gomarc]

Hi callumacrae,


I would run 2 separate queries. The first one will get the user results:

SELECT id, user_id, content
FROM feed
WHERE feed.user_id = ?

And the second one for the user's friends:

SELECT feed.id,
       feed.user_id,
       feed.content
FROM feed
INNER JOIN friends
ON friends.user2_id = feed.user_id
WHERE friends.user_id = ?
ORDER BY feed.id ASC
LIMIT 10

Save both of the results into an array and there you go.

 

[callumacrae]

Can I do an OR on the inner join?

~Callum

 

[descalzo]

SELECT feed.id , feed.user_id, feed.content 
    FROM feed INNER JOIN friends 
    ON feed.user_id = friends.user2_id  
    WHERE friends.user_id = current_user ;

should work

Edit: facepalm.png ... thirty lashes with a wet lasagna noodle for me for not reading gomarc's post more closely.

 

[callumacrae]

SELECT feed.id,
       feed.user_id,
       feed.content
FROM feed
INNER JOIN friends
ON friends.user2_id = feed.user_id
WHERE friends.user_id = ?
ORDER BY feed.id ASC
LIMIT 10

:/

Well this is awkward...

~Callum

 

[gomarc]

Can I do an OR on the inner join?

~Callum

Yes you can use the SQL UNION operator to combine the results of two queries into a composite result.

It will be something like this:

SELECT feed.id, feed.user_id, feed.content
FROM feed
WHERE feed.user_id = ?
UNION
SELECT feed.id, feed.user_id, feed.content
FROM feed
INNER JOIN friends ON friends.user2_id = feed.user_id
WHERE friends.user_id = ?

 

[callumacrae]

Will that return them in proper order or will it be all the users stuff and then their friends stuff?

~Callum

 

[callumacrae]

Will that return them in proper order or will it be all the users stuff and then their friends stuff?

~Callum

 

[gomarc]

callumacrae,

As posted, the query has no defined order. You need to add 'ORDER BY'.

If you want it by feed.id ASC:

SELECT feed.id, feed.user_id, feed.content
FROM feed
WHERE feed.user_id = ?
UNION
SELECT feed.id, feed.user_id, feed.content
FROM feed
INNER JOIN friends ON friends.user2_id = feed.user_id
WHERE friends.user_id = ?
ORDER BY id ASC

 

[callumacrae]

Will that affect the entire union or just the second statement?

 

[gomarc]

Will that affect the entire union or just the second statement?

All of it. You can test yourself by changing ASC to DESC.

 

[denzil]

Callum. I would just like to point out that you could potentially drop the ID table from friends, and set your primary key to the combination of user_id and user2_id.

 

[callumacrae]

How would I go about doing that? I've known that it's possible, but never actually tried it

 

[misson]

How would I go about doing that? I've known that it's possible, but never actually tried it

ALTER TABLE friends DROP COLUMN id;
ALTER TABLE friends ADD PRIMARY KEY (user_id, user2_id);

To do it from scratch:

CREATE TABLE friends (
    user_id INT UNSIGNED,  -- Match dfn of users(id); should only be UNSIGNED if users (id)
    user2_id INT UNSIGNED, --  is unsigned, which it should be if auto-incremented
    PRIMARY KEY (user_id, user2_id),
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (user2_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
) Engine=InnoDB; -- in MySQL, must set Engine for foreign keys to work.

Some RDBMSs (e.g. MySQL and SQL Server) support REFERENCES attributes, meaning you can place the FOREIGN KEY constraints in the column definition:

    user_id INT UNSIGNED REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,

However, this is less portable, and MySQL will ignore the foreign key attributes if there are any problems (such as column type mismatches).

Functionally, primary keys are simply NOT NULL columns with a UNIQUE index. If you already have a primary key defined on a table, you can define additional non-null, unique columns.

Can I do an OR on the inner join?

Yes, you can use OR in the join condition, or you could do it in the WHERE clause:

SELECT DISTINCT feed.*
  FROM feed
    JOIN friends ON feed.user_id=friends.user2_id
  WHERE friends.user_id=? OR friends.user2_id=?
  ORDER BY feed.id
;

As for which query to use, you'll need to profile each (after making sure you have suitable indices defined on your tables) to see which performs better. Every RDBMS has it's own method of profiling. In MySQL, for example, you'd use EXPLAIN.