CODEWITHSUNDEEP
phpmysqlarrayslaravel-5.3laravel-query-builder
06011991
06011991

Reputation: 807

array values with where condition in laravel query

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

data

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?

Upvotes: 0

Views: 2193

Answers (2)

Daniel E.
Daniel E.

Reputation: 2480

Here is the mysql you should do :

1- get all the id who have more than 1 supplier_id :

SELECT supplier_id, Count(distinct asset_id) as dist_asst
 FROM Table
 GROUP BY supplier_id
 HAVING dist_asst > 1

2- then doing a join :

SELECT t1.supplier_id
FROM Table t1
INNER JOIN (SELECT supplier_id, Count(distinct asset_id) as dist_asst
     FROM Table
     GROUP BY supplier_id
     HAVING dist_asst > 1) t2 on t2.supplier_id = t1.supplier_id

Upvotes: 0

G.Baghashvili
G.Baghashvili

Reputation: 220

You can try:

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

Upvotes: 4

Related Questions