Re: [gnome-db] GdaSqlBuiler: JOIN AS

On 17 May 2010 15:06, Murray Cumming <[email protected]> wrote:
> On Mon, 2010-05-17 at 15:02 +0200, Vivien Malerba wrote:
>> On 17 May 2010 14:53, Murray Cumming <[email protected]> wrote:
>> > On Mon, 2010-05-17 at 14:43 +0200, Vivien Malerba wrote:
>> >> On 17 May 2010 14:39, Murray Cumming <[email protected]> wrote:
>> >> > On Mon, 2010-05-17 at 14:20 +0200, Vivien Malerba wrote:
>> >> >> On 17 May 2010 10:07, Murray Cumming <[email protected]> wrote:
>> >> >> > In Glom, I use the AS keyword with JOINS, to allow multiple joins with
>> >> >> > the same table. A simple case looks like this, though it's unnecessary
>> >> >> > in this particular example:
>> >> >> >
>> >> >> > SELECT "albums"."album_id", "albums"."name", "albums"."artist_id",
>> >> >> > "relationship_artist"."name",
>> >> >> > FROM "albums"
>> >> >> > LEFT JOIN "artists"
>> >> >> >  AS "relationship_artist"
>> >> >> >  ON ("albums"."artist_id" = "relationship_artist"."artist_id")
>> >> >> > WHERE "albums"."album_id" = 123
>> >> >> >
>> >> >> > (That might be an "alias", but I don't know if that's the right
>> >> >> > terminology.)
>> >> >> >
>> >> >> > But I don't see how to do that with
>> >> >> > gda_sql_builder_select_join_targets()
>> >> >> > http://library.gnome.org/devel/libgda/unstable/GdaSqlBuilder.html#gda-sql-builder-select-join-targets
>> >> >> >
>> >> >>
>> >> >> To do this you need to specify an alias when using the
>> >> >> gda_sql_builder_select_add_target*() methods.
>> >> >
>> >> > But that's for adding table targts, right? How would it know which join
>> >> > the alias name is for?
>> >> >
>> >>
>> >> The alias is for a target (table or sub select), not for a join: you
>> >> don't need to name joins as they are never referenced anywhere else in
>> >> the SQL statement, you can name targets if necessary.
>> >
>> > In my example, the join name is mentioned in the list of fields. For
>> > instance, relationship_artist.name.
>> >
>> > Here's a silly example that shows the need for the name, because using
>> > the table name would be ambiguous:
>> >
>> > SELECT "albums"."album_id", "albums"."name",
>> > "albums"."artist_singer_id", "relationship_artist_singer"."name",
>> > "albums"."artist_drummer_id", "relationship_artist_drummer"."name",
>> >  FROM "albums"
>> >  LEFT JOIN "artists"
>> >    AS "relationship_artist_singer"
>> >    ON ("albums"."artist_singer_id" =
>> > "relationship_artist_singer"."artist_id")
>> >  LEFT JOIN "artists"
>> >    AS "relationship_artist_drummer"
>> >    ON ("albums"."artist_drummer_id" =
>> > "relationship_artist_drummer"."artist_id")
>> > WHERE "albums"."album_id" = 123
>> >
>>
>> This example illustrates why you need to be able to set targets'
>> aliases. In this example you have the following targets:
>> A: "albums"
>> B: "artists" with the "relationship_artist_singer" alias
>> C: "artists" with the "relationship_artist_drummer" alias
>>
>> and you reference the targets using their alias in the join condition
>> and in the list of fields to be selected.
>>
>> and 2 joins (joinning conditions not mentionned here):
>> A <--> B
>> A <--> C
>>
>> the joins themselves are never referenced anywhere in the statement,
>> so they are not named.
>
> So the SQL statement would/could look different?

When using the GdaSqlBuilder, the rendered sql statement looks like
the one in your example (it is normal SQL, no GDA extension).

Vivien