Open lorefnon opened 8 months ago
I see are some problems with this solution. For example:
INNER JOIN tags
because it is used on the SELECT statement:
SELECT
products.name as productName,
category.id as categoryId,
category.description as categoryDescription,
tags.name as tagName
FROM products
[[ JOIN category on products.category_id = category.id and category.id = {{category_id}} ]]
[[ JOIN product_tags on product_tags.product_id = products.id
JOIN tags on tags.id = product_tags.tag_id and tags.name = {{tag_name}} ]]
I am exploring a different approach in TypeSQL (similar to this library but for mysql). You write the plain SQL without any filter and annotate it with -- @dynamicQuery
. Then TypeSQL will parse the SQL and build the optimized SQL based on the select and where parameters, omitting the unnecessary JOINs and CTEs.
Example:
-- @dynamicQuery
SELECT
products.name as productName,
category.id as categoryId,
category.description as categoryDescription,
tags.name as tagName
FROM products
JOIN category on products.category_id = category.id
JOIN product_tags on product_tags.product_id = products.id
JOIN tags on tags.id = product_tags.tag_id
If you call the generated function like so:
const result = await selectProducts(conn, {
select: {
productName: true,
categoryDescription: true
},
where: [
['productName', 'LIKE', productName],
['categoryId', 'IN', categories]
]
}
The resulting SQL will be:
SELECT
products.name as productName,
category.description as categoryDescription
FROM products
JOIN category on products.category_id = category.id
WHERE 1 = 1
AND products.name LIKE concat('%', ?, '%')
AND products.category_id in (?)
Hello, thanks for maintaining pgtyped.
Is your feature request related to a problem? Please describe. The currently prescribed approach for dynamic queries works well for conditional where clauses, but is not adaptable when we need conditional joins, or optional CTE steps etc.
The advise for splitting complex queries is generally sound, but suffers from lack of composability. So if we have multiple join based filters, we would need a query for each possible combination which gets messy.
Describe the solution you'd like In the BI solution Metabase, there is minimal support for optional clauses through
[[ ... ]]
.Example usage:
If a variable used within these square brackets is not defined the entire clause is omitted. This is conceptually simple but works well for a wide variety of scenarios.
Combined with a trailing comment trick, it also serves well as overridable default
I was wondering if this/similar solution could be adopted in pgtyped as well.