Support for returning objects inside columns
Closed this issue · 8 comments
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!
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>().
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!
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.
Your first link is broken, but it makes sense. Thank you!
Your first link is broken, but it makes sense. Thank you!
Sorry about that. Fixed.
I just tested this, works exactly as I expected. Thanks for all of your hard work!
@trueqbit, didn't ask this before. Is it possible to use this with the
iteratemethod as well so I'm not loading a large result set into a vector?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!