[odb-users] Using CTEs in ODB queries

[Whisperity]

Dear Boris,

Thank you for your reply. I assume the same patch has been added to
libodb-sqlite as well?

-----

What I was trying to achieve is this. I have a RESULT table (so there is no
persistent type for it) of two columns which are two IDs calculated by the
query. But actually this table defines a relationship of an object having
the ID from the first column to many objects having IDs from the second
column.

So instead of doing "SELECT id1, id2 FROM cte;" and loading them as just
pairs of IDs, I thought I could let ODB do the grouping job for me and my
code would only get struct which contains an int and a vector(/set) of ints.

So

#prama db view
> struct MyStruct
> {
>   int id1;
>   int id2;
> };
>
> ...
>
> std::multimap<int, int> grouped;
> result<MyStruct> res = db->query(...);
> for (auto it = res.begin(); it != res.end(); ++it)
> {
>   grouped.insert(std::make_pair<int, int>(it->id1, it->id2));
> }
>

I could just do

#pragma db view
> struct MyStruct
> {
>   int id1;
>   std::vector<int> id2s;
> };
>
> ...
>
> result<MyStruct> res = db->query(...);
> // And now all the "second" IDs for the same "first" ID is grouped at this
> point.
>

But seeing your last sentence and the fact that doing the grouping myself
is maybe marginally worse (if it is worse at all) than ODB doing it, we
just scrapped this idea.

2016-08-03 17:45 GMT+02:00 Boris Kolpackov <boris at codesynthesis.com>:

> Hi,
>
> Whisperity <whisperity at gmail.com> writes:
>
> > [...] 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
>
> Thanks, committed for the next release:
>
>
> http://scm.codesynthesis.com/?p=odb/libodb-pgsql.git;a=commit;h=fa085bca16a685b768e091c7c352e97677b4d8ff
>
>
> > 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?
>
> I am not sure I understand what you are trying to achieve. A small
> example might help.
>
> Generally, you cannot load containers in views thought you could load
> a container as a view (i.e., view representing each element).
>
> Boris
>
>