[odb-users] ODB and aggregate functions

[Nicolas ALBEZA]

Thanks a lot !

You saved my day =)

Regards,

2011/9/22 Boris Kolpackov <boris at codesynthesis.com>

> 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
>



-- 
ALBEZA "Pause" Nicolas