Iterate two or more objects over a joined table

[xiaopi-ouo]

Hi, thank for your work on this!

I wonder if I can assoicated an object by an id to another corresponding object during iteration.
i.e. Getting the object A, B during the iteration by inner join.

// SELECT A.*, B.*
// FROM A
// INNER JOIN B
// ON A.b_uid = B.uid
for (auto& [a, b] : storage.iterate<A, B>(inner_join<B>(on(c(&A::b_uid) == &B::uid)))) {
  // do something to a, b
  ...
}

According to the inner join example, now I can only use select with a batch size to avoid memory issue when the tables are too large to be loaded once.

get_all return objects but only for the left one. i.e. get_all<A>(statement), so if there is a usage like get_all<A, B>(statement), it'll also solve the problem by setting a reading batch size

Is there any hidden method I ignored to do that?

[trueqbit]

Hi @xiaopi-ouo It's indeed possible to iterate over joined objects. The feature is relatively new since v1.9. Furthermore, at the time of your question and this answer, it is currently awaiting a fix in PR #1448.

There's now an iterate(select) method that accepts a select statement. Specifically:

for (auto [a, b] : storage.iterate(select(columns(object<A>(true), object<B>(true)), inner_join<B>(on(c(&A::b_uid) == &B::uid))))) {
  // do something to a, b
  // ...
}

Note: get_all<Object>(conditions) and iterate<Object>(conditions) are still only available for a single object type that is mapped as a table, which makes sense.

[trueqbit]

Hi @xiaopi-ouo, you can now use the latest development branch.

[xiaopi-ouo]

Hi @trueqbit , I used the header include/sqlite_orm/sqlite_orm.h in the branch dev and I tried to compile a tiny example but got an error.

#include "sqlite_orm.h"
#include <string>

struct A {
    int uid;
    int b_uid;
};

struct B {
    int uid;
};

int main() {
    using namespace sqlite_orm;
    auto storage = make_storage(
        "test.db",
        make_table("A",
                    make_column("uid", &A::uid),
                    make_column("b_uid", &A::b_uid)),
        make_table("B",
                    make_column("uid", &B::uid))
    );
    for (auto [a, b] : storage.iterate(
        select(
            columns(
                object<A>(true),
                object<B>(true)),
            inner_join<B>(on(c(&A::b_uid) == &B::uid))))) {
    // do something to a, b
    // ...
    }
    return 0;
}

error

test.cpp:28:56: error: no matching function for call to ‘sqlite_orm::internal::storage_t<sqlite_orm::internal::table_t<A, false, sqlite_orm::internal::column_t<int A::*, sqlite_orm::internal::empty_setter>, sqlite_orm::internal::column_t<int A::*, sqlite_orm::internal::empty_setter> >, sqlite_orm::internal::table_t<B, false, sqlite_orm::internal::column_t<int B::*, sqlite_orm::internal::empty_setter> > >::iterate(sqlite_orm::internal::select_t<sqlite_orm::internal::columns_t<sqlite_orm::internal::object_t<A>, sqlite_orm::internal::object_t<B> >, sqlite_orm::internal::inner_join_t<B, sqlite_orm::internal::on_t<sqlite_orm::internal::is_equal_t<int A::*, int B::*> > > >)’
   28 |             inner_join<B>(on(c(&A::b_uid) == &B::uid))))) {
      |                                                        ^
In file included from test.cpp:1:
sqlite_orm.h:23383:48: note: candidate: ‘template<class T, class O, class ... Args> sqlite_orm::internal::mapped_view<O, sqlite_orm::internal::storage_t<DBO>, Args ...> sqlite_orm::internal::storage_t<DBO>::iterate(Args&& ...) [with T = T; O = O; Args = {Args ...}; DBO = {sqlite_orm::internal::table_t<A, false, sqlite_orm::internal::column_t<int A::*, sqlite_orm::internal::empty_setter>, sqlite_orm::internal::column_t<int A::*, sqlite_orm::internal::empty_setter> >, sqlite_orm::internal::table_t<B, false, sqlite_orm::internal::column_t<int B::*, sqlite_orm::internal::empty_setter> >}]’
23383 |             mapped_view<O, self_type, Args...> iterate(Args&&... args) {
      |                                                ^~~~~~~
sqlite_orm.h:23383:48: note:   template argument deduction/substitution failed:
test.cpp:28:56: note:   couldn’t deduce template parameter ‘T’
   28 |             inner_join<B>(on(c(&A::b_uid) == &B::uid))))) {
      |                                                        ^

Did I miss anything?

[trueqbit]

This is a feature that requires C++20.

[trueqbit]

@xiaopi-ouo Feel free to reopen this issue if you have further questions.

[xiaopi-ouo]

Sorry for my late reply.
I simply compiled the example I showed in the last reply by

g++ test_sqlite.cpp -std=c++20 -lsqlite3

where g++=10.2.0 but faced the same error.

The header sqlite_orm.h is from https://github.com/fnc12/sqlite_orm/tree/dev/include/sqlite_orm

[trueqbit]

Well, gcc 10 reports 201709 for __cplusplus even in its C++20 mode, which is the reason the iterate() method for result sets isn't available (only for mapped objects).

When I implemented the feature (with PR #1311), I probably thought it required C++20, or I just decided that it did.
It's easy enough to relax the requirements and make it available for C++17. See PR #1453 .

[trueqbit]

@xiaopi-ouo Assuming that this is now working for you, I am closing this ticket (again).