CODEWITHSUNDEEP
phpmysql
user3806984
user3806984

Reputation: 13

Retrieving data from multiple tables and listing them in a list

I'm having some problems retrieving data from two tables and then listing them. I'd like to list the user's feed posts and their likes activity all in one.

What I'm attempting to make: List BOTH feeds and user's Like activity in an ACTIVITY WALL.

So it should output like (ordered by timestamp desc):

My current SQL:

SELECT * FROM feeds,likes WHERE feeds.deleted!=0 or likes.deleted!=0 ORDER BY feeds.timestamp, likes.timestamp

However, my problem is I have no idea how to link both tables, since the IDs in my 'feeds' differ from those in 'likes'

Upvotes: 1

Views: 137

Answers (4)

spencer7593
spencer7593

Reputation: 108510

To combine the two sets, you can use a UNION ALL set operator.

Something like this:

SELECT f.timestamp    AS `timestamp`
     , 'feed'         AS `src`
     , f.feed_id      AS `id`
     , f.feed_content AS `content`
  FROM feeds f
 WHERE f.deleted!=0
 UNION ALL
SELECT l.timestamp    AS `timestamp`
     , 'like'         AS `src`
     , l.like_id      AS `id`
     , l.note         AS `content`
  FROM likes l
 WHERE l.deleted!=0
 ORDER BY 1 DESC

Note the the queries (on either side of the UNION ALL operator) need to match, in terms of the number of columns returned, and the datatype of each column.

To accommodate differences, such as extra columns returned from one table, but not from the other, you can add literal expressions in place of the "missing" columns.

The return of the extra src column is one way we can use to distinguish which query a row was returned by. It's not mandatory to return such a column, but it's something I often find useful. (The src column could be removed from each query, if it's not useful for your use case.)

Note that it's also possible to combine the results from more than two queries in this way, we'd just add another UNION ALL and another query.

The column names in the combined resultset are determined from the first query. The column names and aliases in the second query are ignored.

The ORDER BY applies to the entire set, and follows the last select.

Upvotes: 3

Alexander R.
Alexander R.

Reputation: 1756

I think, it's better to use 3rd table, say, "actions", and insert to it real actions. Then just select rows from this table, joined to "posts" & "users" table.

When user posts articles, o likes an article, insert corresponding row to "actions" table.

actions table:

|id|action_name|user_id|post_id|   date   |
 1   posted        3      3      5/7/2014
 2   liked         5      3      5/7/2014
 3   liked         4      3      6/7/2014
 4   posted        5      6      7/7/2014
 5   liked         3      6      7/7/2014

SELECT user_name a, post_title b, action_name c FROM actions c LEFT JOIN users a ON a.id=c.user_id LEFT JOIN posts b ON b.id = c.post_id ORDER BY c.date DESC LIMIT 10

Then, in loop, choose how to display this data, according to "action_name". In such way you can expand your wall for other activities, +use indexes for better database performance.

Upvotes: 0

Mariano D'Ascanio
Mariano D'Ascanio

Reputation: 1202

When you write SELECT * FROM feeds,likes... you are implicitly CROSS JOINing both tables. The engine will combine every record in each table with every record in the other. That is far from what you want.

I don't think you should be "linking" both tables, either. What you need, roughly speaking, is to get every post and every like, and then order that big set according to timestamps.

It sounds more like a UNION between two queries, and an ORDER BY applied to the whole UNION. UNIONs are never easy on the eye, by the way...

The thing with UNIONs is that both sub-queries need to return the same amount of columns. Not knowing exactly which columns you have, I'll show you one possible solution:

SELECT activity, timestamp FROM (
   ( SELECT CONCAT(u.name,' posted ',f.content) as activity, timestamp
     FROM user u
     JOIN feed f on (f.user_id=u.id)
     WHERE f.deleted!=0
   ) UNION
   ( SELECT CONCAT(u.name, ' liked a post by ',u2.name) as activity, timestamp
     FROM user u
     JOIN likes l on (l.user_id=u.id)
     JOIN feed f on (l.feed_id=f.id)
     JOIN user u2 on (f.user_id=u2.id)
     WHERE l.deleted!=0
   )
) as whole_result
ORDER by timestamp desc

You should, of course, modify this to match your structure.

Hope this helps!

Upvotes: 0

no_juan
no_juan

Reputation: 67

Query should be linked via postID F=feeds table, L=likes table, U1=usertable linked to owned feeds, U2=usertable linked to likes table

 SELECT F.postTitle+' posted by '+ U1.username,'liked by'+U2.username  
 FROM likes L
 LEFT JOIN feeds F on (F.postID=L.postID)
 LEFT JOIN users U1 on (U1.userID=F.userID) 
 LEFT JOIN users U2 on (U2.userID=L.userID) 
 ORDER BY L.date,L.postID DESC

Upvotes: 0

Related Questions