-
-
Notifications
You must be signed in to change notification settings - Fork 89
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Support for conditional fragments #557
Comments
I see are some problems with this solution. For example:
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 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.
The text was updated successfully, but these errors were encountered: