[odb-users] Loading persistent object using custom joins

[Patrick Rotsaert]

Hi Boris,

thanks for the swift answer. I think I'll manage it like that.

Any chance there will be a future possibility to load objects directly from arbitrary SQL queries (select tablename.* from...), perhaps only specifying the part starting from 'from...' ?
The built-in query language covers most of my use cases, but it's for that occasional special situation that I think that might be really useful.

Pat

On 3-mrt.-2013, at 18:00, Boris Kolpackov <boris at codesynthesis.com> wrote:

> Hi Patrick,
> 
> Patrick Rotsaert <Patrick.Rotsaert at intoit.be> writes:
> 
>> I know joins can be done with views, but if I understand correctly,
>> views contain only a subset of the objects involved.
> 
> More precisely, views can contain a subset or a full set of data
> members from one or more objects.
> 
> 
>> #pragma db object
>> class employer
>> {
>>  ...
>> 
>>  #pragma db id
>>  unsigned long id_;
>> 
>>  std::string name_;
>> };
>> 
>> #pragma db object
>> class employee
>> {
>>  ...
>> 
>>  #pragma db id
>>  unsigned long id_;
>> 
>>  std::string first_;
>>  std::string last_;
>> 
>>  unsigned short age_;
>> 
>>  shared_ptr<employer> employer_;
>> };
>> 
>> Suppose I'd like to query all employers that have employees under 30 
>> years of age.
> 
> In ODB you can reference data members from the pointed-to objects (one
> level deep) in queries. So, if, for example, you wanted to find all the
> employees that have "Example, Inc" as an employer, then you could do:
> 
> typedef odb::query<employee> query;
> typedef odb::result<employer> result;
> 
> result r (db.query<employee> (query::empployer->name == "Example, Inc"));
> 
> Your query is a bit different, however. First of all, there is no "back"
> pointer from employer to employees, but even if we added one, it would
> have been a container and there is no support for using containers in
> queries, yet.
> 
> 
>> In SQL speak, that would be something like:
>> 	SELECT DISTINCT employer.*
>> 	FROM employee
>> 	NATURAL JOIN employer
>> 	WHERE employee.age < 30
>> 
>> How can this be done, so I have an odb::result<employer> to work with?
> 
> The only way to achieve this that I can think of is to use a view to get
> the list of ids of all the employers that have employees under 30. Then,
> you can use these ids to load each employer:
> 
> #pragma db view object(employer) \
>                object(employee) \
>                query((?) + "GROUP BY" + employer::id_)
> struct employers
> {
>  #pragma db column(employer::id_)
>  unsigned long id;
> };
> 
> typedef odb::query<employers> query;
> typedef odb::result<employers> result;
> 
> result r (db.query<employers> (query::employee::age < 30));
> 
> for (const employers& es: r)
> {
>  shared_ptr<employer> e (db.load<employers> (es.id));
>  ...
> }
> 
> Boris