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

2022-01-08 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;
}

这将允许您这样做:

$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/

相关文章