Laravel many to many loading related models with count

Question

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

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

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

Jarek Tkaczyk · Accepted Answer · 2015-03-25T14:41:49.530

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

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;

And for many to many relations:

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

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

Thank you, I wound up doing something similar to get the results I wanted. Less clean, but worked fine for what i needed. — Ego_I, Sep 05 '14 at 04:04
Aint the link wrong ? this is the one that should be referenced http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently/ it is your own page damn you :P — Abderrahmane TAHRI JOUTI, Mar 25 '15 at 14:34
@JarekTkaczyk This works perfectly for first degree relationships. I tried tweaking it and use it on : `Country > City > Person`, where I would want to count all people in a `Country`. instead of `hasOne`, I am using `hasManyThrough('Person','City')`. All is fine, except `people_count` (the relation) comes with all fields from the `Person` table, not just `city_id` and `aggregate`. How can I avoid polluting the results with unnecessary data ? — Abderrahmane TAHRI JOUTI, Mar 25 '15 at 15:33
@AbderrahmaneTAHRIJOUTI Show me a paste of your code and its result pls. — Jarek Tkaczyk, Mar 25 '15 at 15:53
@JarekTkaczyk http://stackoverflow.com/questions/29261913/count-and-sum-a-second-degree-relation-in-laravel-5 — Abderrahmane TAHRI JOUTI, Mar 25 '15 at 17:01

score 0 · Answer 2 · answered Apr 21 '19 at 21:58

0

as of laravel 5.3 you can do this

withCount('comments','tags');

and call it like this

$post->comments_count;

laravel 5.3 added withCount

answered Apr 21 '19 at 21:58

Ahmed Aboud

1,232
16
19

Laravel many to many loading related models with count

2 Answers2

However you can make it easier with relations like below:

Linked