How do I force a Rails query to return potentially two models per result?

Question

I'm using Rails 5. I have this in my controller model for loading a certain model subject to criteria ...

  @results = MyObjectTime.joins(:my_object,
                            "LEFT JOIN user_my_object_time_matches on my_object_times.id = user_my_object_time_matches.my_object_time_id #{additional_left_join_clause}")
                     .where( search_criteria.join(' and '), *search_values )
                     .limit(1000) 
                     .order("my_objects.day DESC, my_objects.name")
                     .paginate(:page => params[:page])

My question is, how do I rewrite the above so that the @results array contains both the "MyObjectTime" and any potential "UserMyObjectTimeMatch" it is linked to?

Answer 1

I would recommend to search for an alternative, but with the information that you provided and looking to avoid the potential 10001 queries mentioned in your comment, if you have the has_many 'user_my_object_time_matches' setup you could do:

@results = MyObjectTime.joins(:my_object,
                            "LEFT JOIN user_my_object_time_matches on my_object_times.id = user_my_object_time_matches.my_object_time_id #{additional_left_join_clause}")
        .where( search_criteria.join(' and '), *search_values )
        .limit(1000) 
        .order("my_objects.day DESC, my_objects.name")
        .paginate(:page => params[:page])
        .includes(:user_my_object_time_matches)
        .map{|t| [t, t.user_my_object_time_matches]}

Answer 2

You cannot. At least not using ActiveRecord or Rails' default interface. ActiveRecord query methods are designed in such a way that they will only return the objects of calling Model.

For example, If you query like

MyObjectTime.joins(:time).where(parent_id: 5)

it'll return the objects for MyObjectTime only. However, because of the join, the records from association time are might also be fetched, only not returned. So, you can take advantage of it. Especially when you use includes in place of joins, the associated models are fetched and you can use them via reference of the associating record/object.

Explanation to build a result pair

This can be done easily by creating a hash with required results.

For example, consider a model Mark that has answer_sheet association.

You can fetch the marks with :answer_sheet using includes this way. I'm fetching 20 in the example.

marks = Mark.limit(20).includes(:answer_sheet);

This fetches answer_sheet which can be retrieved via mark, So, build a hash this way

h = {}
marks.each do |mark|
  h[mark.id] = {}
  h[mark.id][:mark] = mark
  h[mark.id][:answer_sheet] = mark.answer_sheet
end

Now, your hash has the mark and answer_sheet object ready via mark.id key.

This will only execute at most two queries at first fetch and the iteration doesn't won't trigger any further queries. In my system the only two required queries are (with using includes)

SELECT  "marks".* FROM "marks" LIMIT 20
  AnswerSheet Load (0.9ms)  SELECT "answer_sheets".* FROM "answer_sheets" WHERE "answer_sheets"."mark_id" IN (877, 3035, 3036, 878, 879, 880, 881, 561, 882, 883, 884, 885, 886, 887, 888, 889, 890, 891, 892, 893)

You can even use the mark object itself as the key. Then the building process become more simple

h = {}
marks.each do |mark|
  h[mark] = mark.answer_sheet
end

Now, whenever you wanted to access the answer_sheet associated with mark, you'll just need to use h[mark] to fetch it.