Support nested queries for ComplexFilterBackend

[gatensj]

Hey everybody. I’m using Django-rest-framework-filters for a project and it is great. Reason for message, is I’m running into issue with complex operations. Specifically I’m having trouble with grouping logic.

• Python: 3.6.8
• Django 2.1.1
• DRF 3.10.2
• DRF-F 1.0.0.dev0

This is the unencoded filter:

(vendor_code=clrvt) & (subscriber_code=tamu | subscriber_code=princeton)

Which encodes to
%28vendor_code%3Dclrvt%29+%26+%28subscriber_code%3Dtamu+%7C+subscriber_code%3Dprinceton%29

*** When I try to view this in the Browsable API, it returns a count however the results are an empty list, because the generated SQL can not parse the nested conditional ("wrdsrpts_rep_usage"."usage"."subscriber_code" = 'tamu | subscriber_code=princeton'):

SELECT
"wrdsrpts_rep_usage"."usage"."access_method", "wrdsrpts_rep_usage"."usage"."compress", "wrdsrpts_rep_usage"."usage"."date", "wrdsrpts_rep_usage"."usage"."document", "wrdsrpts_rep_usage"."usage"."duration", "wrdsrpts_rep_usage"."usage"."file", "wrdsrpts_rep_usage"."usage"."format", "wrdsrpts_rep_usage"."usage"."ip", "wrdsrpts_rep_usage"."usage"."job_id", "wrdsrpts_rep_usage"."usage"."library", "wrdsrpts_rep_usage"."usage"."login_country_iso", "wrdsrpts_rep_usage"."usage"."method", "wrdsrpts_rep_usage"."usage"."observations", "wrdsrpts_rep_usage"."usage"."product", "wrdsrpts_rep_usage"."usage"."product_id", "wrdsrpts_rep_usage"."usage"."size", "wrdsrpts_rep_usage"."usage"."subscriber_city", "wrdsrpts_rep_usage"."usage"."subscriber_code", "wrdsrpts_rep_usage"."usage"."subscriber_country", "wrdsrpts_rep_usage"."usage"."subscriber_country_id", "wrdsrpts_rep_usage"."usage"."subscriber_id", "wrdsrpts_rep_usage"."usage"."subscriber_joindate", "wrdsrpts_rep_usage"."usage"."subscriber_latitude", "wrdsrpts_rep_usage"."usage"."subscriber_longitude", "wrdsrpts_rep_usage"."usage"."subscriber_name", "wrdsrpts_rep_usage"."usage"."subscriber_state", "wrdsrpts_rep_usage"."usage"."subscriber_type", "wrdsrpts_rep_usage"."usage"."success", "wrdsrpts_rep_usage"."usage"."time", "wrdsrpts_rep_usage"."usage"."time_end", "wrdsrpts_rep_usage"."usage"."times_accessed", "wrdsrpts_rep_usage"."usage"."times_accessed_taq", "wrdsrpts_rep_usage"."usage"."user_dept", "wrdsrpts_rep_usage"."usage"."user_email", "wrdsrpts_rep_usage"."usage"."user_first_name", "wrdsrpts_rep_usage"."usage"."user_id", "wrdsrpts_rep_usage"."usage"."user_last_name", "wrdsrpts_rep_usage"."usage"."user_login_count", "wrdsrpts_rep_usage"."usage"."user_representative", "wrdsrpts_rep_usage"."usage"."user_sponsor", "wrdsrpts_rep_usage"."usage"."user_type", "wrdsrpts_rep_usage"."usage"."user_username", "wrdsrpts_rep_usage"."usage"."vendor_code", "wrdsrpts_rep_usage"."usage"."vendor_id", "wrdsrpts_rep_usage"."usage"."vendor_name", "wrdsrpts_rep_usage"."usage"."wrds_tier"
FROM "wrdsrpts_rep_usage"."usage" 
WHERE ("wrdsrpts_rep_usage"."usage"."vendor_code" = 'clrvt' AND "wrdsrpts_rep_usage"."usage"."subscriber_code" = 'tamu | subscriber_code=princeton') LIMIT 10

I tried adding containing parentheses. This is the unencoded filter:

((vendor_code=clrvt) & (subscriber_code=tamu | subscriber_code=princeton))

Which encodes to
%28%28vendor_code%3Dclrvt%29+%26+%28subscriber_code%3Dtamu+%7C+subscriber_code%3Dprinceton%29%29

*** When I try to view this in the Browsable API, It returns a status 400 for a bad request. The message is Ending querystring must not have trailing characters. Matched: ')'.

It’s possible that I may have misunderstood the proper syntax. If this is the case, please let me and I’ll be happy to issue a PR to update the documentation. Any and all help on this front will greatly be appreciated. Thanks for your help. Take care and talk to you soon.

[rpkilby]

Hi @gatensj - glad to hear someone is using the complex ops backend! Individual groups are your traditional query strings, so (param1=value1&param2=value2). It isn't possible to implement OR operations here, since | isn't part of the query string syntax. | is only supported between groups.

Your second attempt fails because nested groups aren't supported. Implementing a backend that supports ((a=1) | (b=2)) & ((c=3) | (d=4)) would be possible, but much more complicated.

---

That all said, in this specific case it looks like you want to match against multiple subscriber codes. You could get away with a CSV-based in filter. The unecoded filter could look like

(vendor_code=clrvt) & (subscriber_code=tamu,princeton)

Although, you don't really need the complex backend for the query since it's equivalent to

vendor_code=clrvt&subscriber_code=tamu,princeton

It might be good to update the documentation clarifying that individual groups match traditional query stings, and that each query string is processed by an individual filterset.

[FlipperPA]

@rpkilby Thanks, that would explain why. Do you have any interest in working on a backend that supports nested groups? We'd be willing to help, and might be able to get some finances to back the effort. (@gatensj is on vacation, but will be back Monday)

[gatensj]

Hey gang. I'm going to take a stab at this problem. Originally I was looking at parsing the string using the pyparsing package. If that proves problematic, I might end up parsing the string with the sqlparse package.

Anyway here’s a link to my fork - I’ll be pushing every night. Feel free to follow along. Please let me know if you have any questions and I'll respond accordingly. Thanks. Take care.

https://github.com/gatensj/django-rest-framework-filters

[patrickcash]

Hey, sorry if this is a little off topic but I am looking at using the complex backend and I am a little confused. So do i need to write the code to parse the query string and encode it or is there a library you are using to do that? Also where do I do that at? Is there some example code I can look at?

[rpkilby]

Hi @patrickcash - I moved your question to #316.

---

Do you have any interest in working on a backend that supports nested groups?

I unfortunately don't have the bandwidth to implement this. The current implementation was only possible because I assumed certain limitations (specifically, no nesting 😄). That said, I'd be happy to help review any PR on this. Besides parsing the query, the most difficult part will be to properly nest any validation errors.

[ferrants]

Supported in this PR:
#321