please add a function to convert SQL query result to std::vector<Json::Value>

[silentmissile]

in doc https://github.com/drogonframework/drogon-docs/blob/master/ENG-08-1-DataBase-DbClient.md there is an example to run SQL query directly

auto clientPtr = drogon::app().getDbClient();
clientPtr->execSqlAsync("select * from users where org_name=$1",
                            [](const drogon::orm::Result &result) {
                                std::cout << result.size() << " rows selected!" << std::endl;
                                int i = 0;
                                for (auto row : result)
                                {
                                    std::cout << i++ << ": user name is " << row["user_name"].as<std::string>() << std::endl;
                                }
                            },
                            [](const DrogonDbException &e) {
                                std::cerr << "error:" << e.base().what() << std::endl;
                            },
                            "default");

we can see, the return class of SQL query result is iterable. For each iterator, the item is a dict of row class.

in drogon/orm/Result.h, there is no constructor function with empty parameters. So it's impossible to construct an empty Result object. In the lambda function, we can not capture an empty Result object and transfer the SQL query result out of lambda function scope.

similarly, there is no constructor function of class Row, so we can't prepare an empty std::vector<Row> to contain the SQL query result out of lambda function scope.

sometimes we need to handle the query result in complex functions, so we need to extrac the data. But there is no function to do it. We have to read the database description to find the column name and column data type, and put it in many lines similar like:

ext_obj[i]["col_name"] = result[i]["col_name"].as<data_type>()

could you please provide a function to convert SQL query result to std::vector, then we can simply extract the data and handle it out of lambda function scope.

[an-tao]

Basically, drogon's ORM is what you need here, have you tried it?

[silentmissile]

yes, I've tried ORM. But we all know ORM can't resolve every trouble.

For example, in Users table, there are 8 columns, in Login action, we just need to check 3 columns username, password, is_active, if we transfer data of all 8 columns, that would bring more load of database server and net tranform.

Of cource, divide a big table with many columns to several tables and connect them by "have one" relationship can resolve this trouble. But this means sometimes we have to take a SQL query across many tables, "many to many" can handle only 3 tables, if we divide a table to many tables, maybe we have to take a query across too many tables.

For example. Here is a Users table, and a Companies table, to reduce Users table query load, we divide the user's company information out of User table, and assign it to User_to_Company table. Then we have a Role table, and role is relatade to company, and the relationsip is "many to many". And there is another table AccessRight with "many to many" relationship to Role. If we want to query a users access right, we have to query User->User_to_Company->Companies->"many to many" intermedia table->Role->"many to many" intermedia table->AccessRight. It's very boring to write the query by ORM, and all columns of all tables would be returned, but we only need severy columns, and we can't filter columns, the ORM can't realize this feature. Divide the complecated query to several simple queries would make coding easier, but it's stupid to the database server.

I believe there must be some more complecated query in practical development. As we all know, manuly edit SQL expression is must for some application. And it's very good SQL expression can be performed directly in drogon. The only pity is it's difficult to extract the query result out. If you can add this function to convert SQL query result to std::vector<Json::Value>, it'll be perfect.

[an-tao]

Thanks for your explanations. but I'm a little confused that why you need to extract the query result out. you could do the follow-up work in the callback function or use the synchronous interface (or the coroutine interface as well) to get the result object as a return value.