Can't filter on Join?

[Michael P. McDonnell]

Hey team -

I'm trying to figure out how to basically rewrite this:

SELECT

count(task.id) 

FROM task

JOIN round on task.game_id = round.game_id

JOIN tournament ON round.tournament_id = tournament.id

WHERE tournament.id = '626aeaa7-783b-415c-85f9-5222d9c95973';

As this:

total_tasks = column_property(

        select([func.count(Task.id)])

        .outerjoin(Round, Task.game_id == Round.game_id)

        .filter(round.tournament_id == tournament_id)

        .correlate_except(Task))

I keep getting the following error:

AttributeError: 'Join' object has no attribute 'filter'

Which I *know* tells me that a join object has no method called "filter", but there's also no "where", "filter_by" etc...

What painfully obvious thing am I missing?

[Mike Bayer]

hi -

you're doing a classic mistake-ish thing that will be less possible in 1.4 and the longer term plan is by 2.0 all confusion will be eliminated, which is that you are using the Core select() construct in the way you would use the ORM Query object, when in fact these are two totally different objects that work very differently.

To do a join (and filter) with Core select():

from sqlalchemy import outerjoin, select

j = outerjoin(Task, Round, Task.game_id== Round.game_id)

stmt = select([func.count(..)]).select_from(j).where(...).correlate_except(...)

basically:

1. do not call select.join() or select.outerjoin(). These methods are 100% useless, unfortunately, and are gone in 1.4.      use select.select_from(join(A, B, onclause)).

2. select() doesn't have filter(), it has where().  

For now, as we are still in SQLAlchemy medieval times, background on how to use select() is at https://docs.sqlalchemy.org/en/13/core/tutorial.html#selecting

--

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 sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ad3b421d-bf9f-43bd-bd1a-061136572200%40googlegroups.com.

[Michael P. McDonnell]

I'm sorry this took me so long - but thank you. This is actually much cleaner syntax and I like it. 

Thanks for all you do!

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d465c941-6441-46d3-8bbe-315d857ff7bb%40www.fastmail.com.