Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Joining onto a subquery in SQLAlchemy

I have the follwing SQL query (It get's the largest of a certain column per group, with 3 things to group by):

select p1.Name, p1.nvr, p1.Arch, d1.repo, p1.Date
from Packages as p1 inner join
    Distribution as d1
    on p1.rpm_id = d1.rpm_id inner join (
        select Name, Arch, repo, max(Date) as Date
        from Packages inner join Distribution
            on Packages.rpm_id = Distribution.rpm_id
        where Name like 'p%' and repo not like '%staging'
        group by Name, Arch, repo
    ) as sq
    on p1.Name = sq.Name and p1.Arch = sq.Arch and d1.repo = sq.repo and p1.Date = sq.Date
    order by p1.nvr

And I'm trying to convert it to SQLAlchemy. This is what I have so far:

p1 = aliased(Packages)
d1 = aliased(Distribution)
sq = session.\
        query(
            Packages.Name,
            Packages.Arch,
            Distribution.repo,
            func.max(Packages.Date).\
                                        label('Date')).\
        select_from(
            Packages).\
        join(
            Distribution).\
        filter(
            queryfilter).\
        filter(
            not_(Distribution.repo.\
                                    like('%staging'))).\
        group_by(
            Packages.Name,
            Packages.Arch,
            Distribution.repo).subquery()

result = session.\
            query(
                p1, d1.repo).\
            select_from(
                p1).\
            join(
                d1).\
            join(
                sq,
                p1.Name==sq.c.Name,
                p1.Arch==sq.c.Arch,
                d1.repo==sq.c.repo,
                p1.Date==sq.c.Date).\
            order_by(p1.nvr).all()

The problem arises when I do the join on the subquery. I get an error that states that there is no from clause to join from. This is strange because I specify one right after the subquery in the join funciton as an argument. Any idea what I'm doing wrong? Perhaps I need to alias something and do a select_from again?

EDIT: Exact error

Could not find a FROM clause to join from. Tried joining to SELECT "Packages"."Name", "Packages"."Arch", "Distribution".repo, max("Packages"."Date") AS "Date" FROM "Packages" JOIN "Distribution" ON "Packages".rpm_id = "Distribution".rpm_id WHERE "Packages"."Name" LIKE :Name_1 AND "Distribution".repo NOT LIKE :repo_1 GROUP BY "Packages"."Name", "Packages"."Arch", "Distribution".repo, but got: Can't find any foreign key relationships between 'Join object on %(139953254400272 Packages)s(139953254400272) and %(139953256322768 Distribution)s(139953256322768)' and '%(139953257005520 anon)s'.

It's trying to join, but it says it doesn't know where to make the join. Is there something wrong with my syntax? I think it's correct based on what's in the join function.

like image 694
miscsubbin Avatar asked Aug 27 '13 19:08

miscsubbin


People also ask

What is subquery in SQLAlchemy?

The grouping is done with the group_by() query method, which takes the column to use for the grouping as an argument, same as the GROUP BY counterpart in SQL. The statement ends by calling subquery() , which tells SQLAlchemy that our intention for this query is to use it inside a bigger query instead of on its own.

What is join in SQLAlchemy?

Python Flask and SQLAlchemy ORM The join() method returns a join object from one table object to another. join(right, onclause = None, isouter = False, full = False) The functions of the parameters mentioned in the above code are as follows − right − the right side of the join; this is any Table object.

How does the querying work with SQLAlchemy?

Python Flask and SQLAlchemy ORM All SELECT statements generated by SQLAlchemy ORM are constructed by Query object. It provides a generative interface, hence successive calls return a new Query object, a copy of the former with additional criteria and options associated with it.


1 Answers

Apparently you need to add an and_() around multiple join conditions.

join(
    sq,
    and_(p1.Name==sq.c.Name,
    p1.Arch==sq.c.Arch,
    d1.repo==sq.c.repo,
    p1.Date==sq.c.Date)).\
like image 147
miscsubbin Avatar answered Sep 20 '22 04:09

miscsubbin