CODEWITHSUNDEEP
pythonmysqlsqlsqlalchemy
xyz-123
xyz-123

Reputation: 2237

SQLAlchemy: select over multiple tables

I wanted to optimize my database query:

link_list = select(
    columns=[link_table.c.rating, link_table.c.url, link_table.c.donations_in],
    whereclause=and_(
        not_(link_table.c.id.in_(
            select(
                columns=[request_table.c.recipient],
                whereclause=request_table.c.donator==donator.id
            ).as_scalar()
        )),
        link_table.c.id!=donator.id,
    ),
    limit=20,
).execute().fetchall()

and tried to merge those two selects in one query:

link_list = select(
    columns=[link_table.c.rating, link_table.c.url, link_table.c.donations_in],
    whereclause=and_(
        link_table.c.active==True,
        link_table.c.id!=donator.id,
        request_table.c.donator==donator.id,
        link_table.c.id!=request_table.c.recipient,
    ),
    limit=20,
    order_by=[link_table.c.rating.desc()]
).execute().fetchall()

the database-schema looks like:

link_table = Table('links', metadata,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('url', Unicode(250), index=True, unique=True),
    Column('registration_date', DateTime),
    Column('donations_in', Integer),
    Column('active', Boolean),
)
request_table = Table('requests', metadata,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('recipient', Integer, ForeignKey('links.id')),
    Column('donator', Integer, ForeignKey('links.id')),
    Column('date', DateTime),
)

There are several links (donator) in request_table pointing to one link in the link_table. I want to have links from link_table, which are not yet "requested".

But this does not work. Is it actually possible, what I'm trying to do? If so, how would you do that?

Thank you very much in advance!

Upvotes: 4

Views: 3497

Answers (2)

user742071
user742071

Reputation:

You may be look for the SQL NOT EXISTS construct:

http://www.sqlalchemy.org/docs/orm/tutorial.html#using-exists

Upvotes: 1

SingleNegationElimination
SingleNegationElimination

Reputation: 156308

Riffing on masida's answer:

First, the original query:

>>> print select(
...     columns=[link_table.c.url, link_table.c.donations_in],
...     whereclause=and_(
...         not_(link_table.c.id.in_(
...             select(
...                 columns=[request_table.c.recipient],
...                 whereclause=request_table.c.donator==5
...             ).as_scalar()
...         )),
...         link_table.c.id!=5,
...     ),
...     limit=20,
... )
SELECT links.url, links.donations_in 
FROM links 
WHERE links.id NOT IN (SELECT requests.recipient 
FROM requests 
WHERE requests.donator = :donator_1) AND links.id != :id_1 
 LIMIT 20

And rewritten in terms of exists():

>>> print select(
...     columns=[link_table.c.url, link_table.c.donations_in],
...     whereclause=and_(
...     not_(exists().where(request_table.c.donator==5)),
...     #    ^^^^^^^^^^^^^^
...         link_table.c.id!=5,
...     ),
...     limit=20,
... )
SELECT links.url, links.donations_in 
FROM links 
WHERE NOT (EXISTS (SELECT * 
FROM requests 
WHERE requests.donator = :donator_1)) AND links.id != :id_1 
 LIMIT 20

Upvotes: 0

Related Questions