[odb-users] Use a view to query many-to-many relationship

[Lukas Barth]

Hi,

I have a class (call it "MyObject"), each object of which holds an
integer value and is associated with a "Config" object (possibly many
MyObjects to one Config). Each Config object is associated to multiple
"ObjectKV" objects, each containing one key-value mapping.

The complete code is below for clarification.

I want to create (for example) a query that returns all "MyObjects" which:

- have value 42
- the associated config of which has exactly two Key-Value pairs, namely:
	- "Foo"->"Bar"
	- "Fuz"->"Baz"

Obviously, I don't want to hard-code this query, but the value, the
number of key-value pairs and the keys and values are dynamic in my program.

I know I can use Views to load a MyObject together with its Config by
saying

    #pragma db view object(MyObject) object(Config)
    struct my_view { … };

However, now there's a to-many relationship to the ConfigKVs. How do I
formulate a view / a query on a view that only selects the entries that
have exactly the right ConfigKVs associated? Basically, I'd need a magic
"contains" and "size" predicates in the query language like this:

    db->query(query<my_view>::value == 42 &&
                query<my_view>::cfg->kvs->contains(
                  query<ConfigKV>::key == 'foo' &&
                  query<ConfigKV>::value == 'bar') &&
                query<my_view>::cfg->kvs->contains(
                  query<ConfigKV>::key == 'fuz' &&
                  query<ConfigKV>::value == 'baz') &&
                query<my_view>::cfg->kvs->size() == 2)

Note that I modeled the 'exactly the two required KV mappings' by asking
for both to be contained and then querying for the size to be exactly
two. Are there any such predicates in the query language? Or is there a
different way of formulating this?

Here follows the complete definition of the three classes:
=======================================================================
#include <memory>
#include <odb/core.hxx>
#include <string>
#include <vector>

// Forward
class MyObject;
class Config;
class ConfigKV;

/*
 * An object has a value and one associated Config
 */
#pragma db object
class MyObject {
  int value;

  std::shared_ptr<Config> cfg;

private:
  #pragma db id auto
  unsigned long id_;
  friend class odb::access;
};

/*
 * A Config object has multiple ConfigKV objects associated, each
 * of which contains one Key-Value mapping.
 */
#pragma db object
class Config {
public:
  std::string name;

#pragma db inverse(cfg)
  std::vector<std::shared_ptr<ConfigKV>> kvs;

private:
#pragma db id auto
  unsigned long id_;
  friend class odb::access;
};

/*
 * One Key-Value mapping
 */
#pragma db object
class ConfigKV {
public:
  std::string key;
  std::string value;

  std::shared_ptr<Config> cfg;

private:
#pragma db id auto
  unsigned long id_;
  friend class odb::access;
};
=======================================================================

Thanks a lot,

Lukas