Kohana ORM，重写 ORDER BY 和 GROUP BY 查询

发布于 2025-01-05 04:07:25 字数 589 浏览 1 评论 0原文

我需要从使用 Kohana ORM 在article.id = message.article_id 上加入的相关表中获取按最新评论排序的文章列表。我设法构建了一个查询，该查询是 GROUPS 然后是 ORDERS：

SELECT * 
FROM  `articles` 
LEFT JOIN  `comments` ON (  `articles`.`id` =  `comments`.`article_id` ) 
GROUP BY  `comments`.`item_id` 
ORDER BY  `datetime` DESC

我试图构建的查询是：

SELECT * FROM `articles` LEFT JOIN 
(SELECT article_id, MAX(datetime) as datetime FROM comments GROUP BY (article_id)) 
AS b ON `articles`.`id` = b.`article_id` 
ORDER BY datetime

我不知道如何将其重写为 Kohana ORM...（而且我无法避免 ORM，因为有大量的依赖于它的代码）

原文

I need to get a list of Articles sorted by the latest Comment from a related table joined on article.id = message.article_id using Kohana ORM. I managed to build a query that GROUPS and only then ORDERS:

SELECT * 
FROM  `articles` 
LEFT JOIN  `comments` ON (  `articles`.`id` =  `comments`.`article_id` ) 
GROUP BY  `comments`.`item_id` 
ORDER BY  `datetime` DESC

The query I am trying to build is:

SELECT * FROM `articles` LEFT JOIN 
(SELECT article_id, MAX(datetime) as datetime FROM comments GROUP BY (article_id)) 
AS b ON `articles`.`id` = b.`article_id` 
ORDER BY datetime

I have no idea how to rewrite it into Kohana ORM... (and I can't avoid ORM because there is a ton of code that depends on it)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

哆啦不做梦 2025-01-12 04:07:25

$subquery = DB::select('article_id', array('MAX("datetime")','datetime'))
    ->from('comments')
    ->group_by('article_id');

$s = ORM::factory('article')
    ->join(array($subquery, 'b'), 'LEFT')
    ->on('article.id','=','b.article_id')
    ->order_by('datetime')
    ->find_all();

这是您的查询的翻译，我不确定它是否有效

$subquery = DB::select('article_id', array('MAX("datetime")','datetime'))
    ->from('comments')
    ->group_by('article_id');

$s = ORM::factory('article')
    ->join(array($subquery, 'b'), 'LEFT')
    ->on('article.id','=','b.article_id')
    ->order_by('datetime')
    ->find_all();

This is the translation of your query, I'm not really sure if it will work

回复收藏 0 原文

背叛残局 2025-01-12 04:07:25

ORM::factory('article')->join('comments', 'LEFT')->on('article.id', '=', 'comments.article_id')->group_by('comments.id')->order_by('date', 'DESC')->find_all()->as_array();

这会生成 sql 如下：

SELECT article.* FROM articles AS article LEFT JOIN comments
ON (文章.id = 评论.article_id) 按评论分组。<代码>id
ORDER BY 日期 DESC

与您的第一个查询匹配。

我不确定如何在 ORM 中使用嵌套查询，但有查询生成器在 kohana 中，这应该可以帮助你。

ORM::factory('article')->join('comments', 'LEFT')->on('article.id', '=', 'comments.article_id')->group_by('comments.id')->order_by('date', 'DESC')->find_all()->as_array();

This generates sql as :

SELECT article.* FROM articles AS article LEFT JOIN comments
ON (article.id = comments.article_id) GROUP BY comments.id
ORDER BY date DESC