CODEWITHSUNDEEP
phpmysqlsqlpdo
Jihane Elberouhi
Jihane Elberouhi

Reputation: 55

Getting two different results by one SQL query

First I am new to SQL and PHP.

I have created a simple social networking web app so users can post and follow others to see new posts from them.

At home page a user can first see posts from all users he is followong.

but what i want is to make the user see some other random popular posts that will be ordered by Likes.

here what i have done to get posts from users i follow:

SELECT * FROM posts WHERE author_id in 
 (SELECT followedID FROM follows WHERE
 followerID=:myID)
 ORDER BY id DESC LIMIT 10

Now let's say you are following only 1 person. and that person has only one post. here you will see no more than a post!

That's why i want to show more posts when a user has already seen all posts.

i want some easy way to get other posts when the above query has done getting some specific posts.

This is the next query i'd like to execute.

SELECT * FROM posts ORDER BY post_likes DESC LIMIT 10

Upvotes: 0

Views: 300

Answers (2)

Ken Lee
Ken Lee

Reputation: 8103

You may use UNION to do what you want

(SELECT * FROM posts WHERE author_id in 
 (SELECT followedID FROM follows WHERE
 followerID=:myID)
 ORDER BY id DESC limit 0,10) 
union
(SELECT * FROM posts ORDER BY post_likes DESC limit 0,10)
LIMIT 0, 10
  1. UNION will automatically append the 2nd query result to the 1st query result, and then show only the number of records specified by the LIMIT clause
  2. Please note that union works only if the queries are of the same structure (which in this case is positive)
  3. Please note that the use of parenthesis is mandatory if you use order by or limit or both
  4. I have used 3 limit clauses (one for each query , and one for the final result of union) AND Both queries have ORDER BY clause. This is to make sure that the records extracted are what you want. (to show the followed posts first, and both are ordered properly)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I wouldn't recommend union, because it incurs overhead for removing duplicates.

Instead, you can use a LEFT JOIN:

SELECT p.*
FROM posts p LEFT JOIN
     follows f
     ON p.author_id = f.follows_id AND
        f.followerID = :myID
ORDER BY (f.follows_id IS NOT NULL) DESC,
         (CASE WHEN f.follows_id IS NOT NULL THEN p.id END),
         p.post_likes DESC
LIMIT 10;

The ORDER BY puts the followed posts first. The other two clauses order each of the groups by the criteria you want.

Upvotes: 1

Related Questions