sqlite_orm 🚀 - More complex query example

currently what I'm doing is two steps.

auto taggings = db->get_all<TAGMAP>(where(c(&TAGMAP::tag_uuid) == tag_id));

std::vector<ITEM> items;

for (auto tagging : taggings) {
    auto item = db->get_all<ITEM>(where(c(& ITEM::id) == tagging.item_id)).front();
}

but it's a hit on the DB for each item : /

jakemumu on 25 Apr 2020

Assume you have struct Tagmap, struct Bookmark and struct Tag.

auto rows = storage.select(asterisk<Bookmark>(),
    where(is_equal(&Tagmap::tagId, &Tag::tagId) 
        and in(&Tag::name, {"bookmark", "webservice", "semweb"}) 
        and is_equal(&Bookmark::id, &Tagmap::bookmarkId)),
    group_by(&Bookmark::id),
    having(is_equal(count(&Bookmark::id), 3))
);

Tell me if you need your table alises - I shall add it here too.

fnc12 on 25 Apr 2020

here is the query with my table aliases using you ref:

        auto rows = db->getDatabase()->select(asterisk<MKUSampleMeta>(),
                                              where(is_equal(&MKUTagging::tag_uuid, &MKUTagMeta::uuid)
                                                    and in(&MKUTagMeta::name, { inTags[0].toStdString() })
                                                    and is_equal(&MKUSampleMeta::uuid, &MKUTagging::taggable_uuid)),
                                              group_by(&MKUSampleMeta::row_id),
                                              having(is_equal(count(&MKUSampleMeta::row_id), 1)));

so it's Tagmap -> MKUTagging, Bookmark -> MKUSampleMeta, Tag -> MKUTagMeta

I threw a print in to the lib where the query is generated:

SELECT * FROM 'SAMPLE_META'  WHERE ( 'TAGGINGS'."TAG_UUID" = 'TAG_META'."UUID" AND 'TAG_META'."NAME" IN (  ? ) AND 'SAMPLE_META'."UUID" = 'TAGGINGS'."TAGGABLE_UUID") GROUP BY 'SAMPLE_META'."ID" 
libc++abi.dylib: terminating with uncaught exception of type std::__1::system_error: no such column: TAGGINGS.TAG_UUID: SQL logic error

The error is strange because that column most definitely exists in the db..

jakemumu on 25 Apr 2020

@jakemumu please use dev branch cause master doesn't parse WHERE clause during filling FROM block. This is a new feature. It will be merged into master with next release

fnc12 on 25 Apr 2020

Nice, I just gave it a shot from dev and that query returned results.

The only thing is it's returning tuples instead of the full object, is there a way to have it return the ORM mapped object similar to the get_all query?

Anyways the features one dev are super useful, it's great to be able to prepare the statement and the be able to print / inspect it to make sure it's looking correct

jakemumu on 26 Apr 2020

It is very good that new functions are useful for you.
About returning objects instead of tuples. select statements returns tuples not objects. If you need objects use get_all. But selection from several tables with get_all isn't supported right now. I need to think about adding it. I have some ideas. Anyway right now you can make std::transform and create objects from tuples with std::move. It is not the best solution but it will work for now.

fnc12 on 26 Apr 2020

Hey @fnc12,

Do you have any example on how you'd do that std::transform & move you mentioned?

I think it would be great to add relational mapping to the select statements, as tuples are pretty generic and difficult to interact with, and it would only make the ORM better : )

I assume there is a place in the library where you are doing conversions from tuples to the structs, maybe you could point me?

jakemumu on 10 May 2020

Currently I'm doing it like this, but is it guaranteed the indexes on the tuple will always match the order they were defined in to the table?

    for (auto row : rows) {
        MKUSampleMeta sample;
        sample.row_id = get<0>(row);
        sample.uuid = get<1>(row);
        sample.pack_uuid = get<2>(row);
        sample.name = get<3>(row);
        sample.path = get<4>(row);

        mNoiseSamples.push_back(sample);
    }

jakemumu on 10 May 2020

std::transform is an algorithm from <algorithm> header which helps you in transforming data from one type to another. It is map function analog from js-like languages. If you need to transform a container of std::tuple to a container with structs you have to do it like this:

std::vector<std::tuple<...>> rows = storage.select(...);
std::vector<MKUSampleMeta> objects;
objects.reserve(rows.size());
std::transform(rows.begin(), rows.end(), std::back_inserter(objects), [](auto &tuple){
    MKUSampleMeta sampleMeta;
    sample.row_id = std::move(get<0>(row));
    sample.uuid = std::move(get<1>(row));
    sample.pack_uuid = std::move(get<2>(row));
    sample.name = std::move(get<3>(row));
    sample.path = std::move(get<4>(row));
    return sampleMeta;
});

fnc12 on 10 May 2020

@jakemumu what kind of API do you prefer in a perfect world? Just tell me and I shall try to implement it

fnc12 on 10 May 2020

I think the ideal would be that the select could have the option to take either the current column specification, such as, &MKUSampleMeta::ID, &MKUSampleMeta::Name which would return tuple, or select_all MKUSampleMeta (statement) which would function similar to the get_all operation.

But as I've said this is all a very new approach to me so I'm not positive what's sure what's possible.

Another suggestion would be allow for a binding on the parameters on the preparedStatements, so that one could prepare the statement, but then change the arguments later as the parameters for queries change. It's also a bit tricky to deduce exactly what statement you've created from the prepared statements as when you print, it should the arguments as variable "?" but no ability to print what the arguments are currently? is that correct?

In general the framework is awesome and very close to an ORM you'd see in the web world such as Active Record from the Ruby on Rails platform.

The basic are seemingly all covered for me aside these couple details

jakemumu on 10 May 2020

Is there any idea when you'll merge the dev features into master? I'd be a bit nervous to ship a product from the dev branch

You know what would be awesome, to create an actual API reference and add doxygen comments to the library. I think that would make it really helpful. Currently I have to look at the wiki & examples to discover what's possible. I would be happy to help with this. Maybe I could open a PR with a start?

jakemumu on 10 May 2020

this binding is also failing:

auto selectStatement = db->getDatabase()->prepare(select(asterisk<MKUSampleMeta>(),
                                                         where(like(&MKUSampleMeta::name, "%"+mCurrentSearchString.toStdString()+"%")
                                                               and is_equal(&MKUTagging::tag_uuid, &MKUTagMeta::uuid)
                                                               and in(&MKUTagMeta::name, { tags_string })
                                                               and is_equal(&MKUSampleMeta::uuid, &MKUTagging::taggable_uuid)),
                                                         group_by(&MKUSampleMeta::row_id),
                                                         having(is_equal(count(&MKUSampleMeta::row_id), tag_count))));


std::cout << selectStatement.sql() << std::endl;
std::cout << get<0>(selectStatement) << std::endl;
std::cout << get<1>(selectStatement) << std::endl;
std::cout << get<2>(selectStatement) << std::endl;

There are 3 variables passed into this prepare statement, however the resulting sql statement is failing to get<1>(statement) & get<2>(statement) after creation.

This is from dev

jakemumu on 10 May 2020

1) I guess expanding get_all with selecting from more than one table will give you exaclty what you want. To do it I need to refactor get_all implementation without changing API. I shall make it soon. It is a great idea thank you.
2) How do you want statements to be improved? You will help if you write desired behavior and current behavior.
3) I'd like to do update of master ASAP but there are still bug exists in prepared statements. And I also want to refactor serialization before. I almost finished it.
4) Looks like a bug. I shall try to fix it today or tomorrow. Thanks
5) Why lib reference is more comfortable to use instead of example? I always thought the opposite.

fnc12 on 10 May 2020

That would be awesome! thank you. I'd love to learn how this mapping works in the library
I believe I was incorrect actually, it seems you're making it so you can bind the parameters. It would just be nice to print the full statement with what the parameters are binded to for sanity checks. It seems the variables disappear or have no way for inspection, I couldn't figure it out.
Yeah I see that, take your time, more important to have stable master
Thank you
What IDE do you use? Here is an example from my wrapper I've been writing around your library specific to my application, basically to remove inline try & catches etc..

Here I build the function and use a doxygen comment which explains it's functionality with details:

Screen Shot 2020-05-11 at 6 02 20 PM

inside of Xcode on Mac, this then allows me to option click the function name to obtain the information from the function definition:

Screen Shot 2020-05-11 at 6 01 46 PM

There's a couple benefits to this, first, it allows people to option click functions to learn about what they're doing and how to use them inside of the code.

The other benefit of a documentation, would be to explore the things which aren't in the examples.

The other note on having an API doc, would be because the library is a single header (which is totally awesome btw and should not change), it's difficult to look through the header to determine which functions are meant to be used by myself as the user, and which functions are meant to be used internally by the library.

One idea would be to have two files instead of one, which I don't think would be a deal breaker. One could be an interface file which would show all the functions which are meant to be used as a user of the library, and the other would hold all of the internal functions.

The other would be to only use doxygen comments on functions which are meant to be used as part of this interface, this would at least make it clear like: "hey, you can use this function" or, "hey, this function is internal and not for your use"

I think that you could then get doxygen to spit out documentation on only the functions which are labelled accordingly and build an API reference.

I agree examples are much better than references and shouldn't be dismissed, as they're much more helpful than a reference, but for example from my example Args&& ...Args, is very hard to figure out what type it corresponds to, how to find what options are available, etc.

I'd need to look more at the API, but I imagine if there is a function like get_all

Sqlite_orm: More complex query example

All 33 comments

Related issues