sql - How to use ActiveRecord for multiply join same table (ruby on rails) -

my application learning words reading sentences , translation of these sentences.

each word has , belongs many sentences , each sentence has translated sentences ('links' table) not translations specific language (many english sentences translated japanese not have translation russian)

i need words (english example) have sentences (english) have translated sentences (russian)

db:

       words                 sentences_words            sentences              links(sentences_sentences) _______________________    _____________________    __________________     _____________________________ |id |lang   |word     |    |word_id|sentence_id|    |id|lang|sentence|     |sentence_1_id|sentence_2_id|    |1  |rus    |Ё        |    |   1   |     1     |    |1 |rus |  ЁЖ    |     |      1      |      5      |  |2  |rus    |Ж        |    |   1   |     4     |    |2 |rus |  ЗЖ    |     |      1      |      8      |  |3  |rus    |З        |    |   2   |     1     |    |3 |rus |  ЙЫ    |     |      2      |      6      |  |4  |rus    |Й        |    |   2   |     2     |    |4 |rus |  ЁЗ    |     |      3      |      7      |  |5  |rus    |Ы        |    |   3   |     2     |    |5 |eng |  ab    |     |      3      |      10     |  |6  |eng    |a        |    |   3   |     4     |    |6 |eng |  bc    |     |             |             |  |7  |eng    |b        |    |   4   |     3     |    |7 |eng |  ca    |     |             |             |  |8  |eng    |c        |    |   5   |     3     |    |8 |jpn |        |     |             |             |  |9  |jpn    | ...     |    |   6   |     5     |    |9 |jpn |        |     |             |             |  |10 |jpn    | ...     |    |   6   |     7     |    |10|jpn |        |     |             |             |  |   |       |         |    |   7   |     5     |    |11|jpn |        |     |             |             |  |   |       |         |    |   7   |     6     |    |12|jpn |        |     |             |             |  |   |       |         |    |   8   |     6     |    |13|jpn |        |     |             |             |  |   |       |         |    |   8   |     7     |    |14|jpn |        |     |             |             |  

models:

class word < applicationrecord   has_and_belongs_to_many :sentences end  class sentence < applicationrecord   has_and_belongs_to_many :words   has_and_belongs_to_many :translations,                           class_name: "sentence",                           join_table: "links",                           foreign_key: "sentence_1_id",                           association_foreign_key: "sentence_2_id" end 

this sql works nice need activerecord query:

sql = "     select w.word words w     join sentences_words sw on sw.word_id = w.id     join sentences s1 on sw.sentence_id = s1.id     join links l on l.sentence_1_id = s1.id     join sentences s2 on l.sentence_2_id = s2.id     w.language = 'eng'     , s1.language = 'eng'     , s2.language = 'rus'     group w.id     order w.id"  @words = activerecord::base.connection.execute(sql) 

upd:

this code works:

@words = word.joins("inner join sentences_words sw on sw.word_id = words.id                      inner join sentences s1 on sw.sentence_id = s1.id                      inner join links l on l.sentence_1_id = s1.id                      inner join sentences s2 on l.sentence_2_id = s2.id                      words.language = 'eng'                      , s1.language = 'eng'                      , s2.language = 'rus'").group(:id).order(:id) 

is possible that? (its not works):

@words = word.where(sentence.where(language: 'eng').joins(:sentences). where(sentences: {language: 'rus'})).where(language: 'eng') 

or

@words = word.joins(:sentences).joins(:translations). where(words: {language: 'eng'}, sentences: {language: 'eng'}, translations: {language: 'rus'} 

thank you!

hi @dmitry can use nested join . here link [http://guides.rubyonrails.org/active_record_querying.html#joins]