On Tue, Jan 2, 2018 at 11:59 PM, Nikola Jankovic <[email protected]> wrote:
> I have an API endpoint that handles searches from the frontend. A search can
> have a dynamic amount of filters applied to it, including (1) sizes, (2)
> colors, (3) price, and (4) category that are passed through query
> parameters. Sizes and colors are passed as comma separated values (i.e.
> colors=Red,Blue,Orange) and are converted to lists on the backend. Price and
> Category are single values (i.e. price=20-300&category=Shirt).
>
> I'm having trouble constructing a query based on these filters. If multiple
> values could not be passed for any parameters I wouldn't have much of an
> issue - I could construct a dictionary based on the parameter name and
> unpack it in a .filter() call. That's not the case though - I need to
> implement an 'or' operator to accomodate the multiple possible colors and
> sizes.
>
> Additionally, each of these filters is optional, meaning some queries will
> comprise only some of them, which again complicates the query.
>
> And if that wasn't complicated enough - properties like sizes and colors are
> represented as a one to many relationship not simple categories. I have
> hybrid properties that return a list of possible sizes but using those in
> queries containing filter_by() raises issues.
>
> Does anyone know how I could even begin to structure a dynamic query like
> this?
Just build it up one step at a time, something like this:
q = session.query(YourObject)
if price is not None:
q = q.filter(YourObject.price == price)
if category is not None:
q = q.filter(YourObject.category == category)
if sizes:
q = q.join(Size).filter(Size.size.in_(sizes))
if colors:
q = q.join(Color).filter(Color.name.in_(colors))
Hope that helps,
Simon
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
