[odb-users] ODB and aggregate functions

[Boris Kolpackov]

Hi Nicolas,

> 2011/8/19 Boris Kolpackov <boris at codesynthesis.com>
> 
> > Nicolas ALBEZA <n.albeza at gmail.com> writes:
> >
> > I'm looking for a way to use aggregate (group-by) functions with ODB, to
> > use in simple queries, such as "SELECT AVG(field) from table where ...".
> > It seems that ODB doesnt allow such things, so i guess i'll have to use
> > the low-level and db specific functions to reach my goal. Is there any
> > examples or documentation about that ? (I'm using a mysql DB).
> >
> I think the 'view' feature that is still in development will allow you
> to achieve this pretty easily.

Wanted to let you know that the 1.6.0.a2 pre-release that I just announced[1]
adds support for views and the above could be achieved quite easily using
this feature.

If we have an object, for example:

#pragma db object
class person
{
  ...

  #pragma db id
  unsigned long id_;

  std::string first_;
  std::string last_;

  unsigned short age_;
};

And we would like to find the average age, then we can create a view like
this:

#pragma db view object(person)
class average_age
{
  #pragma db column("AVG(" + person::age_ + ")")
  std::size_t value;
};

And then use it like this:

typedef odb::query<average_age> query;
typedef odb::result<average_age> result;

// Average for all objects.
//
result r1 = db.query<average_age> (); 

const average_age& a1 (*r1.begin ());
cerr << a1.value << endl;

// Average for objects with the "Doe" last name.
//
result r2 = db.query<average_age> (query::last == "Doe");

const average_age& a2 (*r2.begin ());
cerr << a2.value << endl;

You can also do this for an ad-hoc database table that doesn't have
a corresponding object. In this case, the view will look like this:

#pragma db view query("SELECT AVG(age) FROM person")
class average_age
{
  std::size_t value;
};

And the usage like this:

result r1 = db.query<average_age> (); 
result r2 = db.query<average_age> ("last = " + query::_val ("Doe"));


[1] http://www.codesynthesis.com/pipermail/odb-users/2011-September/000304.html

Boris