Support for conditional fragments

[lorefnon]

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:

SELECT count(*)
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}} ]]

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

WHERE column = [[ {{ your_parameter }} --]]your_default_value

I was wondering if this/similar solution could be adopted in pgtyped as well.

[wsporto]

I see are some problems with this solution. For example:

1. The SQL is not a valid SQL anymore. You can't copy and run it in a SQL editor.
2. How would it work if you don't want to filter by tag_name but still want to select the tag name? You can't omit the 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}} ]]

3. How would it work with a CTE optional step? The same CTE could be used in two different JOINS.

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 (?)