Laravel Query Builder count from join

Question

So in my database I have table called website_tags, which contains id, title and so on, and also I have table called websites, with similar construction. And also there is table called assigned_tags, which contains relation between tags and websites, so it contains relation id, tag_id and website_id.

What I need is to join these tables with query, I need to get all the tags and count how many times these tags are used. So, for example website_tags contains following information:

1: men
2: women

And assigned tags contains like id: tag_id: website_id

1: 1: 1
2: 1: 2
3: 2: 2

So I will get that tag 'men' is used in 2 websites and tag 'women' is used in 1. How should I build the query? For now I have:

DB::table('website_tags')
->join('assigned_tags', 'website_tags.id', '=', 'assigned_tags.tag_id')
->select('website_tags.id as id', 'website_tags.title as title', DB::raw("count(assigned_tags.tag_id) as count"))-
>get();

But this is wrong, this query just counts rows in assigned_tags.

Answer 1

You have to define groupBy so query will know how to count it (just like in the regular SQL)

Try something like this

DB::table('website_tags')
->join('assigned_tags', 'website_tags.id', '=', 'assigned_tags.tag_id')
->select('website_tags.id as id', 'website_tags.title as title', DB::raw("count(assigned_tags.tag_id) as count"))
->groupBy('website_tags.id')
->get();