SQLAlchemy ORM Lateral Join using Subquery

Question

I Want to convert an SQL query to SQLalcheny. Here is my query.

**SELECT
  *
FROM (
  -- Get the first time each user viewed the homepage.
  SELECT
    pr.id, pr.name, pr.sku, ca.name as "Catergory Nmae", su.name as "Supplier Name", br.name as "Brand Name"
  FROM public."products" pr
LEFT JOIN public."categories" ca on pr.category_id = ca.id
LEFT JOIN public."suppliers" su on pr.supplier_id = su.id
LEFT JOIN public."brands" br on pr.brand_id = br.id
    GROUP BY pr.id, ca.name, su.name, br.name
) er LEFT JOIN LATERAL (
  -- For each row, get the first time the user_id did the enter_credit_card
  -- event, if one exists within two weeks of view_homepage_time.
  SELECT
    pp.scraped_price,
pp.process_date,
re.name
    FROM public."product_urls" pu
LEFT JOIN public."productPriceDetails" pp on pp.product_id = pu.product_id and pp.url_id = pu.id and pp.process_date = '2020-04-17'
LEFT JOIN public."retailers" re on pu.retailer_id = re.id
--GROUP BY  er.id, re.name
    LIMIT 5
) e2 ON true**

I create two subqueries namely 'init_query' and 'sub_qry_lateral' as follows

init_query = db.session.query(Product.id, Product.name.label('Product_Name'), Product.sku,
                  Category.name.label("Catergory_Name") , Supplier.name.label('Supplier_name'), Brand.name.label('Brand_Name') ) \
                .join( Category,  Product.category_id == Category.id, isouter=True) \
                .join(Supplier, Product.supplier_id == Supplier.id, isouter=True) \
                .join(Brand, Brand.id == Product.brand_id, isouter=True) \
                .group_by(Product.id, Category.name, Supplier.name, Brand.name) \
                .subquery('init_query')

    **sub_qry_lateral** = db.session.query(ProductUrl.product_id, 

ProductPriceDetails.scraped_price,ProductPriceDetails.process_date,Retailer.name ) \
            .join( ProductPriceDetails, and_(ProductPriceDetails.product_id == ProductUrl.product_id , ProductPriceDetails.url_id == ProductUrl.id , ProductPriceDetails.process_date =='2020-04-17'), isouter=True) \
            .join(Retailer,  ProductUrl.retailer_id == Retailer.id,  isouter=True) \
            .limit(5) \
            .subquery('sub_qry_lateral') \
            .lateral()
and combine it as follows

db_result = db.session.query(init_query,  sub_qry_lateral.c.scraped_price, sub_qry_lateral.c.process_date) \
                .join(sub_qry_lateral, init_query.c.id == sub_qry_lateral.c.product_id, isouter=True )

But it didn't give right answer. It output only the result-set from sub query 'init_query'

Any help appreciated

score 1 · Answer 1 · answered Apr 19 '20 at 11:44

1

AT last I could solve it. I rewrite it as

db_result = db.session.query(init_query, sub_qry_lateral.c.scraped_price, sub_qry_lateral.c.process_date).join(sub_qry_lateral, true())

Removed the condition and add true()

answered Apr 19 '20 at 11:44

Justin aruja

11
2

SQLAlchemy ORM Lateral Join using Subquery

1 Answers1