[odb-users] ODB and aggregate functions

Boris Kolpackov boris at codesynthesis.com
Thu Sep 22 08:30:23 EDT 2011

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

#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


More information about the odb-users mailing list