Support UNION operator

[tzvatot]

Missing support for SQL UNION operator.

[tnissen375]

May be it solves your use case.
Support CTE and UNION
https://github.com/tnissen375/squirrel

Drop me a line if it does not work as expected.

[tzvatot]

@tnissen375 thanks! how about creating it as a pull request to this fork?

[ousloob]

Your version support CTE? Very cool
I used to do a prepare statement for the SELECT inside the WITH and then a normal query for the SELECT after that, don't know if it's good idea :/

[tnissen375]

how about creating it as a pull request to this fork

May be i will. But i have to write some tests.

[tnissen375]

Your version support CTE? Very cool
I used to do a prepare statement for the SELECT inside the WITH and then a normal query for the SELECT after that, don't know if it's good idea :/

I dont know if my implementation solves all edge cases, but i was able to solve my usecase:
Finding the Depth of the Nodes

WITH RECURSIVE cte AS
(
SELECT category_id, CAST(name AS CHAR(200)) AS name,
CAST(category_id AS CHAR(200)) AS path,
0 as depth
FROM category WHERE parent IS NULL
UNION ALL
SELECT c.category_id,
CONCAT(REPEAT(' ', cte.depth+1), c.name), # indentation
CONCAT(cte.path, ",", c.category_id),
cte.depth+1
FROM category c JOIN cte ON
cte.category_id=c.parent
)
SELECT * FROM cte ORDER BY path;

If there is an sql statement which you cant build - post it. May be i can help out or extend if i missed something...,

[ousloob]

With squirrel I could not do this statement:

WITH inter_kpi AS (
    SELECT rec_date,
        hll_cardinality (hll_union_agg(ad_list_ids)) AS mtx
    FROM games.g_offers_agg AS base
        LEFT JOIN games.g_offers USING (game_id)
    WHERE rec_date >= $1
        AND rec_date < $2
        AND department_code = $3
        AND game_type = $4
    GROUP BY rec_date
)
SELECT rec_date,
    mtx::FLOAT / NULLIF(MAX(mtx) OVER (), 0) AS mtx_100
FROM inter_kpi
ORDER BY rec_date 

So I managed to do it like this with FromSelect :

SELECT rec_date,
    mtx::FLOAT / NULLIF(MAX(mtx) OVER (), 0) AS mtx_100
FROM (
        SELECT rec_date,
            hll_cardinality (hll_union_agg(ad_list_ids)) AS mtx
        FROM games.g_offers_agg AS base
            LEFT JOIN games.g_offers USING (offer_id)
        WHERE rec_date >= $1
            AND rec_date < $2
            AND department_code = $3
            AND game_type = $4
        GROUP BY rec_date
    ) AS inter_kpi
ORDER BY rec_date

I changed column/table name for confidentiality purpose
Some people say that the first statement is more efficient than the second because of the CTE, others says it's the same

[ousloob]

I finally managed to build CTE query with starting WITH

subQuery := sq.Select("rec_date").
		Column("hll_cardinality (hll_union_agg(ad_list_ids)) AS mtx").
		From("games.g_offers_agg base AS base").
		LeftJoin("games.g_offers USING (game_id)").
		Where(sq.GtOrEq{"rec_date": jf.StartDate}).
		Where(sq.Lt{"rec_date": jf.EndDate})

	// Optional query params
	if jf.DptCode != "" {
		subQuery = subQuery.Where(sq.Eq{"department_code": jf.DptCode})
	}

	if jf.GameType != "" {
		subQuery = subQuery.Where(sq.Eq{"game_type": jf.GameType})
	}

	subQuery = subQuery.PlaceholderFormat(sq.Dollar).
		GroupBy("rec_date")

	with := subQuery.Prefix("WITH inter_kpi AS (").Suffix(")")

	subSQL, params, err := with.ToSql()
	if err != nil {
		return nil, err
	}

	query := sq.Select("agg_date").
		Column("mtx::FLOAT / NULLIF(MAX(mtx) OVER (), 0) AS mtx_100").
		Prefix(subSQL).
		From("inter_kpi").
		OrderBy("rec_date")

	// No params in the second query.
	sql, _, err := query.ToSql()
	if err != nil {
		return nil, err
	}

[ovadbar]

Drop me a line if it does not work as expected.

tnissen375 I added some tests for your fork. I also had to make a change to get it working you can take a look a my fork off you https://github.com/ovadbar/squirrel. I also created the pull request that you have not created yet.

Thanks!

[mattiasgrenfeldt]

This feature was also talked about in #20 #140 #320

[LouisAldorio]

Here is my work arround for UNION

    productQuery := stmtBuilder.Select(
	"id",
	"category_id",
	"slug",
	"name",
	"display_name",
	"ecommerce_price",
	"is_selling_in_ecommerce",
	"weight",
	"created_at",
	"updated_at",
	"'PRODUCT' as type",
).From(model.ProductTableName).Where(squirrel.Eq{
	"deleted_at": nil,
})

bundlingQuery := stmtBuilder.Select(
	"id",
	"category_id",
	"slug",
	"name",
	"display_name",
	"ecommerce_price",
	"is_selling_in_ecommerce",
	"weight",
	"created_at",
	"updated_at",
	"'BUNDLING' as type",
).From(model.ProductBundlingTableName)

stmt := stmtBuilder.Select(
	"ecommerce_commodities.*",
).FromSelect(
	productQuery.SuffixExpr(bundlingQuery.Prefix("UNION ALL")),
	"ecommerce_commodities",
)

[kcross-ctoken]

THe problem with the above is that If you have a parameterised query in both sides of the union the numbering is broken So I cant use this