CODEWITHSUNDEEP
phpmysql
Kiwizoom
Kiwizoom

Reputation: 443

Combine 2 MySQL select statements both using LIMIT

I'm making a feed wall, and two services save posts to the same database table.

One posting service is used way more than the other, so on the wall I want to limit each service to the 25 newest posts (total 50) on the front page for equal representation.

This is what I originally had, without evening the posts:

$sql = "SELECT * FROM posts";
$sql .= " WHERE disq = 0";
$sql .= " AND approved = 1";
$sql .= " ORDER BY created_at DESC";
$sql .= " LIMIT 50";

but then I try to limit them by service:

$sql_1 = "SELECT * FROM posts";
$sql_1 .= " WHERE disq = 0";
$sql_1 .= " AND approved = 1";
$sql_1 .= " AND source = 'TW'";
$sql_1 .= " ORDER BY created_at DESC";
$sql_1 .= " LIMIT 25";

$sql_2 = "SELECT * FROM posts";
$sql_2 .= " WHERE disq = 0";
$sql_2 .= " AND approved = 1";
$sql_2 .= " AND source = 'IG'";
$sql_2 .= " ORDER BY created_at DESC";
$sql_2 .= " LIMIT 25";

Doing something like 

$sql = $sql_1 UNION $sql_2;

Doesn't seem to work, because all the examples I see perform LIMIT at the end of a bunch of queries. And an ORDER BY should be performed after that, to reorder the posts chronologically and make the wall mixed service.

MySQL help is appreciated

Upvotes: 2

Views: 275

Answers (2)

Timo Willemsen
Timo Willemsen

Reputation: 8857

You could omit your ORDER BY in your subqueries and just do it at the end, after the union.

(
SELECT * FROM POSTS 
WHERE disq = 0 AND approved = 1 AND source= 'TW'
LIMIT 25
)
UNION ALL
(
SELECT * FROM POSTS 
WHERE disq = 0 AND approved = 1 AND source= 'IG'
LIMIT 25 
)
ORDER BY created_at DESC

More info on the union and how to sort is explained at this MySQL documentation

Upvotes: 1

O. Jones
O. Jones

Reputation: 108686

You need to pull the most recent 25 items separately from your two criteria sets, then put them together and order them again.

That will go like this. You need these parenthetical subqueries because the ORDER BY ... LIMIT clauses need to be associated with each one separately.

SELECT * 
  FROM 
       ( SELECT * 
           FROM POSTS 
          WHERE disq = 1 AND approved = 1 AND source= 'TW'
          ORDER BY created_at DESC 
          LIMIT 25
        ) A
 UNION ALL
       ( SELECT * 
           FROM POSTS 
          WHERE disq = 1 AND approved = 1 AND source= 'IG'
          ORDER BY created_at DESC
          LIMIT 25 
        ) A
 ORDER BY created_at DESC, source

An index on your POSTS table on (disq, approved, source, created_at) will serve to speed up this query.

Upvotes: 1

Related Questions