V8-archive: API request with nested filter throws error: Failed generating the SQL query

[Kinzi]

Bug Report

I have a table products with a M2M field related_products which references the same table. Products can be draft or published. I'm trying to filter so the related_products are only populated with published products.

Steps to Reproduce

1. Create a table products with a status field and a M2M field related_products referencing the same table. autogenerate relations (make sure to fix same-name columns)
2. Fill it with a few dummy products in draft and published state, and enter a few relations
3. Query the api items/products?filter[related_products.status][eq]=published&fields=*.*.*.*&status=published
4. See error

"code": 9,
 "message": "Failed generating the SQL query. Statement could not be executed (42S22 - 1054 - Unknown column 'product.related_products' in 'field list')",

The created MSQL query seems to make sense though as far as I can tell:

(SELECT `products`.`related_products` AS `related_products` FROM `products` WHERE `products`.`related_products` IN (SELECT `products`.`id` AS `id` FROM `products` WHERE `products`.`status` = 'published'))

Expected Behavior

api response with related_products only populated with "published" products

Actual Behavior

SQL error

Other Context & Screenshots

I'm trying to recreate this on the demo installation but keep crashing the server I think 🤔

[itsmerhp]

Hello @Kinzi
Thank you for reporting this issue.

I have followed steps as you have mentioned, but this issue is not reproducing.
It will be great help if you provide your dump sql file to reproduce. If it's private then you can provide it in Slack too!

I have also few questions regarding steps you have shared to reproduce this issue :
1) Are you using latest release for APIs?

2) As mentioned in steps, while you are creating M2M field, in relation tab, you are auto generating junction table.
Same collections are relating in M2M field, and due to that auto generated junction table will have same fields name(which are used to relate both collection).
Question : Do you keep fields name same as it is auto generated for junction table?
If yes, then you must have to give different names for both fields of junction table which are relating both collections.

3) As I can see in your steps, you want to filter products based on related products' status.
To achieve that, API endpoint should be, items/products?filter[related_products.product2_id.status][eq]=published&fields=*.*.*.*&status=published
Here, related_products is used to relate products collection with junction collection and product2_id is relating junction collection with second products collection.

@benhaynes I have noticed a bug in app and api both, if one is relating same collection with M2M field and auto generating junction table, then the generated field names must be different. And API is throwing an exception if same names are passed but M2M relation field is creating, I think field should not be created, exception should throw before and restrict to create this field.

[Kinzi]

@itsmerhp Thanks for getting back to me. I'm sure 2. is OK and I'm also pretty sure I tried 3.

Will look into it again and get back to you!

[Kinzi]

@itsmerhp so just ran some checks:

1. yes latest git clone build
2. field names are mended to be unique
3. updated the query accordingly

With the latest version of the API I don't get the error I posted above anymore - alas, the filter doesn't work. Which means I get all related products including the ones that are set to draft

[itsmerhp]

Hello @Kinzi
Can you please share API end point with filter query string parameters you are trying with and dump sql file to reproduce the issue?
If it's private then you can provide it in Slack too!

[Kinzi]

@itsmerhp ~whats your handle on slack?~ I think I sent it to the right one :)