問題描述

我正在嘗試鏈接 4 個(gè)表，并添加一個(gè)自定義字段，該字段通過使用 laravel 計(jì)算一些相關(guān)表的 id 來計(jì)算.我在 SQL 中有這個(gè)可以做我想要的，但我認(rèn)為它可以提高效率:

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 來實(shí)現(xiàn)它:

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 值沒有顯示在查詢結(jié)果中.任何線索如何去做?

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

推薦答案

使用 with 不能這樣做，因?yàn)樗鼒?zhí)行單獨(dú)的查詢.

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

您需要的是簡單的join.只需將您的查詢翻譯成類似的內(nèi)容:

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();

那么集合中的每個(gè)帖子都會有計(jì)數(shù)屬性:

then each post in the collection will have count attribute:

$post->numComments;

<小時(shí)>

但是，您可以通過以下關(guān)系更輕松:

雖然第一個(gè)解決方案在性能方面更好(除非您有大數(shù)據(jù)，否則可能不會引起注意)

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;
}

這將允許您執(zhí)行此操作:

This will allow you to do this:

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

對于多對多關(guān)系:

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/

這篇關(guān)于Laravel 多對多加載相關(guān)模型計(jì)數(shù)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！