Relations in ORM Mapper?

chiefsucker • 3 months ago

Is it possible to model relations via the ORM Mapper API, or is it necessary to use SQL queries in this case?

As an example you could have a “categories” table and a “products” table that are related via a foreign key. Is it possible to get all products for a specific category like “Database Software” or is it necessary to create a SQL query with a JOIN instead?

antao2002 • 3 months ago

Hi, When you create models, there is a option named relationships which is used to add some APIs for relations in model classes. Please refer to the 241. The sub-options of it are listed in the model.json.

chiefsucker • 3 months ago

Thanks for pointing me to the GitHub page. There is a lot of informative reading material and the poster had similar questions to mine.

I already have all my relationships modeled that way in the configuration file. Taking a look at the generated model classes I can also see some generated relationship methods. I’m still unsure how to use the ORM API when I want (as an example) to get a specific category, and all products in that category that start with the letter “A”.

The only way I could find was this one:

drogon::orm::Mapper<drogon_model::demo::Category> mp(db);
auto category = mp.findOne(…); // Use Criteria to find a specific category.
category.getProducts(db, [this, category](auto products) {
    // Do your logic here, maybe add to HttpViewData.
},
[](const DrogonDbException &e) {
    // Error handling.
});

Is this the only way to use the ORM API for relations or did I miss something?

antao2002 • 3 months ago

Yes, or change the findOne method to the asynchronous mode. The disadvantage of this approach is that you have to query twice for this.

chiefsucker • 3 months ago

OK, thanks. I think in my case I’ll just use plain old SQL queries then and enhance them with some of the generated constants to get more safety in case of future refactorings 🙂👍🏻

antao2002 • 3 months ago

👍🏻

chiefsucker • 3 months ago

Is some kind of query builder (similar to what the ORM API currently offers, or sqlpp11) planned as part of the framework?

antao2002 • 3 months ago

I don't quite understand your needs, can you write more details? And would you like to be a collaborator of drogon if I sent an invitation?

chiefsucker • 3 months ago , edited 3 months ago

My primary need is to have type safety around SQL queries. This is already possible with Drogon, although the syntax can easily get complicated and hard to grasp without debugging / analyzing the program during runtime. As an example, today you have to write something along the lines of:

std::string sql = "select "
                        + Article::tableName + "." + Article::Cols::_title + ", "
                        + Article::Cols::_subtitle + ", "
                        + Article::Cols::_created_at + ", "
                        + Article::Cols::_content + ", "
                        + Article::Cols::_slug
                    + " from " + Article::tableName
                    + " join " + Blog::tableName + " on " + Article::tableName + "." + Article::Cols::_blog_id + "=" + Blog::tableName + "." + Blog::Cols::_id
                    + " where " + Blog::tableName + "." + Blog::Cols::_title + " = 'C++ Blog'";

A lot of parts are already type safe, thanks to tableName and Cols, but there are some issues left with this approach:

  1. You have to formulate parts of SQL as std::string, if there’s an error it will be caught during runtime.
  2. You have to meticulously pay attention to spaces inside these strings, otherwise you’ll get SQL syntax errors like selectarticle.title ….
  3. You have to track those + ", " at the end of the lines, and + "." + between table name & column, or alternatively use stringstream which doesn’t make things better, maybe just a little bit faster, and it introduces another indirection.
  4. Single quotes inside double quotes are better than escaping double quotes inside double quotes, but they’re easy to overlook (see the end of the last line).

There are already several ideas how one can solve these issues. Taking ideas from current solutions, the above query would look like that (in a naïve first approach for a fluent interface):

Selector s(dbClientPtr);
auto results = s.select(
                        Article::tableName + "." + Article::Cols::_title,
                        Article::Cols::_subtitle,
                        Article::Cols::_created_at,
                        Article::Cols::_content,
                        Article::Cols::_slug)
                    .from(Article::tableName)
                    .join(Blog::tableName).on(Article::tableName + "." + Article::Cols::_blog_id == Blog::tableName + "." + Blog::Cols::_id)
                    .where(Blog::tableName + "." + Blog::Cols::_title == "C++ Blog");

This already solves most of the problems mentioned above. One of the remaining issues is the manual string building between table name & column in case of ambiguity where a nice as syntax could help. Furthermore Drogon has already Criteria which should probably be reused in the case of on() & where() for the sake of similarity. Maybe the syntax can be improved further by designating a “main” table which could get rid of a lot of repetition (Article in this case). Mapper could be used for this, instead of introducing Selector or something similar, but I’m currently not sure how much both concepts should be mixed. What do you think about these ideas?

I can try my best to contribute improvements to the framework after a decision will be made how the API should look like, so feel free to add me. But beware that my C++ is quite rusty (no Rust language pun intended 😉), and I’m currently digging all the changes of modern C++, so some thorough pull request reviews will be needed.

Maybe we should add this to the issue on GitHub or create a new one?

antao2002 • 3 months ago

We can create a new issue for this and I have invited you to join drogon on github :)

antao2002 • 3 months ago

I will take time to think about what you write with so many details.

chiefsucker • 3 months ago

@antao2002

I have some questions before I move this to a new GitHub issue. As far as I currently understand there is no support for prepared statements when you want to write SQL queries? Is it currently only possible to bind parameters when you use the ORM API (with the help of Criteria)?

chiefsucker • 3 months ago

Regarding prepared statements for plain SQL queries: I just found the documentation and also the support with the custom syntax.

antao2002 • 3 months ago

You could write a sql with placeholders to bind parameters, for example:

*dbClient<<"select * from users where user_id=?" <<1000 >> callback >> exceptionCalblack; (Mysql or sqlite3)
*dbClient<<"select * from users where user_id=$1"<<1000 >> callback >> exceptionCalblack; (PostgreSQL)
Log in to comment