1

I have word's senses and translations in different languages, here is schema http://sqlfiddle.com/#!15/cba5e/2 I need to get all senses and only Spain translations (code "es"), so query should looks like 

SELECT *
FROM senses s 
LEFT OUTER JOIN translations t ON
    s.id = t.sense_id AND t.language_code = 'es';

Can I do it with eager_load/includes/..?

Code like 

Sense.includes(:translations).where(translations: { language_code: 'es' })

doesn't work, because it specify conditions in the WHERE clause and generate query like 

SELECT *
FROM senses s 
LEFT OUTER JOIN translations t ON s.id = t.sense_id
WHERE t.language_code = 'es';

and I get only senses with "es" translations. But I need all senses + spain translations, see the difference: http://sqlfiddle.com/#!15/cba5e/2 and http://sqlfiddle.com/#!15/cba5e/3

From https://www.postgresql.org/docs/current/static/queries-table-expressions.html: Restriction placed in the ON clause is processed before the join, while a restriction placed in the WHERE clause is processed after the join

alexfv
  • 1,826
  • 2
  • 19
  • 22

2 Answers2

1

Found a way to fulfill the eager loading with left outer join. it is done by using association with condition.

Sense (model)
has_many es_translations -> { where(language_code: 'es') },
           :class_name => "Translations",
           :foreign_key => "foreign_key_column"

# references is used to ensure eager load
Sense.includes(:es_translations).references(:es_translations)

with this it will create a left outer join with condition on the ON query.

bLaXjack
  • 780
  • 2
  • 11
  • 23
0

This will do 

Sense.includes(:translations).where('translations.language_code = ?', 'es')

includes does LEFT OUTER JOIN

Deepak Mahakale
  • 22,834
  • 10
  • 68
  • 88
  • well i was writing the answer and was doing some other work that doesn't mean you will downvote it – Deepak Mahakale Jul 28 '16 at 13:52
  • As this is a duplicate and later answer, there is no use for it other than taking place on this page. It should be clearly marked as such and downvote is the way to do that. Everyone can post a duplicate answer but rather than keeping it, one should upvote the earlier answer instead. – Nic Nilov Jul 28 '16 at 13:55
  • As I just said I was answering and simultaneously doing my work. How am I supposed to know someone is already posting the same answer – Deepak Mahakale Jul 28 '16 at 14:01
  • By clicking on the `1 new answer has been posted` banner before posting yours, of course. – Nic Nilov Jul 28 '16 at 14:03
  • @Deepak and that's why I put in the title of the post phrase "...conditions in the ON clause" ;-) – alexfv Jul 28 '16 at 14:30
  • @faost `es` is fixed? or it can be dynamic? – Deepak Mahakale Jul 28 '16 at 14:42