CODEWITHSUNDEEP
mysqlsql
TheLettuceMaster
TheLettuceMaster

Reputation: 15734

Getting a list of data based on Items associated with User

I have a table called reviews. I get the most current user reviews like this:

SELECT b.item, b.item_id, a.review_id, a.review, c.category, u.username, c.cat_id 
FROM reviews a
INNER JOIN items b
     ON a.item_id = b.item_id
INNER JOIN master_cat c
     ON c.cat_id = b.cat_id
INNER JOIN users AS u
     ON u.user_id = a.user_id               
ORDER BY a.review_id DESC;

What I want to do is slightly alter it to be more personable for users.

I have another table of user "connections". Kind of like Twitter. When a user follows someone, it gets logged in this table called profile_follow. This has three columns. id, user_id, follow_id. Simply: If I am user #1, and I "follow" user # 3 and user #5, two rows will be added in this table:

     profile_follow
------------------------
id | user_id | follow_id
   | 1       | 3
   | 1       | 5

Here is how I want to change the query above. I want to only show newest reviews, from people you follow.

So I will need at least one more join, for table profile_follow. And I need to pass in a user_id (it's a php function), doing something like `WHERE profile_follow.user_id = '{$user_id}'. I think I will have to add a sub query on this, not use.

Can someone show me how to finish this query? I am not sure how to handle it from here? All of my attempts have been off so far.

I think I need to do something like:

Selectfollow_idwhereuser_id= (logged in user)

And then in the main query:

Select reviews only with profile_follow.follow_id = review.user_id.

I can't figure out how to make this filter work.

Upvotes: 0

Views: 84

Answers (1)

boisvert
boisvert

Reputation: 3739

Always difficult without testing, but:

SELECT b.item, b.item_id, a.review_id, a.review, c.category, u.username, c.cat_id 
FROM reviews a
INNER JOIN items b
     ON a.item_id = b.item_id
INNER JOIN master_cat c
     ON c.cat_id = b.cat_id
INNER JOIN profile_follow pf
     ON pf.follow_id = a.user_id
WHERE profile_follow.user_id = '{$user_id}'
ORDER BY a.review_id DESC;

Upvotes: 1

Related Questions