Features request: Filter with OR

[xunto]

Query object has filter function which (in some way) dynamically adds another condition to if in sql-query. Good replacement for query builder but the condition is added with AND. As I know there is no way to dynamically add such condition with OR.

Usecase (example):

from pony.orm import *

db = Database()

class News(db.Entity):
    id = PrimaryKey(int, auto=True)
    tags = Set('Tag')


class Tag(db.Entity):
    id = PrimaryKey(int, auto=True)
    news = Set(News)


db.bind("sqlite", "data.sqlite", create_db=True)
db.generate_mapping(create_tables=True)

In such situatuion I see no way to select all news which have atleast one tag from given array using one sql request.

Maybe I'm just missing something. But if not... I have some ideas about how to make it possible.

[xunto]

Solved my problem.

tags = [ ... ]
news = select(
    news 
    for news in orm.News
    for tag in news.tags
    if tag in tags
)

But maybe we still need this feature anyway?

[xunto]

Eh, actually not completely because this will not return news if news has no tags even if tags array is empty. (Adding "if not tags" doesn't help and even if it would it's too hacky)

[kozlovsky]

Filters with OR may be an error-prone feature. If we have a query like that:

query = select(obj for obj in MyEntity if A and B and C)

and then add OR filter condition using some hypothetical API:

query = query.or_filter(D)

then it will be equivalent to a query

select(obj for obj in MyEntity if (A and B and C) or D)

whereas the intended query may be actually

select(obj for obj in MyEntity if A and B and (C or D))

or

select(obj for obj in MyEntity if A and ((B and C) or D))

That is, programmer may thinks mistakenly that the important A condition will always be checked, but objects with D condition satisfied will be retrieved even if A condition is not satisfied for that objects.

Regarding your query, I think you may write it in the following way:

tags = [ ... ]
news = left_join(
    news 
    for news in orm.News
    for tag in news.tags
    if (tag in tags or not tags)
)

I added or not tags condition to select all news if tag list is empty. Also I replaced select with left_join to return news which has no tags if the list of required tags is empty.

I agree that the query does not look very intuitive, but don't see good API which can be suggested as an alternative

[xunto]

@kozlovsky, my idea is:

query = select(obj for obj in MyEntity if A and B and C)
query = query.filter([D, E])
query = query.filter(F)

Will be equivalent to:

select(obj for obj in MyEntity if A and B and C and (D or E) and F)

So by creating array of lambdas we can compose OR statements.

[fluffy-critter]

Filters with OR may be an error-prone feature. If we have a query like that:

...

then it will be equivalent to a query

select(obj for obj in MyEntity if (A and B and C) or D)

whereas the intended query may be actually

select(obj for obj in MyEntity if A and B and (C or D))

This objection is pretty reasonable, but maybe a different nomenclature for the API would clarify it. Instead of query.or_filter(query2), how about query.union(query2)? That feels like that would make it obvious that it's unioning two disjoint queries together, rather than adding a single OR clause to the existing query expression.

[BlueMagma2]

query.union(query2)

has this ever been implemented ? this is exactly what I would need right now

[Zocker1999NET]

query.union(query2)

has this ever been implemented ? this is exactly what I would need right now

no, at least I didn't found it either. Also, supporting SQL UNION was already asked for in #512 and I think this could be used really simple to have an or_filter/union like proposed here