最后 x 个博客条目 - 但每个用户只有一次

2021-12-21 00:00:00 mysql cakephp cakephp-2.0

我想显示一个包含最后 x 个(比如 5 个)博客条目的框.但我想避免非常活跃的用户被列出两次.

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.

我的试用归结为:

    $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,
    ));

但是 - 当然 - 它分组太快而没有机会首先对其进行排序.生成的 sql 经常会丢失用户最后发布的博客条目，以支持他的旧博客条目:

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.

如何在分组前先按已发布的 DESC 排序?或者有其他方便的方法吗?谢谢

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

表格的结构:

用户:

- id
- username

博客:

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

@杰拉德:

好像 MYSQl 不喜欢这样的子查询:

Seems like MYSQl doesnt like such subqueries:

语法错误或访问冲突:1235 此版本的 MySQL 尚不支持 'LIMIT &IN/ALL/ANY/SOME 子查询'

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

如果我省略 subqery 限制:

If I omit the subqery limit:

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

推荐答案

使用子查询似乎有效 - 通过这个小技巧:

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() 是一个 AppModel 方法:https://github.com/dereuromark/tools/blob/2.0/Lib/MyModel.php#L405

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

相关文章