How do I do a semijoin using SQLAlchemy?

Question

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 the 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.

Answer 1

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')