Adding a join to an SQL Alchemy expression that already has a select_from()

Question

Note: this is a question about SQL Alchemy's expression language not the ORM

SQL Alchemy is fine for adding WHERE or HAVING clauses to an existing query:

q = select([bmt_gene.c.id]).select_from(bmt_gene)
q = q.where(bmt_gene.c.ensembl_id == "ENSG00000000457")
print q

SELECT bmt_gene.id 
FROM bmt_gene 
WHERE bmt_gene.ensembl_id = %s

---

However if you try to add a JOIN in the same way you'll get an exception:

q = select([bmt_gene.c.id]).select_from(bmt_gene)
q = q.join(bmt_gene_name)

sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'Select object' and 'bmt_gene_name'

---

If you specify the columns it creates a subquery (which is incomplete SQL anyway):

q = select([bmt_gene.c.id]).select_from(bmt_gene)
q = q.join(bmt_gene_name, q.c.id == bmt_gene_name.c.gene_id)

(SELECT bmt_gene.id AS id FROM bmt_gene)
JOIN bmt_gene_name ON id = bmt_gene_name.gene_id

---

But what I actually want is this:

SELECT
     bmt_gene.id AS id 
FROM
     bmt_gene
     JOIN bmt_gene_name ON id = bmt_gene_name.gene_id

edit: Adding the JOIN has to be after the creation of the initial query expression q. The idea is that I make a basic query skeleton then I iterate over all the joins requested by the user and add them to the query.

Can this be done in SQL Alchemy?

Answer 1

The first error (NoForeignKeysError) means that your table lacks foreign key definition. Fix this if you don't want to write join clauses by hand:

from sqlalchemy.types import Integer
from sqlalchemy.schema import MetaData, Table, Column, ForeignKey

meta = MetaData()

bmt_gene_name = Table(
    'bmt_gene_name', meta,
    Column('id', Integer, primary_key=True),
    Column('gene_id', Integer, ForeignKey('bmt_gene.id')),
    # ...
)

The joins in SQLAlchemy expression language work a little bit different from what you expect. You need to create Join object where you join all the tables and only then provide it to Select object:

q = select([bmt_gene.c.id])
q = q.where(bmt_gene.c.ensembl_id == 'ENSG00000000457')

j = bmt_gene  # Initial table to join.
table_list = [bmt_gene_name, some_other_table, ...]
for table in table_list:
    j = j.join(table)
q = q.select_from(j)

The reason why you see the subquery in your join is that Select object is treated like a table (which essentially it is) which you asked to join to another table.

Answer 2

You can access the current select_from of a query with the froms attribute, and then join it with another table and update the select_from.

As explained in the documentation, calling select_from usually adds another selectable to the FROM list, however:

Passing a Join that refers to an already present Table or other selectable will have the effect of concealing the presence of that selectable as an individual element in the rendered FROM list, instead rendering it into a JOIN clause.

So you can add a join like this, for example:

q = select([bmt_gene.c.id]).select_from(bmt_gene)
q = q.select_from(
    join(q.froms[0], bmt_gene_name,
         bmt_gene.c.id == bmt_gene_name.c.gene_id)
)