[odb-users] Self join WHERE value IS NULL

[Tom Stoffer]

Hi Boris,

I have a question regarding querying using views. Here is my object structure:

#pragma db object
class employee
{
    friend class odb::access;
    
public:
    employee(){};
    employee(std::string name){
        name_ = name;
    }

    #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;
};

The query I am trying to perform is to get all the employees who do not have any up relations. I have attempted using Views for this but have struggled to get the correct query for checking if up is null. Especially seeing as though it is an inverse relation, hence not appearing in the DB. After reading chapter 10 and the mailing list, still no luck.

I have managed to accomplish what I need using a native view as below, although I would like to avoid using native views if possible.

#pragma db view query("SELECT employee.name FROM employee LEFT JOIN employee_down ON employee.name = employee_down.value WHERE employee_down.value IS NULL")
struct NativeEmployeeView
{
#pragma db column(employee::name_)
    std::string name_;
};

Much thanks for all your work on this project!

Tom