CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Dan Tappin
Dan Tappin

Reputation: 3032

Order on a associated column

I has Model that has_many ModelItems. ModelItems belongs_to OtherModel which has a 'code' column.

I am looking to do something like this:

Model.find(1).model_items.includes(:other_model).order("other_model.code" :desc)

I am trying to sort based on the text of that related code column.

I have even tried:

ModelItems.where(model_id: 1).includes(:other_model).order("other_model.code" :desc)

I know I need an include or join here but no matter what I do I get a variation of this error:

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "other_model"

UPDATE

This is an example of not using the real model names bites you. I had it right all along.

The include was singular and the order model name needed to be plural - for clarity lets change other_model to widgit:

Model.find(1).model_items.includes(:widgit).order("widgits.code ASC")

Upvotes: 0

Views: 53

Answers (2)

Eyeslandic
Eyeslandic

Reputation: 14910

Add references 

ModelItems
  .where(model_id: 1)
  .includes(:other_model)
  .references(:other_model)
  .order("other_model.code DESC")

Upvotes: 1

engineersmnky
engineersmnky

Reputation: 29598

includes in this context will execute 2 queries to create a pseudo outer join (also preload) and is conceptually as follows 

 SELECT * FROM model_items WHERE model_items.model_id = 1

 SELECT * FROM other_models WHERE other_models.model_item_id IN ( [IDS FROM PREVIOUS QUERY])

You can enforce single query execution in a few ways:

  • eager_load - (ModelItems.eager_load(:other_model)) This is how includes works when you have the join table referenced in a hash finder query condition or when you add references.

  • references - (ModelItems.includes(:other_model).references(:other_model)) This enforces the eager_load path for include

  • where Hash finder method (ModelItems.includes(:other_model).where(other_models: {name: 'ABC'})) Here includes intelligently realizes that you have placed a condition on the relationship with other_model and will automatically create the join so that the query is not malformed. However this sort of query represents as an outer join but performs like an inner join which is less efficient *

However if you do not need the information in other_model and just want to use this as a sort mechanism then you can use joins (INNER JOIN) or left_joins (OUTER JOIN) which will allow you to sort this data but will not retrieve the attributes or instantiate any related objects under the other_model relationship 

 ModelItems.joins(:other_model)
 # OR 
 ModelItems.left_joins(:other_model)

*These options can be combined as well as in the case of the includes where hash finder method I always recommend the following ModelItems.joins(:other_model).includes(:other_model).where(other_models: { name: 'ABC'}) (INNER JOIN). This will return the same data set as ModelItems.includes(:other_model).where(other_models: {name: 'ABC'}) (LEFT OUTER JOIN) however by utilizing an INNER JOIN it becomes more efficient than its LEFT OUTER JOIN version

Sidenote order("other_models.code" :desc) this is not valid. Instead you need to include the order direction in the String or make that String and Symbol e.g. (("other_models.code DESC") or ("other_models.code": :desc))

Upvotes: 1

Related Questions