ORM and rowset

[Tectu]

Having switched over to SOCI has been a good experience so far. However, there one thing in my application which keeps me rather nervous regarding ORM & rowsets.

Let there be a type person:

struct person
{
    int id = -1;
    std::string name;
}

We can provide a type conversion like this:

namespace soci
{
    template<>
    struct type_conversion<person>
    {
        static void from_base(const values& v, indicator ind, person& p)
        {
            p.id = v.get<int>("id", -1);
            p.name = v.get<std::string>("name", "");
        }

        static void to_base(const person& p, values& v, indicator& ind)
        {
            v.set("id", p.id);
            v.set("name", name);

            ind = i_ok;
        }
    };
}

Which then allows us to do something like this:

sql << "SELECT * FROM persons WHERE id=:1 LIMIT 1", soci::use(42), soci::into(p);

This is great. Where I start to gulp is when I need to retrieve several records. I would have assumed that we can simply feed an std::vector<person> into soci::use(). However, if I understand the documentation correctly that is not possible.

Right now, I do this instead:

std::vector<person> ret;
soci::rowset<soci::row> rs = (sql.prepare << "SELECT * FROM persons LIMIT 100 OFFSET 0");
for (soci::rowset<soci::row>::const_iterator it = rs.begin(); it != rs.end(); ++it) {
    const soci::row& row = *it;

    person p;
    p.id = row.get<int>("id", -1);
    p.name = row.get<std::string>("name", "");

    ret.push_back(std::move(p));
}

Which is really not great as there are now two places where the ORM data binding happens (once where the soci::type_conversion<> is defined and then again everywhere where I want to retrieve multiple person objects.

I feel like I am missing a critical piece of information to avoid this. How can I use the same soci::type_conversion<person> when my query returns multiple rows?

[zann1x]

What you're trying to do with std::vector<person> is currently unsupported, yes.

I usually use move_iterators in such cases. So in your example:

soci::rowset<person> rs = (sql.prepare << "SELECT * FROM persons LIMIT 100 OFFSET 0");

std::vector<person> ret{std::make_move_iterator(rs.begin()), std::make_move_iterator(rs.end())};

[Tectu]

This.... is so painfully obvious now.

Thank you - thank you a lot!

[asmwarrior]

What you're trying to do with std::vector<person> is currently unsupported, yes.

I usually use move_iterators in such cases. So in your example:

soci::rowset<person> rs = (sql.prepare << "SELECT * FROM persons LIMIT 100 OFFSET 0");

std::vector<person> ret{std::make_move_iterator(rs.begin()), std::make_move_iterator(rs.end())};

Sorry to ask a question here. I just tired this method, and I found that the generated std::vector has the size() function return 0, which means the construction from the soci::rowset does not work correctly.

If I remove the std::make_move_iterator wrapper, I still get the empty std::vector. Any idea on how to solve this issue.

Thanks.

[asmwarrior]

Here is my full test code:

1, the definition of the person class:

class person
{
public:
    int id;
    std::string name;
    int age;
};

namespace soci
{
    template<>
    struct type_conversion<person>
    {

        typedef values base_type;

        static void from_base(const values& v, indicator ind, person& row)
        {
            row.id =   v.get<int>("id", -1);
            row.name = v.get<std::string>("name", "");
            row.age =  v.get<int>("age", 0);

        }

        static void to_base(const person& row, values& v, indicator& ind)
        {
            v.set("id",   row.id);
            v.set("name", row.name);
            v.set("age",  row.age);

            ind = i_ok;
        }
    };
}

2, here the is testing code:

        soci::session sql(soci::mysql, "dbname=testdb user=root password=123456");
        try
        {
            soci::rowset<person> rs = (sql.prepare << "SELECT * FROM testtable");

            // Loop through rowset using a range-based for loop
            for (const auto& row : rs) {
                // Do something with the current row
                // For example, print the value of a column
                std::cout << row.id << std::endl;
            }

            // Loop through rowset using a standard for loop
            for (auto it = rs.begin(); it != rs.end(); ++it) {
                const person& row = *it;
                // Do something with the current row
                // For example, print the value of a column
                std::cout << row.id << std::endl;
            }

            //std::vector<person> ret{std::make_move_iterator(rs.begin()), std::make_move_iterator(rs.end())};
            std::vector<person> ret{rs.begin(), rs.end()};

            int s = ret.size();

            std::cout << s << std::endl;

            for (int i = 0; i< ret.size(); i++)
            {
                std::cout << ret[i].id << std::endl;
            }

        }
        catch(const soci::soci_error& e)//Catch the exception and output the exception message
        {
            std::cerr << "Error: " << e.what() << std::endl;
        }

3, this is the database I created: ( I exported the table from the HeidiSQL software tool)

CREATE TABLE IF NOT EXISTS `testtable` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

REPLACE INTO `testtable` (`id`, `name`, `age`) VALUES
    (1, 'aaa', 23),
    (3, 'bbb', 43);

The result is:

1, The first for loop works. 2, The second for loop does not work, I just checked by debugging that the rs.begin() and rs.end() are just the same, so I have no way to run the for loop. 3, The std::vector constructed from the soci::rowset is empty dure to the above iterator issue.

I'm using soci version 4.0.3 from Package: mingw-w64-x86_64-soci - MSYS2 Packages.