On Sunday, August 7, 2016 at 1:22:55 PM UTC-7, Denis Chernov wrote:
>
> I discovered a strange behaviour on join query generation today. Tried
> searching the docs and this group, but to no avail.
> Code speaks better than words so I'll just show it as is. The table
> structure is as follows:
>
> create_table(:langs) do
> primary_key :id
> column :lang_name, 'VARCHAR(64)', null: false
> constraint(:language_name_min_length) { char_length(lang_name) > 0 }
> end
>
> create_table(:books) do
> # Keys
> primary_key :id
> foreign_key :original_lang, :langs
> foreign_key :translated_lang, :langs
>
> # Metadata
> column :created_at, 'TIMESTAMP WITH TIME ZONE', null: false, default:
> 'NOW()'
> column :updated_at, 'TIMESTAMP WITH TIME ZONE', null: false, default:
> 'NOW()'
> column :type, 'SMALLINT', null: false
>
> # Select data
> column :original_title, 'VARCHAR(128)', null: false, unique: true
> column :translated_title, 'VARCHAR(128)', null: false, unique: true
> column :description, 'TEXT'
>
> # Constraints
> constraint(:original_title_min_length) { char_length(original_title) > 5
> }
> constraint(:translated_title_min_length) { char_length(translated_title)
> > 5 }
>
> # Indexes
> index :id, where: { type: 0 }
> end
>
> alter_table :books do
> set_column_not_null :translated_lang
> set_column_not_null :original_lang
> end
>
> The query is a follows:
>
> DB[:books].left_join(:langs___original_langs, { :id => :original_lang })
> .left_join(:langs___translated_langs, { :id => :translated_lang })
> .select(
> :books__id___id, # Due to join
> :original_title,
> :translated_title,
> :original_langs__lang_name___original_lang,
> :translated_langs__lang_name___translated_lang,
> :type
> )
>
> I'm not sure whether I misunderstood the docs and made some mistake or it
> really is a bug, but it gives me an error and postgres logs show this:
>
> ERROR: column books.langs does not exist at character 237
> STATEMENT: SELECT "books"."id" AS "id", "original_title",
> "translated_title", "original_langs"."lang_name",
> "translated_langs"."lang_name" AS "translated_lang", "type" FROM "books"
> LEFT JOIN "langs" AS "original_langs" ON ("original_langs"."id" =
> "books"."langs") LEFT JOIN "langs" AS "translated_langs" ON
> ("translated_langs"."id" = "books"."langs")
>
> Sequel version is 4.37.0 and postgres 9.5.3.
>
>
I can't recreate the error. Here's the SQL code I get for your query:
SELECT books.id AS id, original_title, translated_title,
original_langs.lang_name AS original_lang, translated_langs.lang_name AS
translated_lang, type FROM books LEFT JOIN langs AS original_langs ON
(original_langs.id = books.original_lang) LEFT JOIN langs AS
translated_langs ON (translated_langs.id = original_langs.translated_lang)
There is an error in your code, in that the 2nd join uses the wrong key
(original_langs.translated_lang instead of books.translated_lang). You can
fix that:
DB[:books].left_join(:langs___original_langs, { :id => :original_lang })
.left_join(:langs___translated_langs, { :id => :books__translated_lang })
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
