GNATCOLL.SQL.SQL_Select - I'm in Left_Join hell

GNATCOLL.SQL.SQL_Select - I'm in Left_Join hell

[Thomas Løcke]

Hey all,

I'm hoping some GNATCOLL.SQL specialist can help me write a SQL_Select
call that isn't as horribly ugly as the one I've currently come up with.

The SQL query I'm frantically trying to create is this:

https://gist.github.com/2421273

A fairly straightforward query with a bunch of JOIN's. Nothing overly
complicated and very easy to read.

Next lets take a look at the GNATCOLL.SQL.SQL_Select call I've come up
with:

https://gist.github.com/2421241

It is, IMHO, very unreadable with all those nested Left_Join calls. Is
there a better/prettier way, without falling back to embedding an SQL
string in the Ada source?

The SQL generated by the above SQL_Select call works fine, it's just
that I'm finding it very hard to read.

Any and all suggestions are more than welcome!

:o)

-- 
Thomas L�cke | thomas@12boo.net | http://12boo.net

Re: GNATCOLL.SQL.SQL_Select - I'm in Left_Join hell

[Stephen Leake]

Thomas Løcke <thomas@12boo.net> writes:

> https://gist.github.com/2421241
>
> It is, IMHO, very unreadable with all those nested Left_Join calls. Is
> there a better/prettier way, without falling back to embedding an SQL
> string in the Ada source?

> The SQL generated by the above SQL_Select call works fine, it's just
> that I'm finding it very hard to read.

You could do each Left join in separate statements before the
SQL_Select, and pass the final table to SQL_Select.

or use named association to reorder the lines:

  Left_Join
      (Full => Organization,
       On   => Organization.Org_Id = Contactentity_Organization.Org_Id,
       Partial => Left_Join
         (Full => Contactentity_Organization,
          On   => Contactentity.Ce_Id = Contactentity_Organization.Ce_Id,
          Partial => Left_Join
            (Full => Contactentity,
             On   => Contactentity.Ce_Id = Contactentity_Tag.Ce_Id,
             Partial => Left_Join
               (Contactentity_Tag,
                Tag,
                Tag.Tag_Id = Contactentity_Tag.Tag_Id)))),

I don't like the 'full' and 'partial' names, but this looks a little better.
   
-- 
-- Stephe

Re: GNATCOLL.SQL.SQL_Select - I'm in Left_Join hell

[Thomas Løcke]

On 04/21/2012 02:02 PM, Stephen Leake wrote:
> You could do each Left join in separate statements before the
> SQL_Select, and pass the final table to SQL_Select.
>
> or use named association to reorder the lines:
>
>    Left_Join
>        (Full =>  Organization,
>         On   =>  Organization.Org_Id = Contactentity_Organization.Org_Id,
>         Partial =>  Left_Join
>           (Full =>  Contactentity_Organization,
>            On   =>  Contactentity.Ce_Id = Contactentity_Organization.Ce_Id,
>            Partial =>  Left_Join
>              (Full =>  Contactentity,
>               On   =>  Contactentity.Ce_Id = Contactentity_Tag.Ce_Id,
>               Partial =>  Left_Join
>                 (Contactentity_Tag,
>                  Tag,
>                  Tag.Tag_Id = Contactentity_Tag.Tag_Id)))),
>
> I don't like the 'full' and 'partial' names, but this looks a little better.
>



I ended up with this:

https://gist.github.com/2469364

Which is good enough. I can read it, and I can reuse the JOIN's in other
queries.

So all in all it ended pretty well.  :o)


-- 
Thomas Løcke | thomas@12boo.net | http://12boo.net