_ncontains doesn't work on o2m nested dataset

[nordcart]

Filter not working properly on nested items (on many to one or one to many)

• Left column: ID
  id: 110

• Right column: Jakso (info from another collection)
  id: 110 --> 1/2021, 2/2021, 3/2021

Trying to only show the ID:s which does not have 2/2021 anywhere in "Jakso" (screenshot).

In the screenshot ID:110 has got 2/2021, but is still shown.

[rijkvanzanten]

@martijnboland Sounds like we've found the limit of the subquery! Negation doesn't work how you would expect it.

Take the following minimal example:

authors & articles
articles.author -> authors.id

Fetch all authors that have an article where title IS NOT "Test"

The following SQL is used

SELECT
	"authors"."id",
	"authors"."name"
FROM
	"authors"
WHERE
	"authors"."id" in(
		SELECT
			"articles"."author" AS "author"
		FROM
			"articles"
		WHERE
			NOT "articles"."title" = 'Test'
	)
ORDER BY
	"authors"."id" ASC

However, this still fetches the top level author as soon as they have any other article that matches the search query 🤔

[martijnboland]

@rijkvanzanten ah crap, well maybe there is a possibility to generate a 'where exists' subquery instead of the 'where in'? Or wouldn't that make a difference? Still early morning here...

[rijkvanzanten]

@martijnboland Sounds like a potential solution! Wondering how that affects the negation though 🤔 I believe you can do a "where not exists", but then we might have the opposite problem (where regular filters stop functioning)

[nordcart]

Any news on this?

[nordcart]

Has this been forgotten? There are probably more important bugs, but just asking :)

[rijkvanzanten]

Certainly hasn't been forgotten. Unfortunately nobody else seemed to have ran into this issue, fixing it takes quite a lot of rethinking on how nested queries are constructed, and there are other more important bugs that took priority..

We recognize that this may be an important feature/fix, but we are a small open-source organization with a lot to triage and complete. As of now, the only way to get an accurate timeline or release date is to sponsor this task.

[nordcart]

I don´t know what happend, I viewed this issue and it got automatically closed. Sorry.

[joselcvarela]

Hello,
I was trying to check if this got solved in the meantime but this seems a though one.
This will happen for all nested filters containing not operations.
Since we are recursively applying the filters, it will need a proper refactor in order to achieve the correct behaviour.

Instead of select ... where in (select .. where not ...), I think we will need select ... where not in (select .. where ...).
This requires that not operation needs to be converted into the affirmative ones(ie, neq to eq) and the not would be on root level.

[krazyjakee]

Just to note that this also affects insights. For example, to find all items in a collection where an M2M field is empty is simply not possible.

I also can't think of reasonable workarounds...

• Interact with Postgres directly and figuring out the queries. Reliant on having no breaking changes in the postgres structure.
• Have a separate background worker that downloads all items in the collection through the API and then performs logic on the data to get the item IDs where the M2M field is an empty array. Not scalable.

Both of which are pretty ugly solutions. Any other suggestions welcome

[nordcart]

I believe the Directus-team finds a solution.. no worries 😃. I think this feature would be really useful.

[nordcart]

I don´t think #12082 solves this..did not work for me. #11737 should solve it.

[rijkvanzanten]

Linear: ENG-296