[odb-users] Subselects in View

[Lukas Obermann]

Hi Boris,

thank you for the help!

I think I mixed that up a bit. With „complex“ I meant building queries on-the-fly without the use of views, that use joins ans subselects.

I have two more questions regarding the native view.
How to handle if the relation from table2 to table2 is done via a lazy_shared_ptr? And, can I also reference it when the relation is done via a vector, thus having a separate table in between them.

Thanks,
Lukas



> Am 20.03.2017 um 17:21 schrieb Boris Kolpackov <boris at codesynthesis.com>:
> 
> Hi Lukas,
> 
> Lukas Obermann <obermann.lukas at gmail.com> writes:
> 
>> I know that it is not recommended to do complex queries with odb, [...]
> 
> Not recommended by whom?
> 
> You can handle pretty much any query with an ODB native view. It may not
> be as convenient as for simpler queries, but it is definitely possible
> and not "not recommended".
> 
> 
>> SELECT table1.name, (SELECT AVG(score) FROM table2 WHERE
>>  table2.t_id = table1.t_id) as score FROM table1
> 
> You can handle it pretty easily with a native view. You can even get fancy
> and do something like this:
> 
> #include <string>
> 
> #pragma db object
> struct table1
> {
>  #pragma db id
>  int t_id;
> 
>  std::string name;
> };
> 
> #pragma db object
> struct table2
> {
>  #pragma db id
>  int t_id;
> 
>  int score;
> };
> 
> #pragma db view object(table1) object(table2)                            \
>  query("SELECT " + table1::name + ", "                                  \
>        "("                                                              \
>        " SELECT AVG(" + table2::score + ")"                             \
>        " FROM " + (object_traits_impl<table2, id_common>::table_name) + \
>        " WHERE " + (table2::t_id == table1::t_id) +                     \
>        ") AS score"                                                     \
>        "FROM " + (object_traits_impl<table1, id_common>::table_name))
> struct view
> {
>  std::string name;
>  int avg_score;
> };
> 
> You can read moe about table_name here:
> 
> [1] http://www.codesynthesis.com/pipermail/odb-users/2016-April/003209.html
> 
> Boris