Filtering the same field with OR operator results in AND in generated SQL
evolkmann opened this issue · 4 comments
I want to filter a text[]
field by checking if it contains the variable $documentType
. With a second variable I want to include or exclude records where the array field is null. To achieve this, I combine both conditions with or
, but the resulting SQL contains an and
, regardless if I use or
or and
in the GraphQL query.
I wonder if this is indeed a bug or if I use the filter options in the wrong way.
Versions / Settings
PostgreSQL
:13.2
pg
:8.6.0
postgraphile
:4.12.3
postgraphile-plugin-connection-filter
:2.2.1
- I have not set any
graphileBuildOptions
Minimal Schema
create table demo (
id serial primary key,
only_for_document_types text[]
);
create index on demo using GIN(only_for_document_types);
insert into demo
(only_for_document_types)
values
(null),
('{example}');
Query
query Demo(
$documentType: String
$includeRecordsAvailableForAllDocTypes: Boolean = true
) {
demo(
filter: {
or: {
onlyForDocumentTypes: { isNull: $includeFieldsAvailableForAllDocTypes }
}
onlyForDocumentTypes: { anyEqualTo: $documentType }
}
) {
nodes {
id
onlyForDocumentTypes
}
}
}
Variables
{
"documentType": "example"
}
Generated SQL
with __local_0__ as (
select to_json(
(
json_build_object(
'__identifiers'::text,
json_build_array(__local_1__."id"),
'id'::text,
(__local_1__."id"),
'onlyForDocumentTypes'::text,
(
case when (__local_1__."only_for_document_types") is null then null when coalesce(
array_length(
(__local_1__."only_for_document_types"),
1
),
0
) = 0 then '[]'::json else (
select json_agg(__local_2__)
from unnest((__local_1__."only_for_document_types")) as __local_2__
) end
)
)
)
) as "@nodes"
from (
select __local_1__.*
from "public"."demo" as __local_1__
where (
(
(
$1 = ANY (__local_1__."only_for_document_types")
)
)
# ⚠️ PROBLEM IS HERE ⚠️
# At this point an `and` combination is made, regardless of wether I specify `or` or `and`
# in the GraphQL query. If it was `or` (like I specified in the GraphQL query, it would return
# the expected results.
and (
(
((__local_1__."only_for_document_types" IS NULL))
)
)
) and (TRUE) and (TRUE)
order by __local_1__."id" ASC
) __local_1__
),
__local_3__ as (
select json_agg(
to_json(__local_0__)
) as data
from __local_0__
)
select coalesce(
(
select __local_3__.data
from __local_3__
),
'[]'::json
) as "data"
Simple, manually created query
The query below is the manual equivalent of what I would expect from my GraphQL query above and returns the expected results.
select *
from demo
where
only_for_document_types is null
or
'example' = any(only_for_document_types)
Workaround
fragment Nodes on DemoConnection {
nodes {
id
onlyForDocumentTypes
}
}
query Demo($documentType: String) {
demoByDocType: demo(
filter: { onlyForDocumentTypes: { anyEqualTo: $documentType } }
) {
...Nodes
}
demoForAllDocTypes: demo(
filter: { onlyForDocumentTypes: { isNull: true } }
) {
...Nodes
}
}
I can confirm it - OR filter doesn't work even for different fields - still getting AND instead :(
I don't think you're using the or
operator correctly. See here: #37 (comment)
The operators documentation page also hints at this. (it says "Checks for any expressions in this list.", and says it can be expressed in GraphQL as or: [T]
)
Looks like the correct way is something like:
query {
allClients(filter: {
or: [
{ sex: { in: [FEMALE] } },
{ sex: { isNull: true } }
]
}) {
nodes {
id
firstName
}
}
}
@Venryx thanks for that hint! It works correctly when I pass an array of filters. I did not discover this, because GraphiQL
constructs an object when I select multiple filters.
Ran into the same thing, I suppose that GraphiQL's construction is outside the scope of what is fixable here...