Translating PSQL expression to Laravel Eloquent Builder expression

November 16, 2021

I am trying to return some result set

The PSQL query looks like this:

SELECT DISTINCT id
FROM example
WHERE foo='abc'
AND (
  bar='x'
  OR
  bar='y'
)
AND NOT (
  id = ANY (array_of_ids)
);

This returns the correct set of rows, not including any rows that have an id in the array_of_ids but also, importantly, does not return any rows where bar=z

I’ve attempted the following with Eloquent’s query builder:

DB::table("example")
    ->where("example.foo", "=", "abc")
    ->whereNotIn("example.id", $array_of_ids)
    ->OrWhere(function($query) {
        $query->where("example.bar", "=", "x")
            ->where("example.bar", "=", "y");
    })
    ->distinct()
    ->select("example.id");

Unfortunately, this both includes those rows that have an id in the array_of_ids, and also includes rows where bar=z which is undesired.

I’ve tried moving where the whereNotIn call is, as follows:

DB::table("example")
    ->where("example.foo", "=", "abc")
    ->OrWhere(function($query) {
        $query->where("example.bar", "=", "x")
            ->where("example.bar", "=", "y");
    })
    ->whereNotIn("example.id", $array_of_ids)
    ->distinct()
    ->select("example.id");

But the result is the same.

What am I doing wrong?

>Solution :

in your sql, I do not see any or…
however, or should be in example.bar comparison only …
so, to get the same result using Query Builder, I think your query builder should look like:

 DB::table("example")
            ->where("example.foo", "=", "abc")
            ->where(function($query) {
                $query->where("example.bar", "=", "x")
                    ->orWhere("example.bar", "=", "y");
            })
            ->whereNotIn("example.id", $array_of_ids)
            ->distinct()
            ->select("example.id");