Support for returning objects inside columns

[trevornagy]

This is an issue regarding 2 different items that both fall under the same category: returning named objects rather than tuples to provide an easier API to work with.

Item 1
I would like to perform a select * query with a left join (and return all data from both tables) and instead of returning tuples, I would return the objects of both tables (as it’s a select *).

After digging around in your examples and code I see you have sqlite_orm::object, which is defined underneath sqlite_orm::asterisk. To my confusion, the following code compiles (but returns tuples, which I don’t want in this scenario):

    return dbStorage->select(
        sqlite_orm::columns(
            sqlite_orm::asterisk<X>(), sqlite_orm::asterisk <Y>()
        ),
        sqlite_orm::left_join<Y>(sqlite_orm::on(
            sqlite_orm::c(&Y:: Key) == &X::Key
        )), orderBySql, sqlite_orm::limit(limitCount.value_or(-1))
    );

But the same code using sqlite_orm::object, does not:

    return dbStorage->select(
        sqlite_orm::columns(
            sqlite_orm::object<X>(), sqlite_orm::object<Y>()
        ),
        sqlite_orm::left_join<Y>(sqlite_orm::on(
            sqlite_orm::c(&Y:: Key) == &X::Key
        )), orderBySql, sqlite_orm::limit(limitCount.value_or(-1))
    );

The reasoning why I want to return the objects instead of tuples is that X and Y each have 5-10 properties which would result in a large return tuple in the first example, all with unnamed return types. It’s easier for the developer to use the API that example 2 provides.

The error is as follows: sqlite_orm.h(8754,61): error C2280: 'V sqlite_orm::row_extractor<V,void>::extract(sqlite3_stmt *,int) const': attempting to reference a deleted function

To mitigate this issue, my current strategy is to make a new struct Z that contains the all of the properties of X and Y and use std::make_from_tuple, like so:

    template <typename T, typename... Args>
    std::vector<T> ConvertTuplesToStructs(std::vector<std::tuple<Args...>>&& tuples)
    {
        std::vector<T> results;
        results.reserve(tuples.size());

        for (auto& tuple : tuples)
        {
            results.push_back(std::make_from_tuple<T>(std::move(tuple)));
        }

        return results;
    }

This leads into item 2.

Item 2
It would be nice if there was an API that allowed us to return new named structs that contain all of the return type members from a query. For example, I run this query:

        dbStorage->select(
            sqlite_orm::columns(
                &X::col1, 
                &X::col2,
                &X::col3, 
                &Y::col1
            ),
            sqlite_orm::join<Y>(
                sqlite_orm::on(sqlite_orm::c(&Y::Key) == &X::Key)
            )
        );

This will return a vector of tuples of 4 unnamed values, however, if in the future I update it to retrieve 5 values and the new value is in before &X::col1, all of the callers retrieving the 0th to 4th value will have to change their code. Once again, I can mitigate this using my approach above with the method ConvertTuplesToStructs and some other struct Z containing duplicates of the members X::col1, X::col2, X::col3, Y::col1. It would be nice however, if something like this was built into the API, where the developer could specify the return type struct.

Thanks!

[trueqbit]

Hi @trevornagy,

I have taken your questions as a challenge :)

See PR #1278 at your disposal.

Thoughts

Item 1 (selecting multiple mapped objects) is something that should definitely work.

Item 2 (repacking columns in an ad hoc structure):

Although your two queries are related, they are not quite the same thing. This is because object<X>() means that sqlite_orm is able to return an object that is “mapped” as a table, as opposed to an arbitrary unmapped object type that is unknown to the sqlite_orm type system.

However, extending the code to select multiple objects has led me to believe that it doesn't matter whether sqlite_orm returns a tuple or some other structure, as long as that structure can be constructed from the result set.

Examples

Regardless of which approach you use, I recommend ensuring at least some form of usage and type safety: Define the partial select expression along with the tuple/structure type:

Example 1 (using columns() like traditionally):

using z_tuple = std::tuple<decltype(X::col1), decltype(X::col2), decltype(X::col3), decltype(Y::col1)>;
constexpr auto as_z_tuple = sqlite_orm::columns(&X::col1, &X::col2, &X::col3, &Y::col1);

std::vector<z_tuple> rows = 
    dbStorage->select(
        as_z_tuple,
        sqlite_orm::join<Y>(
            sqlite_orm::on(sqlite_orm::c(&Y::Key) == &X::Key)
        )
    );

Example 2 (using new struct_<Z>()):

struct Z {
    decltype(X::col1) xcol1;
    decltype(X::col2) xcol2;
    decltype(X::col3) xcol3;
    decltype(Y::col1) ycol1;
};
constexpr auto as_z_struct = sqlite_orm::struct_<Z>(&X::col1, &X::col2, &X::col3, &Y::col1);

std::vector<Z> rows = 
    dbStorage->select(
        as_z_struct,
        sqlite_orm::join<Y>(
            sqlite_orm::on(sqlite_orm::c(&Y::Key) == &X::Key)
        )
    );

See also the "named_adhoc_structs" example.

Also, as with object<X>(), the result does not have to be a single returned structure, but can be embedded in the returned multi-column row tuple:

constexpr auto as_z_struct = sqlite_orm::struct_<Z>(&X::col1, &X::col2, &X::col3, &Y::col1);

// row tuple type is: `std::tuple<decltype(X::col1), Y, Z>`
auto rows =
    dbStorage->select(
        sqlite_orm::columns(&X::col1, sqlite_orm::object<Y>(), as_z_struct),
        ...
    );

Naming

I would say that the naming is still open for discussion.

I chose struct_<Z>() for the expression factory function, but there are other apt words I can think of:
construct<Z>(), as_struct<Z>(), repack_as<Z>().

[trevornagy]

Looked at the example, this is exactly what I was hoping for.

Also, as with object(), the result does not have to be a single returned structure, but can be embedded in the returned multi-column row tuple:

constexpr auto as_z_struct = sqlite_orm::struct_(&X::col1, &X::col2, &X::col3, &Y::col1);

// row tuple type is: std::tuple<decltype(X::col1), Y, Z>
auto rows =
dbStorage->select(
sqlite_orm::columns(&X::col1, sqlite_orm::object(), as_z_struct),
...
);

This is really slick, I like this a lot.

Naming
I would say that the naming is still open for discussion.

I chose struct_() for the expression factory function, but there are other apt words I can think of:
construct(), as_struct(), repack_as().

I am happy with the naming, I think struct_<Z>() is clear in the context.

Question: would it be possible for Z to have only public getters defined and the members are all private? Not a requirement, just trying to understand. For example:

struct Z {
  decltype(X::col1) GetXCol1() { return xcol1; } 
  decltype(X::col1) GetXCol2() { return xcol2; } 
  decltype(X::col1) GetXCol3() { return xcol3; } 
  decltype(X::col1) GetXCol4() { return xcol4; } 
private:
    decltype(X::col1) xcol1;
    decltype(X::col2) xcol2;
    decltype(X::col3) xcol3;
    decltype(Y::col1) ycol1;
};

Would we have to define a constructor?

Thanks for your quick work, this is awesome!

[trueqbit]

Just as with a tuple, a named structure must be constructible by "direct list initialization", see also point of construction, and the comment on struct_<>().

If you have something other than an "aggregate" in the C++ sense, you need to provide a constructor.

[trevornagy]

Your first link is broken, but it makes sense. Thank you!

[trueqbit]

Your first link is broken, but it makes sense. Thank you!

Sorry about that. Fixed.

[trevornagy]

I just tested this, works exactly as I expected. Thanks for all of your hard work!

[trevornagy]

@trueqbit, didn't ask this before. Is it possible to use this with the iterate method as well so I'm not loading a large result set into a vector?

#1311

Would this be how we do it?

EDIT: Just tested that out, it's exactly how I would do it. Nevermind!

[trueqbit]

@trueqbit, didn't ask this before. Is it possible to use this with the iterate method as well so I'm not loading a large result set into a vector?

#1311

Would this be how we do it?

EDIT: Just tested that out, it's exactly how I would do it. Nevermind!

Just to actively give you an answer: Yes, you can use all query constructs, including those with CTEs!