Sqlite_orm: left outer join does not serialize correctly

[juandent]

This is the string created by prepare in line 11881 of sqlite_orm.h:

SELECT 't'."fkey_account_other", 't'."fkey_account_own", 'a'."id_account", 'b'."id_account" 
FROM 'Transaccion' t  
LEFT OUTER JOIN  '' ON ('t'."fkey_account_other" = 'a'."id_account") 
INNER JOIN  'Account' 'b' ON ('t'."fkey_account_own" = 'b'."id_account") 

the C++ code for getting this is as follows:

using als_t = alias_t<Transaccion>;
using als_a = alias_a<Account>;
using als_b = alias_b<Account>;

auto lines = Storage::getStorage().select(columns(
    alias_column<als_t>(&Transaccion::fkey_account_other),
    alias_column<als_t>(&Transaccion::fkey_account_own),
    alias_column<als_a>(&Account::id_account),
    alias_column<als_b>(&Account::id_account)),
    left_outer_join<als_a>(on(c(alias_column<als_t>(&Transaccion::fkey_account_other)) == alias_column<als_a>(&Account::id_account))),
    inner_join< als_b>(on(c(alias_column<als_t>(&Transaccion::fkey_account_own)) == alias_column<als_b>(&Account::id_account))));

And the declaration for tables are:

struct Transaccion
{
    int id_transaccion;
    double amount_colones;
    double amount_dolares;
    int fkey_account_own;               // Account
    std::optional<int> fkey_account_other;          // Account optional

    date::sys_days line_date;
    std::string descripcion;
    int fkey_category;
    int fkey_concepto;
    int fkey_statement;
    int row;                     
};
struct Account
{
    int id_account;
    std::string number;             // 15103-02**-****-8467
    int fkey_bank;                  // { BAC San Jose, "Barrio Dent", { Costa Rica} }
    int fkey_account_owner;         // { Juan Dent Herrera, ... }
    std::string description;        // AMEX Cashback Premium
    bool is_tarjeta;                // true
};

what am I doing wrong?

Regards,
Juan

[fnc12]

Looks like a bug. Let me investigate it. Thank you

[fnc12]

@juandent add storage definition please

[juandent]

surely:

static auto storage =
        make_storage(db_name,
            make_table( "Statement",
                make_column("id_statement", &Statement::id_statement, autoincrement(), primary_key()),
                make_column("date", &Statement::date)),
            make_table("Categoria",
                make_column("id_category", &Categoria::id_categoria, autoincrement(), primary_key()),
                make_column("name", &Categoria::name, collate_nocase()),
                make_column("is_expense_or_income", &Categoria::is_expense_or_income)),
            make_table("Concepto",
                make_column("id_concepto", &Concepto::id_concepto, autoincrement(), primary_key()),
                make_column("name", &Concepto::name, collate_nocase()),
                make_column("fkey_account", &Concepto::fkey_account),
                foreign_key(&Concepto::fkey_account).references(&Account::id_account)),
            make_table("Account",
                make_column("id_account", &Account::id_account, autoincrement(), primary_key()),
                make_column("number", &Account::number, collate_nocase()),
                make_column("fkey_bank", &Account::fkey_bank),
                make_column("fkey_account_owner", &Account::fkey_account_owner),
                make_column("description", &Account::description, collate_nocase()),
                make_column("is_tarjeta", &Account::is_tarjeta),
                foreign_key(&Account::fkey_account_owner).references(&AccountOwner::id_owner),
                foreign_key(&Account::fkey_bank).references(&Banco::id_bank)),
            make_table("Banco",
                make_column("id_bank", &Banco::id_bank, autoincrement(), primary_key()),
                make_column("nombre", &Banco::nombre, collate_nocase()),
                make_column("ubicacion", &Banco::ubicacion, collate_nocase()),
                make_column("fkey_Pais", &Banco::fkey_pais),
                foreign_key(&Banco::fkey_pais).references(&Pais::id_pais)),
            make_table("AccountOwner",
                make_column("id_owner", &AccountOwner::id_owner, autoincrement(), primary_key()),
                make_column("name", &AccountOwner::name, collate_nocase())),
            make_table("Transaccion",
                make_column("id_transaccion", &Transaccion::id_transaccion, autoincrement(), primary_key()),
                make_column("colones", &Transaccion::amount_colones),
                make_column("dolares", &Transaccion::amount_dolares),
                make_column("fkey_account_own", &Transaccion::fkey_account_own),
                make_column("fkey_account_other", &Transaccion::fkey_account_other),
                make_column("line_date", &Transaccion::line_date),
                make_column( "descripcion", &Transaccion::descripcion),
                make_column("fkey_category", &Transaccion::fkey_category),
                make_column( "concepto", &Transaccion::fkey_concepto),
                make_column( "fkey_statement", &Transaccion::fkey_statement),
                make_column("row", &Transaccion::row),
                foreign_key(&Transaccion::fkey_account_own).references(&Account::id_account),
                foreign_key(&Transaccion::fkey_account_other).references(&Account::id_account),
                foreign_key(&Transaccion::fkey_category).references(&Categoria::id_categoria),
                foreign_key(&Transaccion::fkey_concepto).references(&Concepto::id_concepto),
                foreign_key(&Transaccion::fkey_statement).references(&Statement::id_statement)),
            make_table("Pais",
                make_column("id_pais", &Pais::id_pais, autoincrement(), primary_key()),
                make_column("name", &Pais::name, collate_nocase()))
            );

[juandent]

These are the structs:

////////// persistence structs//
struct Statement;
struct Concepto;
struct Account;
struct Pais;
struct Banco;
struct AccountOwner;
struct Transaccion;
struct Categoria;
////////////////////////////////



struct Statement 
{
    int id_statement;
    date::sys_days date;
};

struct Concepto
{
    int id_concepto;
    std::string name;               // TFT-SINPE A: 15103-02**-****-8467
    int fkey_account;               // { 15103-02**-****-8467, ...}
};

struct Account
{
    int id_account;
    std::string number;             // 15103-02**-****-8467
    int fkey_bank;                  // { BAC San Jose, "Barrio Dent", { Costa Rica} }
    int fkey_account_owner;         // { Juan Dent Herrera, ... }
    std::string description;        // AMEX Cashback Premium
    bool is_tarjeta;                // true
};

struct Pais
{
    int id_pais;
    std::string name;
};

struct Banco
{
    int id_bank;
    std::string nombre;
    std::string ubicacion;
    int fkey_pais;

    Pais getPais() const;
};

struct AccountOwner
{
    int id_owner;
    std::string name;
};


struct Transaccion
{
    int id_transaccion;
    double amount_colones;
    double amount_dolares;
    int fkey_account_own;               // Account
    std::optional<int> fkey_account_other;          // Account optional

    date::sys_days line_date;
    std::string descripcion;
    int fkey_category;
    int fkey_concepto;
    int fkey_statement;
    int row;                     // fkey_statement + row is unique

    std::shared_ptr<Account> getAccountOrigin() const;
    std::shared_ptr<Account> getAccountDestination() const;

};

struct Categoria
{
    int id_categoria;
    std::string name;
    bool is_expense_or_income;
};

[juandent]

Great! Please let me know ASAP when you correct the bug as I am stuck with the current problem!

I love your library!! Congrats!

Regards,

Juan Dent

From: Yevgeniy Zakharov [e-mail]
Sent: Monday, November 16, 2020 12:29 PM
To: fnc12/sqlite_orm [e-mail]
Cc: juandent [e-mail]; Author [e-mail]
Subject: Re: [fnc12/sqlite_orm] left outer join does not serialize correctly (#625)

Looks like a bug. Let me investigate it. Thank you

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub https://github.com/fnc12/sqlite_orm/issues/625#issuecomment-728243848 , or unsubscribe https://github.com/notifications/unsubscribe-auth/ABI6OFI4WYJCHM3LIOEO2FDSQFVPTANCNFSM4TXFVT2Q . https://github.com/notifications/beacon/ABI6OFKNBLOA7V5QCVYNON3SQFVPTA5CNFSM4TXFVT22YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOFNUB5CA.gif

[juandent]

Hi!
Have you been able to reproduce the problem?
Can I help somehow?
How much have you been able to investigate?

Regards,
Juan Dent

[fnc12]

I've already done it today in the morning. All I need to add is unit tests. I shall push it as is in a dedicated branch. You can use this branch until it is merged into dev.

[fnc12]

please work with branch https://github.com/fnc12/sqlite_orm/pull/626 for now

[juandent]

had problems with installing the #626 branch. CMake throws this:

Microsoft (R) Build Engine version 16.8.1+bd2ea1e3c for .NET Framework
Copyright (C) Microsoft Corporation. All rights reserved.

Checking Build System
Performing download step (git clone) for 'catch2-populate'
Cloning into 'catch2-src'...
fatal: invalid reference: master
CMake Error at catch2-subbuild/catch2-populate-prefix/tmp/catch2-populate-gitclone.cmake:40 (message):
Failed to checkout tag: 'master'

will have to wait for dev to have the changes integrated unless you know how to deal with this error

regards,
Juan

[juandent]

Hi,
I used the sqllite_orm.h from the #626 branch to no avail - problem persists!

Please merge to dev as soon as you are able...
or please create a test for this issue in order to make sure it's gone

[fnc12]

this is weird. I've added tests that reproduce your code here https://github.com/fnc12/sqlite_orm/pull/626/files#diff-b6b061b06cb652ce25a8365da7e31a6882de27f3d1d059c36ad0a1a457fff0aa and fixed error in them. Probably you have a different code.

[juandent]

at ease! sorry I had the wrong branch
Its working now
when do you think you will merge? will it be to dev branch?

[fnc12]

I shall merge it in a day or two into dev branch. I can merge it right now but I want to add some unit tests to make it more kosher =)

[fnc12]

I've merged it in dev branch. Please check it out

[juandent]

Congrats! It works correctly!!