Yawning Milk Yawning Milk - 6 months ago 78
PHP Question

Laravel Query Builder count from join

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

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