V8-archive: has filter seems broken

[nstCactus]

Bug Report

Steps to Reproduce

Considering the following tables:

---------- -------------------- ------------- | movies | | movies_directors | | directors | |--------| |------------------| |-----------| | | | id | | | | id | ← | movies_id | | | | | | directors_id | → | id | ---------- -------------------- -------------

On the movies table I have a M2M field names directors and related to the
directors table.

I'd like to fetch all movies having more than one director.
From the documentation :

Using has will return items that has at least the minimum number given as
related items.

Hence I make the following request: /items/movies?filter[directors][has]=1.

Expected Behavior

The request should return all movies having more than one director.
The generated query should be something like:

sql SELECT movies.id, title FROM movies LEFT JOIN movies_directors ON movies.id = movies_directors.movies_id GROUP BY movies.id HAVING COUNT(movies_directors.id) > 1

Actual Behavior

I got results but not what I expected. As I didn't understand how they were
filtered, I checked the MySQL query log. Here is the generated query :

sql SELECT movies.id AS id, movies.title AS title FROM movies WHERE movies.id IN ( SELECT movies_directors.movies_id AS movies_id FROM movies RIGHT JOIN movies_directors ON movies.id = movies_directors.movies_id WHERE movies_directors.id LIKE 1 OR movies_directors.movies_id LIKE 1 OR movies_directors.directors_id LIKE 1 ) ORDER BY movies.id IS NULL, movies.id ASC, movies.id ASC LIMIT 200 OFFSET 0

Other Context & Screenshots

Simplified database dump (should be restorable on a running Directus instance)

```sql INSERT INTOdirectus_collections(collection,managed,hidden,single,icon,note,translation) VALUES ('directors', 1, 0, 0, NULL, NULL, NULL), ('movies', 1, 0, 0, NULL, NULL, NULL), ('movies_directors', 1, 1, 0, NULL, 'Junction Collection', NULL); INSERT INTOdirectus_fields(collection,field,type,interface,options,locked,validation,required,readonly,hidden_detail,hidden_browse,sort,width,group,note,translation) VALUES ('movies', 'id', 'integer', 'primary-key', NULL, 0, NULL, 0, 0, 1, 1, 0, NULL, NULL, NULL, NULL), ('movies', 'title', 'string', 'text-input', '{\"trim\":true,\"showCharacterCount\":true,\"formatValue\":false,\"width\":\"auto\"}', 0, NULL, 1, 0, 0, 0, NULL, 'full', NULL, NULL, NULL), ('directors', 'id', 'integer', 'primary-key', NULL, 0, NULL, 0, 0, 1, 1, 0, NULL, NULL, NULL, NULL), ('directors', 'name', 'string', 'text-input', '{\"trim\":true,\"showCharacterCount\":true,\"formatValue\":false,\"width\":\"auto\"}', 0, NULL, 1, 0, 0, 0, NULL, 'full', NULL, NULL, NULL), ('movies', 'directors', 'o2m', 'many-to-many', '{\"fields\":\"name\",\"template\":\"{{ name }} - {{ director.name }} - {{ director.director_id.name }} - {{ director.director_id }}\",\"preferences\":{\"viewType\":\"tabular\",\"viewQuery\":{\"fields\":[\"name\"]}}}', 0, NULL, 0, 0, 0, 0, NULL, 'full', NULL, NULL, NULL), ('movies_directors', 'id', 'integer', 'primary-key', NULL, 0, NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL), ('movies_directors', 'movies_id', 'integer', 'numeric', NULL, 0, NULL, 1, 0, 0, 0, 0, NULL, NULL, NULL, NULL), ('movies_directors', 'directors_id', 'integer', 'numeric', NULL, 0, NULL, 1, 0, 0, 0, 0, NULL, NULL, NULL, NULL), ('directors', 'movies', 'o2m', 'many-to-many', '{\"fields\":\"title\"}', 0, NULL, 1, 0, 0, 0, NULL, 'full', NULL, NULL, NULL); INSERT INTOdirectus_relations(collection_many,field_many,collection_one,field_one,junction_field`)
VALUES ('movies_directors', 'movies_id', 'movies', 'directors', 'directors_id'),
('movies_directors', 'directors_id', 'directors', NULL, 'movies_id'),
('movies_directors', 'directors_id', 'directors', 'movies', 'movies_id'),
('movies_directors', 'movies_id', 'movies', NULL, 'directors_id');

CREATE TABLE directors (
id int(15) unsigned NOT NULL AUTO_INCREMENT,
name varchar(200) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO directors (id, name)
VALUES (1, 'Francis Ford Coppola'),
(2, 'Woody Allen'),
(3, 'Martin Scorsese'),
(4, 'Joel Coen'),
(5, 'Ethan Coen');

CREATE TABLE movies (
id int(15) unsigned NOT NULL AUTO_INCREMENT,
title varchar(200) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
INSERT INTO movies (id, title)
VALUES (1, 'New York Stories'),
(2, 'The big Lebowski'),
(3, 'The departed'),
(4, 'Test');

CREATE TABLE movies_directors (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
movies_id int(10) unsigned DEFAULT NULL,
directors_id int(10) unsigned DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO movies_directors (id, movies_id, directors_id)
VALUES (1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 2, 4),
(5, 2, 5),
(6, 3, 3);
````

Technical Details

• Device: Desktop
• OS: MacOS 10.14.5
• Web Server: Apache 2.4.34
• PHP Version: 7.1
• Database: MariaDB 10.2.19
• Install Method: cloned master branch (f28209b515b19b53b9a9a8a34a7ab2010210d4f9)

[nstCactus]

I forgot to mention that I'd be happy to help fix this, but I'd need help to know where to start digging!

[benhaynes]

Thanks @nstCactus!!! ❤️

We're on it and will try to get this resolved. In the meantime, @bjgajjar is our API Lead and can help you find things if you want to help resolve this. You can discuss here, or on our Slack:

https://directus.chat

[nstCactus]

While trying to fix this, I realised we're missing some features here.
The has filter allows the user to fetch records having at least x related records. It would be nice to have other "operators":

| operator | generated SQL |
| ----------- | ----------------------- |
| at_least | HAVING COUNT(id) >= 1 |
| up_to | HAVING COUNT(id) <= 1 |
| more_than | HAVING COUNT(id) > 1 |
| less_than | HAVING COUNT(id) < 1 |
| exactly | HAVING COUNT(id) = 1 |

The has filter syntax would have to be adapted to something like : filter[<column>][has][<operator>]=<value>. The <operator> could even be omitted and default to at_least to preserve the currently documented behavior.

@bjgajjar What do you think about this?

[benhaynes]

An interesting idea for extending the has filter. Any reason not to use a more consistent operator? We already use things like <= or lte so would those work or is there a reason to use what you have outlined?

https://docs.directus.io/api/reference.html#filter-operators

[nstCactus]

Nope, those are perfectly fine, even more if they're already used elsewhere
in the API!

On 18 June 2019 at 17:45:13, Ben Haynes ([email protected]) wrote:

An interesting idea for extending the has filter. Any reason not to use a
more consistent operator? We already use things like <= or lte so would
those work or is there a reason to use what you have outlined?

https://docs.directus.io/api/reference.html#filter-operators

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/directus/api/issues/1016?email_source=notifications&email_token=AACWMMY4HMISAAUVPSXD6MTP3D7ITA5CNFSM4HXYVI72YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODX7CIJI#issuecomment-503194661,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AACWMM2MV3HK6CU7YV24BR3P3D7ITANCNFSM4HXYVI7Q
.

[bjgajjar]

Fixed in #1041