pypika
pypika copied to clipboard
Handle AliasedQuery in backend during with statement
In order to create a CTE query like this one
-- Query from https://learnsql.com/blog/what-is-common-table-expression/
WITH highest AS (
SELECT
branch,
date,
MAX(unit_price) AS highest_price
FROM sales
GROUP BY branch, date
)
SELECT
sales.*,
h.highest_price
FROM sales
JOIN highest h
ON sales.branch = h.branch
AND sales.date = h.date
an AliasedQuery
needs to be created with the name needed to be passed twice (into AliasedQuery
and in with_
)
sales = Table("sales")
highest_query = (
Query
.from_(sales)
.select(sales.branch, sales.date, fn.Max(sales.unit_price).as_("highest_price"))
.groupby(sales.branch, sales.date)
)
# Create an AliasedQuery
highest_alias = "highest"
highest = AliasedQuery(highest_alias)
join_condition = (
(sales.branch == highest.branch)
& (sales.date == highest.date)
)
query = (
Query
.with_(highest_query, highest_alias)
.from_(sales)
.join(highest)
.on(join_condition)
.select(sales.star, highest.highest_sales)
)
However, I think this syntax would be a lot easier to work with.
sales = Table("sales")
highest = (
Query
.from_(sales)
.select("branch", "date", fn.Max("unit_price").as_("highest_price"))
.groupby("branch", "date")
).as_("highest")
query = (
Query
.with_(highest)
.from_(sales)
.join(highest)
.on(join_condition)
.select(sales.star, highest.highest_price)
)
Where a QueryBuilder
could be aliased which would signify it can be used liked a AliasedQuery