[odb-users] Re: 2-table query

MM finjulhich at gmail.com
Fri Sep 11 14:57:47 EDT 2015


On 7 September 2015 at 17:36, Boris Kolpackov <boris at codesynthesis.com>
wrote:

> Hi,
>
> MM <finjulhich at gmail.com> writes:
>
> > SELECT * FROM
> > F LEFT OUTER JOIN M ON F.mid = M.ID
> > WHERE (F.month = M.month1 OR F.month = M.month2)
>
> Good, this is much better than saying "I need to achieve X, do it
> for me".
>
>
> > Is this expressable as a high-level odb query?
>
> Yes, pretty easily using ODB views.
>
> Boris
>

I am struggling after reading chapter 10. Let me put the schema here again:

M
==
id: PRIMARY KEY
month1: TEXT (encoded month,year combination)
month2: TEXT

F
==
id: PRIMARY KEY
mid: NOT NULL REFERENCES M("id")
month: TEXT

There can 0..N rows in F for each row in M.

The intended SQL query is:
> SELECT * FROM
> F LEFT OUTER JOIN M ON F.mid = M.ID <http://m.id/>
> WHERE (F.month = M.month1 OR F.month = M.month2)


class F : public SomeBase {   /// SomeBase provides get_m and set_m
public:
  const M* get_m() const override;   /// this is how I access the related M
instance
  static const std::string type;   /// identifies which of the derived
classes from SomeBase this is
};

#pragma db object(F) table("F") definition
#pragma db member(F::mid) virtual(std::uint32_t)\
  get(this.get_m()? this.get_m()->id: std::uint32_t())\
  set(this.set_m(find_m(F::type,(?))))


1)
I explicitly load all the M instances and populate them myself in a
boost::ptr_map
db.query<M>();
//// use the iterator .load() to get a raw pointer to M and take ownership
of it.

2) I then load the F instances. Either I load them all, the same way as I
load the Ms (store them in their own ptr_map), or I want to load only those
Fs where the month in either month1 or month2 of the relevant M, and store
the loaded instances (by getting raw pointers and storing them in my
ptr_map)

I added the following view:

//// Specifying the joint condition to be    F::get_m   to tell odb that's
how you get the M instance from the F instance
#pragma db view object(F) object(M: F::get_m)\
  query( F::month.in (M::month1, M::month2) )
struct F_current {
  F* f;
};

odb generates the ?xx files, but it doesn't compile

All the M objects are already loaded and stored in the ptr_map. I do not
want the view loading to load them again.
The odb loader of the F instances can find the relevant instances because I
provided the get and set accessors in the pragma for F.


Is this the way to express this view?

Regards,

MM


More information about the odb-users mailing list