Double many-to-many relation filter headache

[Vlad K.]

Hello list,

I don't think my brain is currently capable of figuring out the SQL
itself, let alone how to do it with SQLA.

I've got the following models:

City
Location
Item
Category


Location belongs to one of many Cities via simple Location.city_id
foreign key
Location belongs to one or more Categories via LocationCategoryAssoc
relationship(secondary) many-to-many
Item belongs to one or more Location via LocationItemAssoc
relationship(secondary) many-to-many

I need to list Items that:
- have Item.some_flag == True
- are present in Location X (have relationship with Location.id == X)
- belong to Category Y (have relationship with Category.category_id == Y)

Affected properties:

City.city_id
Category.category_id

Location.city_id

LocationCategoryAssoc.location_id (fkey to Location)
LocationCategoryAssoc.category_id (fkey to Category)

LocationItemAssoc.location_id (fkey to Location)
LocationItemAssoc.item_id (fkey to Item)

Item.some_flag



The Item.some_flag == True is simple, of course, but I'm not sure how to
construct the query, joins and filters for the rest. The result I want
is a list of Item instances.



Many thanks.

--


.oO V Oo.


Work Hard,
Increase Production,
Prevent Accidents,
and
Be Happy! ;)

[Vlad K.]

On 09/28/2012 12:38 PM, Vlad K. wrote:
> I need to list Items that:
> - have Item.some_flag == True
> - are present in Location X (have relationship with Location.id == X)
> - belong to Category Y (have relationship with Category.category_id == Y)
>

My apologies, see how burnt out I am. :)

- are present in Location X (have relationship with Location.id == X)

is wrong, I need this:


- are present in City X (have relationship with Location that has
Location.city_id == X)

[Vlad K.]

To answer my own question, seems like SQLA won't automatically process
inner joins if you supply individual columns to the session.query(), if
had to pass the declarative model class itself for this to work as expected.

Unless I'm doing something wrong, I guess I should use deferred columns
to avoid loading unnecessary data for lists... (that's why I was
querying individual columns instead of entire model).

[Michael Bayer]

On Oct 1, 2012, at 7:21 AM, Vlad K. wrote:

>
>
> To answer my own question, seems like SQLA won't automatically process inner joins if you supply individual columns to the session.query(), if had to pass the declarative model class itself for this to work as expected.

If I understand correctly, this isn't accurate. Your query needs the usage of query.join(), and this usage is not related to whether or not you supply a complete entity to select from.

query(MyClass.x, MyClass.y, MyOtherClass. q).join(MyClass.otherclass)

is the primary form you'd use here. you can also set the first join source explicitly if needed using query.select_from(MyClass).join(<etc>) if you're composing a particular string of joins.

I'm not sure what "won't automatically process inner joins" means, does that mean, you get an error message, or you're forced to call .join() as opposed to some other system of joining two entites, or what.


I see that for all the extensive docs there are on join, none of them go out of their way to make it clear that it can be any series of columns or entities selected from....

[Vlad K.]

On 10/01/2012 04:48 PM, Michael Bayer wrote:
> If I understand correctly, this isn't accurate. Your query needs the usage of query.join(), and this usage is not related to whether or not you supply a complete entity to select from.

I wasn't using explicit .join(), except an outerjoin on a one-to-one
relation with images table (which by default is lazyloaded). I thought
SQLA would figure it out itself based on foreign keys, as it usually
does. Maybe that's wrong, maybe I should be explicit, but so far it
worked (when entire instances of declarative_base were given to
session.query()).

I forgot to mention, and this could be the cause of perceived problems,
that I was listing columns of more than one model in the
session.query(). Maybe that confused it.

What happened was that the query returned a list of named tuples for
each of the rows returned by the database, while I was expecting only
one instance of the main model (see below).

> I'm not sure what "won't automatically process inner joins" means, does that mean, you get an error message, or you're forced to call .join() as opposed to some other system of joining two entites, or what.

I mean the cartesian cross-product in queries with many-to-many
relations. SQLA would "automatically" recognize the queried models and
construct the model instances and their relationships properly.

E.g. if I query for single model that has X related in a many-to-many
fashion, the query would return X number of rows, like with
.joinedload(), but SQLA would return only one instance of the model, and
its related property filled with a list of X instances of that model,
i.e it would automatically process the joins and create a relationship
tree in the session.

I hope I'm making sense here. :)

I guess I should make a minimal example case for this, and will do as
time permits.

[Michael Bayer]

I have a hunch what you're doing here. There is no "automatic" recognition of queried models and rendering of joins based on that, with the exception of joined eager loading which is something different. What there is, is that if you query for whole entities only, the Query will *de-duplicate* entries as rows are received. This is also so that strategies like joined eager loading allow the lead entry to be returned uniquely.

So that would mean, you're getting many, many duplicate rows due to plain cartesian product, and the deduping of entities is hiding it. turn on echo='debug' on your create_engine() to see what's going on, then you'd probably want to start using join() to get the actual SQL you're looking for.