Laravel Query Builder Counts From Combining

Question

So, in my database there is a table called website_tags that contains id, title , etc., and I also have a table called websites , with a similar design. There is also a table called assigned_tags that contains the link between tags and websites, so it contains the relationship id, tag_id and website_id .

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

 1: men 2: women 

And the assigned tags contain as id: tag_id: website_id

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

So, I will get this tag 'men', which is used on 2 sites, and the tag 'women' is used in 1. How can I build a query? At the moment 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 the lines in assign_tags.

Answer 1

You must define groupBy so that the query knows how to count it (as in 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();