Level 73
You can't use a join for that query try using a subquery instead.
something like this
SELECT m.name, (SELECT count(vr.*) FROM visitas_recetas vr WHERE month(vr.created_at) = m.id) FROM months m
Aug 16, 2018
5
Level 1
Eloquent isn't returning the fields where a column result is expected to be 0
I'm doing a query in eloquent that returns all months on a year, and said months have a sum related to a table, I've got everything running smoothly on MySQL but it's not returning the months where there is supposed to be 0 results in Eloquent. This is the actual query in MySQL.
SELECT months.name as m, count(MONTH(created_at)) as total from visitas_recetas RIGHT JOIN months ON MONTH(created_at) = months.id where YEAR(created_at) = 2018 GROUP BY months.id
and my Eloquent query builder code is the following
entriesModel::select('months.name as m',DB::raw('count(MONTH(created_at)) as total'))
->rightJoin('months',DB::raw('MONTH(created_at)') , '=', 'months.id')
->whereYear('created_at',$request->get('year'))
->groupBy('months.id')->get();
Again, I'm using a table containing all the months to do a right join to said table and to return the months that contain 0 entries related to the 'entries' table.
EDIT: I figured it has something do with the "year" filter, is there any workaround to this?
Level 1
Your query does basically the same thing as the one in my question, but I can't filter by the created_at year, and that's where the problem is
Level 24
What do you mean by "I figured it has something do with the "year" filter, is there any workaround to this?"?
Level 1
What do you mean by "I figured it has something do with the "year" filter, is there any workaround to this?"?
Yeah, the problem was that I was trying to filter the results outside of the right join, so the joined data got excluded (the ones returning 0), I had to move the YEAR(created_at) = $year inside of the join, then the Eloquent code had to be like this:
$year = $request->get('year');
entriesModel::select('months.name as m',DB::raw('count(MONTH(created_at)) as total'))
->rightJoin('months', function($join) use($year){
$join->on(DB::raw('MONTH(created_at)') , '=', 'months.id');
$join->on('created_at','=', $year)
})
->groupBy('months.id')->get();
Level 1
But yeah, I didn't know that by the time I edited the question asking for the "workaround" for the "filter" meaning that where did I have to filter the years, the question was poorly formulated, my bad
Please or to participate in this conversation.