Last x blog entries - but only once per user

Question

I would like to display a box with the last x (say 5) blog entries. But I would like to avoid that a very active user is listed twice.

My tryout boils down to:

    $stats['blog'] = $this->User->Blog->find('all', array(
        'order'=>array('Blog.published' => 'DESC'), 
        'conditions' => array('Blog.status' => 1), 
        'contain' => array('User.username'),
        'group' => array('User.id'),
        'limit' => 5,
    ));

But - of course - it groups too soon without the chance of sorting it first. The resulting sql often loses the last published blog entries of a user in favor of one of his older ones:

SELECT * 
FROM `comm_blogs` AS `Blog` 
LEFT JOIN `comm_users` AS `User` ON (`Blog`.`user_id` = `User`.`id`) 
WHERE `Blog`.`status` = 1 
GROUP BY `User`.`id` 
ORDER BY `Blog`.`published` DESC LIMIT 5

Therefore the result is almost completely wrong all the time because the new blog entries never show up if this user already blogged about something else in the past.

How can I first sort by published DESC before grouping? Or is there another convenient way? Thx

The stucture of the tables:

users:

- id
- username

blogs:

- id
- user_id
- published (datetime)
- title
- content
- status

@gerald:

Seems like MYSQl doesnt like such subqueries:

Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

SELECT `User`.`id`, `User`.`username`, `Blog`.`id`, `Blog`.`headline`, `Blog`.`published`, `UserInfo`.`gender`, `UserInfo`.`id` FROM `comm_blogs` AS `Blog` 
LEFT JOIN `comm_users` AS `User` ON (`Blog`.`user_id` = `User`.`id`) 
LEFT JOIN `comm_user_infos` AS `UserInfo` ON (`Blog`.`user_id` = `UserInfo`.`id`) 
WHERE `User`.`active` = '1' AND `Blog`.`status` = 1 AND `Blog`.`id` IN (
    SELECT `LastBlog`.`id`, MAX(`LastBlog`.`published`) as last 
    FROM comm_blogs AS LastBlog WHERE `LastBlog`.`status` = 1 
    GROUP BY `LastBlog`.`user_id` ORDER BY last DESC LIMIT 5
) 
ORDER BY `Blog`.`published` DESC

If I omit the subqery limit:

 Cardinality violation: 1241 Operand should contain 1 column(s) 

Answer 1

Using a subquery seems to work - with this little trick:

$options = array(
    'fields' => array('MAX(SubBlog.created)'),
    'conditions' => array('SubBlog.user_id = Blog.user_id')
);
$subquery = $this->subquery('all', $options);

$options = array(
    'order'=>array($this->alias.'.published' => 'DESC'),
    'conditions' => array(
        'User.active' => 1,
        'Blog.status' => self::STATUS_ACTIVE, 
        'Blog.published = ' . $subquery
    ),
    'contain' => array('User.username'),
    'fields' => array(
        'User.id',  'User.username', 
        'Blog.id', 'Blog.headline', 'Blog.published'
    ),
    'limit' => $limit,
);
return $this->find('all', $options);

subquery() is an AppModel method: https://github.com/dereuromark/tools/blob/2.0/Lib/MyModel.php#L405

Answer 2

how about if you try to use views for this one but it's a little bit weird solution something like:

CREATE VIEW comm_blogs_publish_desc AS SELECT `User`.`id` AS `userid`, * 
FROM `comm_blogs` AS `Blog` 
LEFT JOIN `comm_users` AS `User` ON (`Blog`.`user_id` = `User`.`id`) 
WHERE `Blog`.`status` = 1 
ORDER BY `Blog`.`published` DESC;

Then you could use it already to your original query:

SELECT * 
FROM comm_blogs_publish_desc
GROUP BY `userid` LIMIT 5;

It's just kind of weird because you still need to create the view but upon having it being grouped by userid you'll be sure that it's already sorted DESC by published date though because views are like virtual tables already, but it would be ideal if you specify which columns you'll really need because some of the column names may have conflict (same column names from other tables) if you'll just be using '*' to display all the columns on the table.

I saw the new update you did on the query the issue appears because your using 2 columns on IN statement (it only accepts 1 column for the subquery and will return an error when you use 2 or more columns on it causing the cardinality issue) update it this way:

SELECT `User`.`id`, `User`.`username`, `Blog`.`id`, `Blog`.`headline`, `Blog`.`published`, `UserInfo`.`gender`, `UserInfo`.`id` FROM `comm_blogs` AS `Blog` 
LEFT JOIN `comm_users` AS `User` ON (`Blog`.`user_id` = `User`.`id`) 
LEFT JOIN `comm_user_infos` AS `UserInfo` ON (`Blog`.`user_id` = `UserInfo`.`id`) 
WHERE `User`.`active` = '1' AND `Blog`.`status` = 1 AND `Blog`.`id` IN (
    SELECT `LastBlog`.`id` 
    FROM comm_blogs AS LastBlog WHERE `LastBlog`.`status` = 1 
    GROUP BY `LastBlog`.`user_id` ORDER BY last DESC
) 
ORDER BY `Blog`.`published` DESC LIMIT 0, 5;

And then transfer the limit to the main query instead of the subquery to avoid the last issue: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

;)

Answer 3

SELECT * 
FROM `comm_blogs` AS `Blog` 
LEFT JOIN `comm_users` AS `User` ON (`Blog`.`user_id` = `User`.`id`) 
WHERE `Blog`.`status` = 1 
    AND blog.id IN (
        SELECT lastblog.id, max(lastblog.published) as lastpost
        FROM comm_blogs AS lastblog
        WHERE lastblog.status = 1
        GROUP BY lastblog.user_id
        ORDER BY lastpost DESC LIMIT 5)

I think this will work; however, I have NOT tested the SQL.

As you can see, the inner SQL gets the last five blog entries with unique users. The outer SQL gets the remaining information associated with those entries based upon blog.id.

Answer 4

Without knowing the table structure, it is rather difficult, however, this should give you a starting point:

SELECT `User`.`id`, max(`Blog`.`published`) 
FROM `comm_blogs` AS `Blog` 
LEFT JOIN `comm_users` AS `User` ON (`Blog`.`user_id` = `User`.`id`) 
WHERE `Blog`.`status` = 1 
GROUP BY `User`.`id` 
ORDER BY 2 DESC LIMIT 5

By selecting the max publishing date per use and then sorting afterwards, you will get a list of the 5 most recent posts by different users. You will need a second query afterwards to get the contents however.