[odb-users] Re: odb-users Digest, Vol 72, Issue 14

[Tom Stoffer]

Hi Boris,

Thanks for the reply, I think we are getting close! There is still an issue
with your above suggestion however.

When executing the query you specify, the database throws an error:
"thrown: 1: no such table:"

After looking into the sql that the view generates, it seems it is trying
to join on the employee_up table. As mentioned in my last post, up is an is
inverse relation so does not have a corresponding table.
I am not sure how the odb compiler works but I would have thought that this
should be handled there? Is there some sort of flag that needs to be
specified in the view to tell the odb compiler to switch the tables when
generating the sql?

Here is the generated sql:

    query_base_type r (

      "SELECT DISTINCT "

      "\"up\".\"name\" ");


    r += "FROM \"employee\"";


    r += " LEFT JOIN \"employee_up\" ON";

    r += "\"employee_up\".\"object_id\"=\"employee\".\"name\"";


    r += " LEFT JOIN \"employee\" AS \"up\" ON";

    r += "\"employee_up\".\"value\"=\"up\".\"name\"";


Cheers

Tom

On Tue, Aug 23, 2016 at 5:01 PM, <odb-users-request at codesynthesis.com>
wrote:

> Send odb-users mailing list submissions to
>         odb-users at codesynthesis.com
>
> To subscribe or unsubscribe via the World Wide Web, visit
>         http://codesynthesis.com/mailman/listinfo/odb-users
> or, via email, send a message with subject or body 'help' to
>         odb-users-request at codesynthesis.com
>
> You can reach the person managing the list at
>         odb-users-owner at codesynthesis.com
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of odb-users digest..."
>
>
> Today's Topics:
>
>    1. Re: Self join WHERE value IS NULL (Boris Kolpackov)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Mon, 22 Aug 2016 18:36:32 +0200
> From: Boris Kolpackov <boris at codesynthesis.com>
> Subject: Re: [odb-users] Self join WHERE value IS NULL
> To: Tom Stoffer <tom at touchsurgery.com>
> Cc: odb-users at codesynthesis.com
> Message-ID: <boris.20160822183230 at codesynthesis.com>
> Content-Type: text/plain; charset=us-ascii
>
> Hi Tom,
>
> Tom Stoffer <tom at touchsurgery.com> writes:
>
> > #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")
>
> If you need to test container entries for NULL, then this is the only
> way since there is no support for using container elements in query
> conditions, yet.
>
> In this particular case it is not clear what the benefit of having
> NULL pointers in the container when you could just remove the entry
> altogether. In this case I think you would have been able to test
> this (i.e., no related objects) using a non-native view.
>
> Boris
>
>
>
> ------------------------------
>
> _______________________________________________
> odb-users mailing list
> odb-users at codesynthesis.com
> http://codesynthesis.com/mailman/listinfo/odb-users
>
>
> End of odb-users Digest, Vol 72, Issue 14
> *****************************************
>