ORDER BY and GROUP BY of UNIONs

[hiFloh]

I already described by situation in issue #81.
but the Problem is now different.

How do i apply GROUP BY and ``ORDER BY``` to the union?

// curent working code missing ORDER BY
SQLPP_CREATE_NAME_TAG(timestamp);

(int id){

auto position=select(pos.ts.as(timestamp),pos.lon.as(sqlpp::alias::a),pos.lat.as(sqlpp::alias::b),sqlpp::value(std::optional<int>{}).as(sqlpp::alias::a)).where(pos.id==id);
auto sensor1= select(spec.ts.as(timestamp),sqlpp::value(std::optional<float>{}).as(sqlpp::alias::a),sqlpp::value(std::optional<float>{}).as(sqlpp::alias::b),spec.content.as(sqlpp::alias::c).where((spec.id==id)&&spec.type=="sensor1"))
db(position.union_distinct(sensor1));
}

I would expect the following code to produce the wanted sql statement. But i get the error has no member order_by

position.union_distinct(sensor1).order_by(timestamp);

Florian

[MeanSquaredError]

I am not sure if ANSI SQL allows GROUP BY and ORDER BY on unions.
Also at the moment I don't have a copy of the SQL standard handy, so I cannot check what the standard says about it.

The various SQL dialects do allow GROUP BY and/or ORDER BY on unions, but the sqlpp23 connectors don't seem to support GROUP BY and ORDER BY on unions.

One workaround that you could do is to treat the union as a table and SELECT from it, then call GROUP BY and/or ORDER BY on the SELECT expression. See the sample code below.

#include <sqlpp23/sqlpp23.h>
#include <sqlpp23/postgresql/postgresql.h>

#include <iostream>

int main ()
{
	auto config = sqlpp::postgresql::connection_config {};
	config.user = "postgres";
	config.dbname = "template1";
	auto dbc = sqlpp::postgresql::connection {config};

	auto left = select (sqlpp::value (5).as (sqlpp::alias::a)); // We don't have to use an alias here when selecting from a real table
	auto right = select (sqlpp::value (6).as (sqlpp::alias::a)); // We don't have to use an alias here when selecting from a real table
	auto united = left.union_all (right).as (sqlpp::alias::b); // This alias is required but not really used
	for (const auto& row : dbc (
		select (united.a).from (united).order_by (united.a.desc ())
	)) {
		std::cout << row.a << "\n";
	}
}

The above program outputs:

6
5

Maybe rbock will have other suggestions since he obviously has much better knowledge of sqlpp23 and its internals.

[rbock]

Thanks for the question! Correct, sqlpp23 does not support group_by/order_by for union.

I am having a pretty busy week. But after a brief search on the web it seems like

1. an order_by in the last sub-query of the union would be applied to the whole expression (that's allowed by the library)
2. an order_by in the first query would be an error (not caught by the library, so this needs to be added as a new constraint)
3. you can explicitly order the whole thing or the sub queries by using sub selects as demonstrated by @MeanSquaredError

For group by applied to the union, I believe you have to resort to the sub-select technique.

[hiFloh]

One workaround that you could do is to treat the union as a table and SELECT from it, then call GROUP BY and/or ORDER BY on the SELECT expression. See the sample code below.

Thanks, works for me.

I wont close the issue, because i think the workaround does not solve the issue completely. Although I am satisfied with the workaround.

[rbock]

Thanks for the confirmation.

Just curious: What is missing from your perspective to solve this completely?

[hiFloh]

The Title of the issue suggests the usage of ORDER BY statements after unions. But the workaround does not result in that sql query. Although the results are comparable. And i'm not sure if there are any differences in performance that someone else might notice.

In addition i wanted to keep the possibility open to make a feature out of it.
Feel free to close the issue.

[rbock]

Thanks for the explanation!

As you can see from b911cbd and its revert I was about to say "but you can do this today", but it turns out that would be invalid SQL (due to the table-qualification of columns).

As a first step, I prevented such invalid SQL.

Providing an order_by for union expressions is probably not that hard, so I will leave this open for now.