[odb-users] How to change the join type of relations in a view

[علی دانش]

Hi Boris,

It's a good idea. I think in this situation (i.e. not_null) this is semantically equivalent to use 'inner join' instead of 'left join'.
Therefore it's better to use 'inner join' instead of 'left join' in this situation.
IBM Knowledge Center says:
(https://www.ibm.com/support/knowledgecenter/SSZLC2_7.0.0/com.ibm.commerce.developer.soa.doc/refs/rsdperformanceworkspaces.htm) 
     5- Use inner join, instead of outer join if possible.
        The outer join should only be used if it is necessary. Using outer join limits the database optimization options which typically results in slower SQL execution.

For object relationships, we can already change the join type in views, therefore this option (i.e. not_null) must set the default join type.
#pragma db view object(name1) object(name2 [= alias] [join-type] [: join-condition])

But for containers, there is already no way to change the join type. Therefore if 'value_not_null' is used, 'inner' must be used instead of 'left'.
The benefit of your idea is that is already supported by odb pragma language and there is no need to add anything to it.
e.g.:

#pragma db object
class employee
{
   ...
   #pragma db not_null
   shared_ptr<employer> current_employer_;
   #pragma db value_not_null
   std::vector<shared_ptr<employer>> previous_employers_;
};

Ali
________________________________________
From: Boris Kolpackov <boris at codesynthesis.com>
Sent: Monday, July 11, 2016 7:26 PM
To: علی دانش
Cc: odb-users at codesynthesis.com
Subject: Re: [odb-users] How to change the join type of relations in a view

Hi Ali,

I am still thinking. Conceptually, LEFT JOIN is all we need for object
relationships. Practically, however, this is a different story, as you
have shown.

One idea I had is to add an option that will make the ODB compiler use
INNER JOIN for NOT NULL object relationships (and probably containers).
I still need to think some more on whether this is actually semantically
equivalent so any analysis for/against is welcome.

Boris