[odb-users] Subselects in View

[Boris Kolpackov]

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