CODEWITHSUNDEEP
mysqlruby-on-railsactiverecord
lafeber
lafeber

Reputation: 2770

Use FIELD in ActiveRecord association

I want to show posts that are tagged with the current locale first. I thought that the MySQL FIELD function would be a nice way to solve this.

However, 

Post.includes(:tags).order("FIELD(tags.name, '#{I18n.locale}') DESC").first

results in 

Mysql2::Error: Unknown column 'tags.name' in 'order clause': SELECT  spree_posts`.* FROM 'spree_posts' ORDER BY FIELD(tags.name, 'en') DESC LIMIT 1

If I try

Post.includes(:tags).order("FIELD(tags.name, '#{I18n.locale}') DESC").where.not(tags: { name: 'WTF?' } )

it returns the posts according to the FIELD method correctly, but does a join with tags, hence not returning all posts without tags :(

I think this might be a bug in ActiveRecord? It's definitely not the behaviour I expect.

Upvotes: 1

Views: 40

Answers (2)

Matijs van Zuijlen
Matijs van Zuijlen

Reputation: 468

If you want to use includes and ensure Rails loads the table in the main query, you should use references:

Post.includes(:tags).references(:tags).
  order("FIELD(tags.name, '#{I18n.locale}') DESC").first

Upvotes: 1

Jeiwan
Jeiwan

Reputation: 954

SELECT spree_posts`.* FROM 'spree_posts' ORDER BY FIELD(tags.name, 'en') DESC LIMIT 1

There is no tags table in this query, the table is not loaded, because includes is a smart method, that generates JOIN only when included table is used in WHERE clause.

You need to explicitly define LEFT JOIN:

Post.joins('LEFT JOIN tags ON tags.post_id = post.id').order("FIELD(tags.name, '#{I18n.locale}') DESC").first

(by default joins generates INNER JOIN)

Upvotes: 1

Related Questions