2

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

Kiwizoom
  • 433
  • 1
  • 8
  • 21
  • You could use a temporary table to store your results. This way you don't even need a _UNION_. – A.D. Feb 09 '15 at 21:20
  • The feed updates with new posts every few minutes, so I'm not sure what's better, using a union or table. Not really used to mysql queries more complicated than select, update, etc – Kiwizoom Feb 09 '15 at 21:25
  • Temporary are really fast because they are in memory. And they exist only for the length of the transaction. – A.D. Feb 09 '15 at 21:27

2 Answers2

1

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

Timo Willemsen
  • 8,717
  • 9
  • 51
  • 82
  • 1
    OP wanted 25 from each, I think. And, of course, `UNION ALL` for speed if if there won't be any duplicates to remove. – Marcus Adams Feb 09 '15 at 21:32
  • Ah yeah thanks, forgot about it. Added it in the example. For Kiwizoom: UNION tries to remove duplicates from the result, so it's a bit slower. CHeck also here: http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all – Timo Willemsen Feb 09 '15 at 21:34
  • Oh, thank you. This seems to be working. I guess I didn't know the syntax for how to write that union. The tidbit about UNION ALL is helpful – Kiwizoom Feb 09 '15 at 21:37
  • 1
    This query most likely gets the oldest rows in each group. It certainly doesn't get the most recent. If it were to get the newest you'd need `ORDER BY created_at DESC` right before each `LIMIT 25`. – O. Jones Feb 09 '15 at 21:42
  • disq was 0 but that is petty db condition on my side. This is a correct answer – Kiwizoom Feb 09 '15 at 21:48
  • How many small mistakes can one make in a simple answer :') Apparantly 3 so far xD – Timo Willemsen Feb 09 '15 at 21:49
1

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172