/ laravel

Result + aggregate queries in Eloquent

For a project I'm working on, I wanted to get a list of user-supplied tags for an office and the number of times each was submitted (e.g. 7 users added #freebeer):

[
    { tag: Tag#615, tagged: 942 },
    { tag: Tag#172, tagged: 257 },
    ...
]

Now in Doctrine, this is fairly straightforward:

TagRepository::createQueryBuilder()
    ->select('t', 'count(t.id) AS tagged')
    ->from('Tag', 't')
    ->groupBy('t.tag')
    ->orderBy('t.tag', 'ASC');

However in Eloquent there isn't a way of selecting the model like this (Eloquent's query-builder seems to be halfway between a database query-builder and an ORM query-builder).

You can pass a list of fields and expressions to Tag::select, which are then set on the resulting Tag instance:

Tag::select(['tags.*', DB::raw('COUNT(id) AS tag_count')])
    ->where('tags.office_id', $office->id)
    ->orderBy('tags.tag', 'ASC')
    ->groupBy('tags.tag')
    ->get();

foreach ($tags as $tag) {
    var_dump($tag->tag, $tag->tag_count);
}

The downside here is that you've got to know to look for this field. It seemed cleaner to run two queries and zip together the results:

$tags = Tag::where('tags.office_id', $office->id)
    ->where('tags.office_id', $office->id)
    ->orderBy('tags.tag', 'ASC')
    ->groupBy('tags.tag')
    ->get();

$counts = Tag::select('tags.tag', DB::raw('count(id) as tagged'))
    ->where('tags.office_id', $office->id)
    ->groupBy('tags.tag')
    ->lists('tagged', 'tags.tag'); // returns a map [tagname => count]

return $tags->map(function(Tag $tag) use ($counts) {
    return ['tag' => $tag, 'count' => $counts[$tag->tag]];
});