How do I do a semijoin using SQLAlchemy?
http://en.wikipedia.org/wiki/Relational_algebra#Semijoin
Let's say that I have two tables: A and B. I want to make a query that would work similarly to th开发者_高级运维e following SQL statement using the SQLAlchemy orm:
SELECT A.*
FROM A, B
WHERE A.id = B.id
AND B.type = 'some type';
The thing is that I'm trying to separate out A and B's logic into different places. So I'd like to make two queries that I can define in separate places: one where A uses B as a subquery, but only returns rows from A. I'm sure this is fairly easy to do, but an example would be nice if someone could show me.
Let's assume you have models classes A
and B
mapped to corresponding tables.
The simplest case is when you have relation in A
pointing to B
, let's name it A.b
. Then you just use either A.b.has(type='some type')
or A.b.any(type='some type')
(depending on whether A.b
is scalar or represent a collection) as condition when querying A
model.
But you say you are trying to separate login. Does this mean there is no such relation? If so, you have to define join condition explicitly:
session.query(A).join((B, A.id==B.id)).filter(B.type=='some type')
精彩评论