CODEWITHSUNDEEP
pythonsqlalchemy
Jacob Davis-Hansson
Jacob Davis-Hansson

Reputation: 2663

Filter by labelled column in union'd SQLAlchemy query

Realizing the title is nearly equivalent to How to use a labelled column in sqlalchemy filter? I believe this is a separate issue.

To vastly simplify my issue: Say I have two queries that each return a subset of the result I want, that I union together:

initial_task = "initial_task"
scheduled_task = "scheduled_task"

initial = session.query(Task.task_id,
                        User.signup_date.label('due_date'),
                        literal(initial_task).label('type'))\
                 .join(Task.user)

schedule = session.query(Task.task_id,
                        Schedule.due_date.label('due_date'),
                        literal(scheduled_task).label('type'))\
                  .join(Task.schedule)

tasks = initial.union_all(schedule)

And to be clear: I realize this example could be rewritten without the union; my actual use case has five separate queries with almost nothing in common outside of the result being coercible to this normal format.

How can I filter tasks to only include tasks that are due after April 1st 2017? Conceptually, something like:

tasks.filter(tasks.c.due_date >= datetime(2017, 4, 1))

The main issue is I can't figure out how to refer to the due_date column in a general way. Everything I try from the docs seems to be talking about the lower level API, and on the ORM layer leads to:

'Query' object has no attribute 'c'

Upvotes: 6

Views: 3597

Answers (2)

Predrag Peranović
Predrag Peranović

Reputation: 31

Creating a column definition directly inside the filter should work. You could try the following code:

    import sqlalchemy as sa
    
    tasks.filter(sa.Column(sa.Date, name="due_date") >= datetime(2017, 4, 1))

or

    from sqlalchemy.sql.expression import column
    
    tasks.filter(column("due_date") >= datetime(2017, 4, 1))

Upvotes: 3

Ilja Everilä
Ilja Everilä

Reputation: 52949

The Query.union_all() method produces a new Query instance, which is a bit different from the CompoundSelect produced by the sql.expression.union_all() construct, as you've noted. You could use literal_column() to filter the query:

In [18]: tasks.filter(literal_column('due_date') >= datetime(2017, 4, 1))
Out[18]: <sqlalchemy.orm.query.Query at 0x7f1d2e191b38>

In [19]: print(_)
SELECT anon_1.task_id AS anon_1_task_id, anon_1.due_date AS anon_1_due_date, anon_1.type AS anon_1_type 
FROM (SELECT task.id AS task_id, user.signup_date AS due_date, ? AS type 
FROM task JOIN user ON task.id = user.task_id UNION ALL SELECT task.id AS task_id, schedule.due_date AS due_date, ? AS type 
FROM task JOIN schedule ON task.id = schedule.task_id) AS anon_1 
WHERE due_date >= ?

On the other hand you could just filter the parts of the union separately on their respective date columns.

Finally, you could wrap your union in a subquery, if that is closer to your actual goal (hidden by your simplified example):

In [26]: tasks_sq = tasks.subquery()

In [27]: session.query(tasks_sq).\
    ...:     filter(tasks_sq.c.due_date >= datetime(2017, 4, 1))
Out[27]: <sqlalchemy.orm.query.Query at 0x7f1d2e1d4828>

In [28]: print(_)
SELECT anon_1.task_id AS anon_1_task_id, anon_1.due_date AS anon_1_due_date, anon_1.type AS anon_1_type 
FROM (SELECT anon_2.task_id AS task_id, anon_2.due_date AS due_date, anon_2.type AS type 
FROM (SELECT task.id AS task_id, user.signup_date AS due_date, ? AS type 
FROM task JOIN user ON task.id = user.task_id UNION ALL SELECT task.id AS task_id, schedule.due_date AS due_date, ? AS type 
FROM task JOIN schedule ON task.id = schedule.task_id) AS anon_2) AS anon_1 
WHERE anon_1.due_date >= ?

Upvotes: 7

Related Questions