[odb-users] Querying entity's container

[Boris Kolpackov]

Hi Adnan,

Adnan RIHAN <axel50397 at gmail.com> writes:

> I need to find an entity based of one of it’s container’s data.
> 
> I have the Product entity, which contains a Containers of String
> (QSet<QString>, unique in the table) to store the barcodes.

Support for containers in queries is the next major feature on
the TODO list.

In the meantime:

If we are talking about containers of object pointers (not your
case, I know), then a fairly straightforward workaround is to
use an object loading view (Section 10.2, "Object Loading Views"
in the ODB manual has an example of this specifically).

If it is a container of values (like your case), then things are
a bit harder and we have to resort to a fairly hackish workaround.
In a nutshell, the idea is to define a class that is an "image"
of the container table and then JOIN it into an object loading
view, just like in the above case.

Here is an example. Say we have this product class:

#pragma db object
struct product
{
public:
  #pragma db id auto
  unsigned long id_;

  std::set<std::string> barcodes_;
};

If we generate the database schema for it, the container table will
look like this (using SQLite):

CREATE TABLE "product_barcodes" (
  "object_id" INTEGER NOT NULL,
  "value" TEXT NOT NULL,
  CONSTRAINT ...);

Next we create a special "image" object that matches this table:

#pragma db object table("product_barcodes") no_id abstract
struct product_barcode
{
  person* object_id;
  std::string value;
};

A couple of things to note about this object:

1. It uses the same table name as the container table (product_barcodes).
   In a way, we are overlaying this object on the container table.

2. object_id and value member names are not arbitrary, they should
   match the table columns (or you can use db column() pragma to
   assign them explicitly).

Once this is done, we create an object loading view that JOIN's
product and its container:

#pragma db view object(product) object(product_barcode = barcode) \
  query(distinct)
struct product_view
{
  std::chared_ptr<product> p;
};

Now we can use this view to load a product that matches a barcode:

typedef odb::query<product_view> query;

db.query<product_view> (query::barcode::value == "123456");

Boris