field_null filter not working

[ssoftwares]

Bug report

field_null filter not working

If I talk particularly of my strapi panel , i have two collections , one names "devices" and one named "patient", I have a one-to-one relation of device with patient (i.e device belong to one patient) . But when i want to fetch list of devices which is not assigned to any patient (i.e patient == null) , I use filter patient_null = true in get API as described in strapi official documentation, but the API returns empty array instead of list of patients. I have a lot of devices who is not assigned to any patient. I have tried same with patient API where device == null (device_null : true) , have also tried equals filter (device_eq: null) , nothing works. I have tried with other content types with a relation and have concluded that null filter doesn't work with relations . I have tried deleting my database and create everything from fresh. I have attached screenshots for same , please check them.

Steps to reproduce the behavior

1. Create a content-type with a one-to-one relation with another content-type
2. Hit get API passing filter field_null : true and vice-versa.
3. It will return empty array even if many rows exist whose field_null condition applies.
4. See error

Expected behavior

It should return an array of those entities whose relation field is null.

Screenshots

System

• Node.js version: v14.17.0
• NPM version: 6.14.13
• Strapi version: 3.6.6
• Database: sqlite
• Operating system: Local is windows, live server is running Linux (doesn't work on both)

Additional context

I have made a mistake choosing strapi for developing an enterprise application, I thought it has become stable but working on it I have concluded that minor internal issues keep popping up in strapi and also any change in content-type schema often leads to whole database corruption, strapi doesn't give any option to force delete a row which is having DB issue. There are many issues which needs to be resolved before strapi can be used in enterprise. Bulk uploading api's and required relation field check should be there in API's are must in strapi. I must admit strapi have made my work a lot easier . Strapi is like dreams come true except for some minor issues.
(Just honest feedback from a user who has been using strapi for his projects for nearly a year now. )

[ssoftwares]

Confirmed bug on latest strapi version 3.6.8 .

[derrickmehaffy]

duplicate: #5414

[ssoftwares]

@derrickmehaffy The duplicate post is using graphql for api and i am using rest API.

[ssoftwares]

This is a big issue and a core feature for strapi without which strapi can't be called cms. Why is it still not solved, why its severity is so low.

[derrickmehaffy]

@derrickmehaffy The duplicate post is using graphql for api and i am using rest API.

The issue doesn't come directly from the interface used with the _null filter as it's a database problem. One that we are already aware of in the v3 hence why I marked it as a duplicate.

This is a big issue and a core feature for strapi without which strapi can't be called cms. Why is it still not solved, why its severity is so low.

Severity is set based on how it impacts Strapi as a whole. This is a rough breakdown of how we set Severity:

Severity

Define the priority:

• severity: critical
  If the issue has a security impact or breaks core usage of the product.
  In this case the technical team have to hotfix it right away!
• severity: high
  If it breaks the basic use of the product.
  It makes Strapi unusable.
• severity: medium
  If it breaks the advanced use of the product
  If there is a workaround but should be fix.
• severity: low
  If the issue only affects a very niche base of users and/or an easily implemented workaround can solve.

---

We are already deeply aware that it's not possible to filter on relational fields with the _null filter and this will be fixed in our v4 version that we are currently working on. We have almost entirely rewritten the entire backend query and database layer meaning it would pointless to attempt to backport this to the v3 since the v4 will be released in ~2 months.

The _null filter still works on all normal fields and only impacts relational fields, hence it's considered a very niche usage.