最后x个博客条目 - 但每个用户只有一次 [英] Last x blog entries - but only once per user

问题描述

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

我的试用归结为：

  $ stats ['blog'] = $ this-> User->博客 - > find（'all'，array（
'order' => array（'Blog.published'=>'DESC'），
'conditions'=> array（'Blog.status'=> 1），
'contains'=> ; array（'User.username'），
'group'=> array（'User.id'），
'limit'=> 5，
） 
  

但是 - 当然 - 它太早，没有机会先排序。
生成的sql经常丢失用户的最后一个发布的博客条目，以支持他的一个较旧的博客条目：

  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 
  

因此，结果几乎是完全错误的，因为如果这个用户已经在过去的某些别的东西博客文章，则不会显示新的博客条目。 >

如何在发布DESC之前先进行排序？还是有另一种方便的方法？
Thx

表的结构：

用户：

   -  id 
  - 用户名
  

$ b b

博客：

   -  id 
  -  user_id 
  - 已发布（datetime）
  -  title 
  -  content 
  -  status 
  

@gerald：

看起来像MYSQl不喜欢这样的子查询：

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

  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 
  

如果我忽略子查询限制：

 违反基数：1241操作数应包含1列
  

解决方案

使用子查询似乎工作 - / p>

  $ 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 
），
'包含'=> 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

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) 

解决方案

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

这篇关于最后x个博客条目 - 但每个用户只有一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！