Support UNION operator

[tzvatot]

trafficstars

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