CODEWITHSUNDEEP
phpmysqllaraveleloquentquery-builder
Saroj Shrestha
Saroj Shrestha

Reputation: 2875

Laravel whereIn not giving desired result

I have the following two tables:

unotes    unote_unit
======    ==========
id        unote_id
notes     unit_id

I am trying to retrieve unotes row whose related unit_id columns exactly match an input array.

So, I run following query:

$unote = UNote::whereHas('units', function ($query) use ($request) {
        $query->whereIn('units.id', $request->unit_lists);
    })
    ->withCount('units')
    ->having('units_count', '=', $u_list_count)
    ->get()
    ->pluck("id");

But, the problem with the above query is that even if it has just a single matching unit_id, it retrieves the data. For example I have following datasets:

unotes //with related unit_id
========
id = 3 //49865, 49866, 49867
id = 4  //49865, 49866

With above mentioned code, if I pass [49866,55555], it should return nothing but it returns ids 3 and 4, which contain one match but not all.

I have found similar question on Laracasts as well but running the query returns Cardinality violation: 1241 Operand should contain 2 column(s):

$unote = UNote::with('units')
    ->whereHas('units', function ($query) use ($request) {
        $query->selectRaw("count(distinct id)")->whereIn('id', $request->unit_lists);
    }, '=', $u_list_count)
    ->get()
    ->pluck("id");

I also found a similar question here, but seems it is too expensive.

Here is the dummy SQL to get started: http://sqlfiddle.com/#!9/c3d1f7/1

Upvotes: 0

Views: 929

Answers (1)

miken32
miken32

Reputation: 42712

Because whereHas just adds a WHERE EXISTS clause to the query with your specified filters, a whereIn will indeed return true for any matches. One thing you could try is running a raw subquery to get a list of device IDs and compare it.

$search_ids = [49866, 49865];
sort($search_ids);
$search_ids = implode(",", $search_ids);

Unote::select("id")
    ->whereRaw(
        "(SELECT GROUP_CONCAT(unit_id ORDER BY unit_id) FROM unote_unit WHERE unote_id = unotes.id) = ?",
        [$search_ids]
    )
    ->get()
    ->pluck("id");

Note, if you have soft deletes enabled you will also want to filter out soft deleted items in the subquery.

Upvotes: 1

Related Questions