Laravel 多对多加载相关模型计数

2021-12-26 00:00:00 many-to-many pivot-table php laravel-4 eloquent

我正在尝试链接 4 个表，并添加一个自定义字段，该字段通过使用 laravel 计算一些相关表的 id 来计算.我在 SQL 中有这个可以做我想要的，但我认为它可以提高效率:

I am trying to link 4 tables and also add a custom field calculated by counting the ids of some related tables using laravel. I have this in SQL which does what I want, but I think it can be made more efficient:

DB::select('SELECT 
                        posts.*,
                          users.id AS users_id, users.email,users.username,
                          GROUP_CONCAT(tags.tag ORDER BY posts_tags.id) AS tags,
                          COUNT(DISTINCT comments.id) AS NumComments, 
                          COUNT(DISTINCT vote.id) AS NumVotes
                        FROM 
                          posts    
                          LEFT JOIN comments ON comments.posts_id = posts.id
                          LEFT JOIN users ON users.id = posts.author_id
                          LEFT JOIN vote  ON vote.posts_id = posts.id
                          LEFT JOIN posts_tags  ON posts_tags.posts_id = posts.id
                          LEFT JOIN tags  ON tags.id = posts_tags.tags_id

                        GROUP BY 
                          posts.id, 
                          posts.post_title');

我尝试通过这样做使用 eloquent 来实现它:

I tried to implement it using eloquent by doing this:

$trending=Posts::with(array('comments' => function($query)
                {
                    $query->select(DB::raw('COUNT(DISTINCT comments.id) AS NumComments'));

                },'user','vote','tags'))->get();

但是 NumComments 值没有显示在查询结果中.任何线索如何去做?

However the NumComments value is not showing up in the query results. Any clue how else to go about it?

推荐答案

使用 with 不能这样做，因为它执行单独的查询.

You can't do that using with, because it executes separate query.

您需要的是简单的join.只需将您的查询翻译成类似的内容:

What you need is simple join. Just translate the query you have to something like:

Posts::join('comments as c', 'posts.id', '=', 'c.id')
    ->selectRaw('posts.*, count(distinct c.id) as numComments')
    ->groupBy('posts.id', 'posts.post_title')
    ->with('user', 'vote', 'tags')
    ->get();

那么集合中的每个帖子都会有计数属性:

then each post in the collection will have count attribute:

$post->numComments;

<小时>

但是，您可以通过以下关系更轻松:

虽然第一个解决方案在性能方面更好(除非您有大数据，否则可能不会引起注意)

However you can make it easier with relations like below:

Though first solution is better in terms of performance (might not be noticeable unless you have big data)

// helper relation
public function commentsCount()
{
    return $this->hasOne('Comment')->selectRaw('posts_id, count(*) as aggregate')->groupBy('posts_id');
}

// accessor for convenience
public function getCommentsCountAttribute()
{
    // if relation not loaded already, let's load it now
    if ( ! array_key_exists('commentsCount', $this->relations)) $this->load('commentsCount');

    return $this->getRelation('commentsCount')->aggregate;
}

这将允许您执行此操作:

This will allow you to do this:

$posts = Posts::with('commentsCount', 'tags', ....)->get();
// then each post:
$post->commentsCount;

对于多对多关系:

public function tagsCount()
{
    return $this->belongsToMany('Tag')->selectRaw('count(tags.id) as aggregate')->groupBy('pivot_posts_id');
}

public function getTagsCountAttribute()
{
    if ( ! array_key_exists('tagsCount', $this->relations)) $this->load('tagsCount');

    $related = $this->getRelation('tagsCount')->first();

    return ($related) ? $related->aggregate : 0;
}

可以在此处找到更多此类示例 http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently/

More examples like this can be found here http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently/

相关文章