* GNATCOLL.SQL.SQL_Select - I'm in Left_Join hell
@ 2012-04-19 14:37 Thomas Løcke
2012-04-21 12:02 ` Stephen Leake
0 siblings, 1 reply; 3+ messages in thread
From: Thomas Løcke @ 2012-04-19 14:37 UTC (permalink / raw)
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
^ permalink raw reply [flat|nested] 3+ messages in thread
* Re: GNATCOLL.SQL.SQL_Select - I'm in Left_Join hell
2012-04-19 14:37 GNATCOLL.SQL.SQL_Select - I'm in Left_Join hell Thomas Løcke
@ 2012-04-21 12:02 ` Stephen Leake
2012-04-23 7:33 ` Thomas Løcke
0 siblings, 1 reply; 3+ messages in thread
From: Stephen Leake @ 2012-04-21 12:02 UTC (permalink / raw)
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
^ permalink raw reply [flat|nested] 3+ messages in thread
* Re: GNATCOLL.SQL.SQL_Select - I'm in Left_Join hell
2012-04-21 12:02 ` Stephen Leake
@ 2012-04-23 7:33 ` Thomas Løcke
0 siblings, 0 replies; 3+ messages in thread
From: Thomas Løcke @ 2012-04-23 7:33 UTC (permalink / raw)
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
^ permalink raw reply [flat|nested] 3+ messages in thread
end of thread, other threads:[~2012-04-23 7:33 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2012-04-19 14:37 GNATCOLL.SQL.SQL_Select - I'm in Left_Join hell Thomas Løcke
2012-04-21 12:02 ` Stephen Leake
2012-04-23 7:33 ` Thomas Løcke
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox