Ordering nested has_manys

I am trying to accomplish, approximately, the following query (I’m listing the sql query because it seems like the most simple way to communicate what I want). From an instance of FormDefinition, I’d like to get a list of the questions ordered by page_number, then sequence (ie p1-q1, p1-q2,p2-q1, p2-q2). Running the query on the db gets me the info, ordered in the way I want, but how would I accomplish this with rails associations?

select fd.form_name, fd.description as form_desc, fpd.page_name, fdpd.page_number,  fq.item_name, 
        fq.description as question_desc, fpq.sequence as question_seq,
        fqt.data_type, fqt.data_entry_flag
from form_definitions fd
join form_definition_page_definitions fdpd on fdpd.form_definition_id = fd.id
join form_page_definitions fpd on fpd.id = fdpd.form_page_definition_id
join form_page_questions fpq on fpq.form_page_definition_id = fpd.id
join form_questions fq on fq.id = fpq.form_question_id
join form_question_types fqt on fqt.id = fq.item_type_id
where form_name like '10-Day.Standard'
order by fd.form_name, fdpd.page_number, fpq.sequence

My models are setup with the following associations:

  has_many :form_definition_page_definitions, ->{order :page_number }
  has_many :form_page_definitions,  through: :form_definition_page_definitions
  has_many :form_questions, through: form_page_definitions

FormDefinitionPageDefinition  #has form_definition_id, form_page_definition_id, page_number
  belongs_to :form_definition
  belongs_to :form_page_definition

  has_many :form_page_questions, ->{order :sequence}
  has_many :form_questions, through: :form_page_questions

FormPageQuestions  #has form_page_definition_id, form_question_id, sequence
  belongs_to :form_question
  belongs_to :form_page_definition

  #no defined relations

Hi Hjw,

To help you a bit better, can you provide a plain English description of your queries instead of the SQL?

Perhaps you could simplify your model names to Page, and Question, the way you’ve chosen to model and name the data feels a little awkward. Normally with Rails, you don’t need class names with more than one word, are you used to writing Java or another statically typed language perhaps? The Inflector is designed for a domain model which can be communicated in in simple language, e.g. “each page has many questions”. Thus Page becomes pages in SQL, and Question becomes question.