分组和排序问题

Question

I have a notifications system (PHP, MySQL- based) and I am trying to do the following: Query notifications results grouped by posts without any limit but then ordered by time_sent limited to 5 (per iteration). Then I used an ajax to show the next iteration set of 5 notifications.

Basically I need 5 notifications at a time (per set) but for each of them, if a group concerns one post: there can be as many notifications as wanted:

This is how there will be displayed:

1. Notification concerning post F – sent 1 day ago
2. First notification concerning post C – sent 2 days ago
   • Second notification concerning post C – sent 3 days ago
   • Third notification concerning post C – sent 4 days ago
   • Fourth notification concerning post C – sent 5 days ago
3. Notification concerning post Y – sent 3 days ago
4. First notification concerning post D – sent 4 days ago
   • Second notification concerning post D – sent 5 days ago
   • Third notification concerning post D – sent 6 days ago
5. Notification concerning post Y – sent 5 days ago

If a new notification were sent concerning post Y, this would be upped to the top of the list naturally.

This is how I had started, before I decided to implement this group system:

$notifications_req = mysql_query('SELECT * FROM notifications WHERE user_id = "'.$user_id.'" ORDER BY time_sent DESC LIMIT 5');

And now I got lost, and struggle with this group by and order by logic.

The database contains of course a post_id column.

EDIT:

I ended up changing quite drastically the way I wanted to sor the notifications. This is how I eventually did it. It might not be the most efficient, but it works the way I want:

<?php  $notifications_all_req = mysql_query('SELECT * FROM notifications WHERE user_id = "'.$user_id.'" ORDER BY time_sent DESC');  if(mysql_num_rows($notifications_all_req) > 0){
while($row =  mysql_fetch_assoc($notifications_all_req))
{
    $post_req_ids[] = $row['post_id'];
}
$unique_post_ids = array_unique($post_req_ids);
foreach ($unique_post_ids as $i => $unique_post_id)
{
    ?>      
    <ul id="notifications_ul_<?php echo $i; ?>">
    <?php
    $notifications_req = mysql_query('SELECT * FROM notifications WHERE (user_id = "'.$user_id.'" AND post_id = "'.$unique_post_id.'") ORDER BY time_sent DESC ');
        while($notification =  mysql_fetch_assoc($notifications_req))
        {

        }
        ?>
    </ul>
    <?php
}  }  ?>

This way, I can control the limits of both the notifications group (using $i) and the ones within each group, also with an Ajax load more code for the latter.

Answer 1

You don't want to use GROUP BY here, because that's for summary queries.

Pro tip: Don't use SELECT *. Instead, select the columns you want by name.

Pro tip: People are advising you to use PDO or mysqli_ for a really good reason. The mysql_ interface is notoriously insecure. If you put a web app based on mysql_ on the public network, some badguy will pwn you. If you don't mind being pwned, for example if your're building a prototype or proof-of-concept web site, don't worry about it.

Let's get the result set ordering right before we start to segment it into five-post chunks. You need two items in your ORDER BY clause. It seems you want your result set ordered like this.

SELECT * 
  FROM notifications 
 WHERE user_id = ? 
 ORDER BY post_id, time_sent DESC

Give that a try, without the LIMIT 5, and debug your php code so you get a good display, even if it's too long for your purposes.

Next, if I understand you correctly, you're trying to show five posts at a time. This gets trickier, because you want five posts, and each of them can have a variable number of notifications.

So we have to come up with a way to get five posts at a time. This subquery will do that, giving a list of five post_id values.

 SELECT DISTINCT post_id
   FROM notifications
  WHERE user_id = ?
  ORDER BY post_id
  LIMIT 5 OFFSET ?   

The parameter to OFFSET ? should be 0, 5, 10 and so forth to get the first, second, third, etc, bunch of five posts. If you only want the first five posts just leave out the OFFSET clause.

Now we have to embed that subquery that gets just five posts in another query to get all the data. That works like this.

SELECT * 
  FROM notifications 
 WHERE post_id IN (
                      SELECT DISTINCT post_id
                        FROM notifications
                       WHERE user_id = ?
                       ORDER BY post_id
                       LIMIT 5 OFFSET ?   
                  ) 
 ORDER BY post_id, time_sent DESC

This last, more complex, SQL query will give a resultset that looks like the one from the first statement. You should be able to plug it in to your debugged php code and get the display you're looking for.