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?

I have the answer in mysql but i don't know laravel, you are mixing 2 queries : - first you want to get which supplier_id having 2 different values - Second you want to display it — Daniel E.
– Daniel E., Commented Nov 14, 2017 at 9:16

G.Baghashvili · Accepted Answer · 2017-11-14 09:32:17Z

4

You can try:

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

edited Nov 14, 2017 at 9:32

answered Nov 14, 2017 at 9:08

G.Baghashvili

2201 gold badge3 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

06011991 Over a year ago

I've tried this already. It excludes the repeated values that's it .

G.Baghashvili Over a year ago

Without goupBy it returns only one supplier_id. In your case it has no difference, but if you have large data it goes to error.

Daniel E. · Accepted Answer · 2017-11-14 09:34:13Z

0

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

answered Nov 14, 2017 at 9:34

Daniel E.

2,4811 gold badge16 silver badges25 bronze badges

3 Comments

06011991 Over a year ago

let me check these two queries

06011991 Over a year ago

Your queries are returning all the supplier IDs but this query works for me SELECT supplier_id FROM supplier_assets WHERE asset_id IN ('4', '7') GROUP BY supplier_id HAVING COUNT(*) = But when i convert this into laravel its returning null :(

Daniel E. Over a year ago

What is the result of the 1st query ?

Collectives™ on Stack Overflow

array values with where condition in laravel query

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related