[odb-users] Self join WHERE value IS NULL

[Boris Kolpackov]

Hi Tom,

Tom Stoffer <tom at touchsurgery.com> writes:

> Please could you elaborate on your second suggestion. How would you go
> about defining a view which checks for no related objects. Please note that
> the object I am looking for is inverse, meaning it is not in the database.
> So far as I have seen it is not possible to use inverse values in queries?

You can use inverse pointers in queries. What you cannot do is use containers
in queries. But you can use container-based relationships in view JOIN's. So
the idea in a nutshell is this:

> #pragma db object
> class employee
> {
>     #pragma db id
>     std::string name_;
>     
>     #pragma db value_not_null inverse(down)
>     std::vector<odb::lazy_ptr<employee>> up;
>
>     #pragma db value_not_null unordered
>     std::vector<std::shared_ptr<employee>> down;
> };

#pragma db view                       \
  object(employee)                    \
  object(employee = up: employee::up) \
  query(distinct)
struct employee_up
{
  #pragma db column (employee::name_)
  std::string name
};

using eu_query = odb::query<employee_up>

db.query<employee_up> (eu_query::up::name.is_no_null ());

This will return all the employees that have non empty 'up' containers.

Now, thinking about it, I believe this will also omit employees that
have 'up' containers with only NULL pointers.

Boris