使用子查询的SQLAlchemy ORM横向连接

Question

我想将SQL查询转换为SQLalcheny。这是我的疑问。

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

我创建了两个子查询，即"init_query"one_answers"sub_qry_lateral"，如下所示

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 ) 

但它并没有给出正确的答案。它只输出子查询"init_query"的结果集

感谢的任何帮助

Answer 1

我终于可以解决它了。我把它重写为

db_result=db.session.query(init_query，sub_qry_lateral.cscraped_price，sub_qry_lateral。c.process_date(.join(sub_qry_lateral，true(((

删除条件并添加true((