Exploring the limits of sqlite_orm

[juandent]

Hi Eugene,

I am exploring the limits of sqlite_orm. I found an interesting SQL statement which I think is not currently possible yet runs in SQLITE with no problem:

select * from (select salary, comm as commmission from emp)  where salary< 5000

Interesting!!

[juandent]

here is another one worth considering with similar structure (which means solving one solves the other):

select ename, salary, comm from (
select ename, salary, comm, case when comm is null then 0 else 1 end as is_null from emp) y
order by is_null desc, comm

[juandent]

this last one can be simplified like this, and sqlite_orm can do it:

select ename, salary, comm from emp
order by case when comm is null then 0 else 1 end desc

like this:

storage.prepare(select(columns(&Employee::m_ename, &Employee::m_salary, &Employee::m_commission), 
        order_by(case_<int>().when(is_null(&Employee::m_commission), then(0)).else_(1).end()).desc()));

[juandent]

I still believe supporting from subquery would be a nice addition and very powerful!!

[fnc12]

yeah it is called 'dynamic from' what you are speaking about. I need to think about API cause this is a place where dynamic SQLite stuff faces static sqlite_orm principles. If you have any ideas please feel free to offer. Thanks

[fnc12]

Looks like user first needs to declare cte (common table expression) before calling such functions:

//    select * 
//    from (select salary, comm as commmission 
//          from emp)
//    where salary< 5000
struct EmpCte {
    int salary = 0;
    int comm = 0;
};
auto empCte = make_table("emp",
    make_column("salary", &EmpCte::salary),
    make_column("comm", &EmpCte::comm),
);
auto rows = storage.select(asterisk(), from(empCte), where(c(&EmpCte::salary) < 5000));

[juandent]

I wrote like this:

    struct EmpCte {
        int salary = 0;
        int comm = 0;
    };
    auto empcte = make_table("emp_inter",
        make_column("salary", &EmpCte::salary),
        make_column("comm", &EmpCte::comm));
    auto statement = storage.prepare(select(asterisk<EmpCte>(), from<decltype(empcte)>(), where(c(&EmpCte::salary) < 5000)));
    auto sql = statement.expanded_sql();
    auto rows = storage.execute(statement);

I get this error:

error C2039: 'column_name': is not a member of 'sqlite_orm::internal::storage_impl<>'

[juandent]

seems we are close but not yet...

[fnc12]

this code doesn't work right now cause EmpCte and decltype(empcte) are not mapped types of the storage. Also I'd omit type either in asterisk call or in from call cause they are the same like this:

auto statement = storage.prepare(select(asterisk<EmpCte>(), where(c(&EmpCte::salary) < 5000)));

or

auto statement = storage.prepare(select(asterisk(), from<EmpCte>(), where(c(&EmpCte::salary) < 5000)));

[trueqbit]

BTW, this is working in the CTEs feature branch, example select_from_subselect().