Level 104
Use wherePivot to constrain the query based on a pivot table column.
The $category variable is not in scope for the Closure
1 like
Mar 8, 2022
4
Level 3
Is my query correct when using pivot table?
Assuming both models ItemsList and Items are set correctly with belongsToMany , and the pivot table is items_to_list, I need to get the list of items with selected category and where the items are banned in the pivot table
Does this query seem to be correct
$lists = ItemsList::whereIn('id', $array_of_ids)
->with('items')
->withCount(['items' => function ($query) {
$query->where('items.category', $category)
->where('items_to_list.banned', 1);
}])->get();
Level 3
@tykus ok my mistake, for the example I changed a value to a variable, it's actually:
$lists = ItemsList::whereIn('id', $array_of_ids)
->with('items')
->withCount(['items' => function ($query) {
$query->where('items.category', 5)
->where('items_to_list.banned', 1);
}])->get();
Should I change it to:
$lists = ItemsList::whereIn('id', $array_of_ids)
->with('items')
->withCount(['items' => function ($query) {
$query->where('items.category', $category)
->wherePivot('banned', 1); // notice I removed the table name
}])->get();
Level 104
@cooperino I was responding on my phone earlier and didn't notice that the banned constraint was inside the withCount Closure. In that case, I expect the original implementation was ok - the necessary join should be handled by Eloquent. This should be correct:
$lists = ItemsList::whereIn('id', $array_of_ids)
->with('items')
->withCount(['items' => function ($query) {
$query->where('items.category', $category)
->where('items_to_list .banned', 1);
}])->get();
1 like
Level 3
@tykus thanks! I was about to say that when I changed it to wherePivot I got unknown column 'pivot' error .
now out of curiosity, how could I make the wherePivot work in this case with the same functionality?
Please or to participate in this conversation.