[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
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
More information about the odb-users
mailing list