array values with where condition in laravel query

Question

I have an array called assetIDs as below

$assetIDs = Collection {#505 ▼
  #items: array:2 [▼
    0 => 4
    1 => 7
  ]
}

and I have the data in the table as below shown

and I'm doing query on the above table using this

$supplier_id = SupplierAsset::whereIn('asset_id',$asset_ids)->pluck('supplier_id');

and result for the above query is below

Collection {#510 ▼
  #items: array:3 [▼
    0 => 1
    1 => 1
    2 => 2
  ]
}

here whereIn is returning all the possible rows which satisfies the condition. Actually I need to get the result as like which supplier_id has both the values of assetIDs array.In my table supplier_id=1 has the both values 4 and 7 Just like below collection.

Collection {#510 ▼
  #items: array:3 [▼
    0 => 1
    1 => 1
  ]
}

Can anybody suggest me the solution for this please?

Answer 1

You can try:

$supplier_id = SupplierAsset::whereIn('asset_id',$asset_ids)
               ->groupBy('supplier_id')
               ->havingRaw('count(distinct asset_id) = ' . count($assetIDs))
               ->pluck('supplier_id');