[sqlalchemy] Optimally access a model paramater n number of relationships away

[Brian Leach]

Hi everyone!

I have asked this question on StackOverflow, please see it for full detail 
of how the models are laid 
out: 
http://stackoverflow.com/questions/36463623/optimally-access-an-sqlalchemy-model-paramater-n-number-of-relationships-away

I have some "test" models that all have a relationship back to a "location" 
model, each a varying number of relationships away. When I want to get the 
name of the "location" associated with a "test", there is a ton of SQL 
emitted. Is it possible to build in some sort of shortcut to the location 
relationship?

I have tried setting the 'lazy' parameter on the relationship to 'select', 
'joined', and 'subquery' although these do not seem to make a significant 
difference when it comes to tying a test model to its location.

The way I have things defined also make it difficult for me to filter a 
group of tests based on their location, since the relationship is so 
different with each. I do not even know how to join these tables using raw 
SQL or how to begin to approach trying to emulate what the ORM is 
constructing for me.

I would imagine that this has been nailed before and am hoping that one of 
you out there can fill me in on the missing concept.


 - Brian

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] trouble with advanced orm relationship (primary + secondary)

[Jonathan Vanasco]

On Wednesday, April 6, 2016 at 12:00:22 AM UTC-4, Mike Bayer wrote:
>
> does your test case show that?  I guess I can run it.
>

Thanks.  I think I finally got it. (!)

The confusion stemmed from how the `join()` required the start point on the 
first bit of the "secondary" but not on the latter (e.g. going from 3 
arguments to 2).

- secondary= "join(A2B, C, A2B.b_id == C.b_id)"
- ,primaryjoin="and_(A.id == A2B.a_id)"
- ,secondaryjoin=sqlalchemy.and_(
- C.b_id == sqlalchemy.orm.foreign(A2B.b_id),
+ secondary= "join(A2B, B, A2B.b_id == B.id).join(C, B.id == C.b_id)"
+ ,primaryjoin="and_(A.id == A2B.a_id)"
+ ,secondaryjoin=sqlalchemy.and_(
+ C.b_id == B.id,

The last bit I'm trying to figure out now, is to adding a correlated 
subquery to this (as that helped a lot with another query last week)

I believe the query should look a bit like this: (I joined B, because I 
couldn't find an expression version of orm's `foreign`)

C.id.in_(
sqlalchemy.select([C.id])
.select_from(
C.__table__
.join(B, C.b_id == B.id)
.join(A2B, B.id == A2B.b_id)
)
.where(A2B.a_id == A.id)
.order_by(C.id.desc())
.limit(5)
.correlate()
)

The main issue I've been facing is that sqlite generates an ON issue for 
this 

Otherwise, I've been trying to get it into primaryjoin and secondaryjoin 
with no luck.

I'm just using direct queries now.  This is all just for a spare-time 
project i'm open sourcing so I've only been tackling it at night and during 
lunch.

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.