[odb-users] Using CTEs in ODB queries

[Whisperity]

Dear Mailing List,

We are using ODB as an ORM in our software. A new feature has required us
to use complex queries which are too time-consuming and unneccessary to
implement as persistent objects. For this end, we have developed native
queries, but we have ran into a problem when we wished to execute it as a
native query.

Consider the following code example:

WITH "Query1" AS (
>   SELECT * FROM "SomeTable" WHERE "SomeField" = 'SomeValue'
> ), "Query2" AS (
>   SELECT "id" FROM "SomeIDTable" WHERE "id_field" IS IN (SELECT ...)
> ), ...
>
> SELECT "id", "QueryX"."other_id" FROM "QueryX" WHERE "id" > 500 AND ...
>

This query is to return pairs of values, obviously. I am trying to map it
to the following struct and use the query as follows:

> #pragma db view
> struct MyView
> {
>   ::types::someIDType id;
>   ::types::someIDType other_id;
> };
>

However, when executing the query, the query string is prepended with
WHERE, making the query a huge syntax error. I'm using PostgreSQL as our
database backend.

I have seen a similar issue on the list (back from July 2014) about using
EXEC and a fix for it, but using stored procedures are not available to us
because sometimes we have to use sqlite. But using a similar approach, the
issue was "fixed", so I am requesting a review for this little patch, maybe
it will help other users in the next release:
s.compare (0, (n = 4), "WITH") == 0 || s.compare (0, (n = 4), "with") == 0

----

Along with this issue, I have another one. It is not trivial at first
glance, but where we are using this query is actually a link table defining
one-to-many relationships, or at least considering a view of this
calculated relationship. If I try to use std::vector<::types::someIDtype>
other_id as my second field (or any other collection), the ODB compiler
throws an error. Is it possible to make ODB believe that it should give me
a "list of IDs grouped by another column"? Or should I do this grouping in
my program on a sorted result?

Whisperity