Clever Geek Handbook

How do I query with lower value and associations in Rails 4?

I have an association, I want to query the value of the association field. For example. "Get all associations where Role.name is" admin ". I also want to write down the value of Role.name.

The relationship is for the item has_many :roles

and the other side is belongs_to

. Here's what I have:

  • Works, but not lowercase: .where("roles.name":"Admin")

  • Errors, Even Without Lowercase: .where("roles.name = ?","Admin")

    Generated SQLWHERE (roles.name = 'admin')

  • Failed, no errors or lowercase letters: .where('"roles.name" = ?',"Admin")

    Generated SQLWHERE ("roles.name" = 'admin')

I really want to say .where('lower("roles.name") = ?',"Admin")

, but how do I do it?

The error is usually one of the SQLException: no such column: roles.name

.

As requested schema.rb

:

  create_table "roles", force: :cascade do |t|
    t.string   "name"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  add_index "roles", ["name"], name: "index_roles_on_name", unique: true
+3


source to share


1 answer


If you only want to work with the role model, just try:

where('lower(name) = ?', 'admin')


If you want to do it from another model (like User), try in the User model:

User.joins(:roles).where('lower(roles.name) = ?', 'admin')
+1


source






More articles:


All Articles