Prehistory

It seems there are plenty of similar questions on the forum, but for some reason they did not help me.

I want to make a list of 50 fresh (not earlier than a month ago) answers (new subscribers, likes, comments) for social, forgive me Zuckerberg, networks.

That is, you need to combine 3 tables to simultaneously select a maximum of 50 rows from them and sort these rows by dateTime. That's all what my brain was capable of:

$getReplies=$pdo->prepare( "SELECT * FROM followers FULL OUTER JOIN comments ON comments.replyToUserId = :id AND comments.dateTime BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() FULL OUTER JOIN likes ON likes.toId = :id AND likes.dateTime BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() WHERE followers.toId = :id AND followers.dateTime BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() ORDER BY followers.dateTime DESC, comments.dateTime DESC, likes.dateTime DESC LIMIT 50" ); $getReplies->execute(array('id' => $out[0])); $replies=$getReplies->fetchAll();

But the output is nothing. Help, please, I already danced all tambourine.

▼ AT THE MOMENT THE PROBLEM IS SUCH ▼

On the advice below, I did it with the help of UNION. But I still get an empty array, even for the simplest queries that work without problems one by one. What could be wrong here?

 $getReplies=$pdo->prepare(" SELECT * FROM followers WHERE toId = '1' UNION SELECT * FROM likes WHERE toId = '1' "); $getReplies->execute(); $replies=$getReplies->fetchAll();
  • one
    join usually join tables that are logically related to each other. Thus, in order to present information from several tables in one line. besides, specifically FULL OUTER JOIN in MySQL is not in principle. Something tells me that you need UNION - Mike
  • @Mike, hike, you're right. But I still get an empty array. I updated the question, please look. - i am so lame
  • one
    Always check for error requests and output these errors somewhere. php.net/manual/ru/pdo.error-handling.php In general, queries must be debugged in some kind of MySQL workbench or phpmyadmin or something else before popping the code into php. It would immediately tell you that the number of fields in your subqueries does not match (I strongly doubt that these 2 tables have exactly the same structure). union requires that queries return the same number of columns with the same data types. In general, the use of * bad tone, always specify the necessary columns - Mike
  • @Mike, I repent. One klyaty column in one of the tables spoiled all the fun. But that is my problem. Thank you, you can write down the last comment in response, put a daw) - i am so lame

1 answer 1

I think the request should look something like this:

 select * from ( select dateTime, a, b, c from followers where toId = :id AND dateTime >= NOW() - INTERVAL 30 DAY order by dateTime desc limit 50 ) A UNION ALL select * from ( select dateTime, a, b, c from likes where toId = :id AND dateTime >= NOW() - INTERVAL 30 DAY order by dateTime desc limit 50 ) A order by dateTime desc limit 50
  • If the task is not worth sifting out duplicates it is better to use UNION ALL , it is faster (and there can hardly be duplicates in information from different tables).
  • Data should be reduced as much as possible in advance, in each part of the request, so that the union does not perform extra work by combining what will be thrown away later.
  • The number of columns and data types returned by each subquery must be the same.