Select from joined tables with aliases

Hi.

I have the query:

s = subscriptions.alias('subscriptions')
p = products.alias('products')
query = s.join(p).select() \
    .where((p.c.type == product_type) & (s.c.updated_at >= since)) \
    .order_by(s.c.updated_at.desc())

Which transforms to next SQL:

SELECT subscriptions.id, subscriptions.name, subscriptions.created_at, subscriptions.updated_at, subscriptions.product_id, subscriptions.plan_id, subscriptions.owner, subscriptions.expires_at, subscriptions.is_active, subscriptions.status, products.id, products.name, products.created_at, products.updated_at, products.type 
FROM subscriptions AS subscriptions JOIN products AS products ON products.id = subscriptions.product_id 
WHERE products.type = %(type_1)s AND subscriptions.updated_at >= %(updated_at_1)s ORDER BY subscriptions.updated_at DESC

As a result I get rows which represents as dict with keys without table prefixes.
The keys are superimposed and I get product id in id key instead of subscription id.

Hi,
You can use SqlAlchemy labels to solve this issue. But you need to give all columns that you need within the select function. In the below example I just used two columns.

s = subscriptions.alias(‘subscriptions’)
p = products.alias(‘products’)
query = s.join§.select([s.c.id.label(‘subscriptionsId’), p.c.id.label(‘productsId’)])
.where((p.c.type == product_type) & (s.c.updated_at >= since))
.order_by(s.c.updated_at.desc())

Thanks!

I am using something like:

def get_all_columns(*args):
    columns = []
    for arg in args:
        columns.extend([x.label(name=str(x)) for x in arg.c])
    return columns
    
s = subscriptions.alias('subscriptions')
p = products.alias('products')

query = select(SubscriptionModel.get_all_columns(s, p)).select_from(s.join(p)) \
    .where((p.c.type == 'solution') & (s.c.updated_at >= datetime.utcnow() - timedelta(days=60))) \
    .order_by(s.c.updated_at.desc())

And as result I get:

SELECT subscriptions.id AS "subscriptions.id", subscriptions.name AS "subscriptions.name", subscriptions.created_at AS "subscriptions.created_at", ...
FROM subscriptions AS subscriptions JOIN products AS products ON products.id = subscriptions.product_id 
WHERE products.type = %(type_1)s AND subscriptions.updated_at >= %(updated_at_1)s ORDER BY subscriptions.updated_at DESC
1 Like