SQLAlchemy: Selecting Multiple Tables

Question

I would like 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 combine these two choices 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 is as follows:

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

Request_table has several links (donor) pointing to one link in link_table. I want to have links from link_table that are not yet requested.

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

Thank you in advance!

Answer 1

You can search for the SQL NOT EXISTS construct:

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

Answer 2

Riffing on answer masida :

Initial request first:

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