nested scaffold for has_many through

[moracca]

Scenario:

class Account < ApplicationRecord
  has_many :survey_results
  has_many :survey_answers, through: :survey_results
end

This works ok for getting all survey answers that belong to an account, with account.survey_answers returning the correct data, however when I add this relation to a scaffold in order to render the survey answers scaffold inline, AS ends up generating a query that does not include the intermediate table (survey_results) in the join, and does not contain the condition checking that survey_results.account_id = X.

The actual query is something like this (survey_questions is a column in the survey_answers scaffold, so it is preloading that information):

SELECT          "survey_answers"."id"                 AS t0_r0,
                "survey_answers"."survey_question_id" AS t0_r1,
                "survey_answers"."survey_result_id"   AS t0_r2,
                "survey_answers"."answer"             AS t0_r3,
                "survey_answers"."created_by"         AS t0_r4,
                "survey_answers"."updated_by"         AS t0_r5,
                "survey_answers"."created_at"         AS t0_r6,
                "survey_answers"."updated_at"         AS t0_r7,
                "survey_answers"."scratch"            AS t0_r8,
                "survey_answers"."note"               AS t0_r9,
                "survey_questions"."id"               AS t1_r0,
                "survey_questions"."question"         AS t1_r1,
                "survey_questions"."note"             AS t1_r2,
                "survey_questions"."created_by"       AS t1_r3,
                "survey_questions"."updated_by"       AS t1_r4,
                "survey_questions"."created_at"       AS t1_r5,
                "survey_questions"."updated_at"       AS t1_r6,
                "survey_questions"."scratch"          AS t1_r7,
                "survey_questions"."question_type"    AS t1_r8,
                "survey_questions"."required"         AS t1_r9,
                "survey_questions"."position"         AS t1_r10
FROM            "survey_answers"
LEFT OUTER JOIN "survey_questions"
ON              "survey_questions"."id" = "survey_answers"."survey_question_id"
WHERE           ("survey_answers"."created_at" BETWEEN '2022-08-30 18:31:10.209703' AND '2022-11-30 19:31:10.209703')
ORDER BY        "survey_answers"."created_at" DESC,
                survey_questions.question ASC,
                "survey_answers"."id" ASC limit $1 offset $2 [["LIMIT", 15],

This causes all survey answers to be shown instead of those for the account itself. Is there any way to force it to include the intermediate table in the join and include the correct where conditions?

Thanks!

[scambra]

Please, post the parameters of the request

[moracca]

2022-12-01T11:40:56-08:00 INFO 51132: Processing by Admin::Scaffolds::SurveyAnswersController#index as JS
2022-12-01T11:40:56-08:00 INFO 51132:   Parameters: {"account_id"=>"178", "association"=>"survey_answers", "parent_scaffold"=>"admin/scaffolds/accounts", "adapter"=>"_list_inline_adapter"}

[scambra]

I will try to reproduce it. I think ActivScaffold will use a code like this:

Account.find(178).survey_answers.includes(:survey_questions).order('"survey_answers"."created_at" DESC, survey_questions.question ASC, "survey_answers"."id" ASC')

Does that code use the right query?

[moracca]

no, because it is not including the survey_questions table in the query as a join, but rather includes:

 SELECT "survey_answers".* FROM "survey_answers" INNER JOIN "survey_results" ON "survey_answers"."survey_result_id" = "survey_results"."id" WHERE "survey_results"."account_id" = $1 ORDER BY "survey_answers"."created_at" DESC, survey_questions.question ASC, "survey_answers"."id" ASC LIMIT $2  [["account_id", 178], ["LIMIT", 1]]

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "survey_questions"
LINE 1: ...= $1 ORDER BY "survey_answers"."created_at" DESC, survey_que...
                                                            ^

[scambra]

I see the query from the log has a condition for created_at. How did you do it? Did you override beginning_of_chain in your controller? You should call super because is the method which will load associated records with account_id

[moracca]

I am not 100% sure what is adding that condition. I don't have beginning_of_chain overridden here, but there must be some other way that results were being filtered to the past 3 months. I looked briefly but the only thing I could find was a field_search.default_params, but I tried commenting that out and it was still including those conditions in the query.