Laravel belongsToMany select all specified ids

Question

Relation

    public function groups()
    {
            return $this->belongsToMany('App\Models\ItemGroup','item_to_group','item_id','item_group_id');
    }

** How to select all Items that have item_group_id 0 and 9 BOTH?**

    $zero_and_nine_count = \App\Models\Item::with(['groups' => function ($query) {
        $where[] = ['item_group_id', , 0];
        $where[] = ['item_group_id', '=', 9];
        $query->where($where);
    }])->count();

Counts all Items

Working SQL:

SELECT item_id FROM `item_to_group` WHERE `item_group_id` = 9 AND item_id IN (SELECT item_id FROM `item_to_group` WHERE `item_group_id` = 0)

Answer 1

Reference :

SELECT item_id 
FROM `item_to_group` 
WHERE 
    `item_group_id` = 9 AND 
    item_id IN (
        SELECT item_id 
        FROM `item_to_group` 
        WHERE `item_group_id` = 0
    )

You can query like this :

$items = \App\Models\Item::whereHas('groups', function ($query) {
        $query->where('item_group_id', 9)
            ->whereIn('item_id', function ($subquery) {
                $subquery->select('item_id')
                    ->from('item_to_group')
                    ->where('item_group_id', 0);
            });
    })->get();