CODEWITHSUNDEEP
ruby-on-railsruby
John
John

Reputation: 275

Mysql2::Error: Unknown column in where clause Rails

I receive an error of 

Mysql2::Error: Unknown column 'requests.access_level_id' in 'where clause': 
SELECT `requests`.* 
  FROM `requests` LEFT OUTER JOIN `users` ON `users`.`id` = `requests`.`from_id` 
  WHERE `requests`.`access_level_id` = 1 
  ORDER BY id DESC

Model

class Request < ApplicationRecord
  belongs_to :user, foreign_key: :from_id
end

Controller 

@req = Request.left_outer_joins(:user).where(access_level_id: 1).order('id DESC')

How can I remove requests from the WHERE clause requests.access_level_id = 1? I just want access_level_id = 1 to be in the where statement.

Upvotes: 0

Views: 3602

Answers (2)

Vishal
Vishal

Reputation: 717

Assuming that access_level_id is field for user, you can replace your query with following:

@req = Request.left_outer_joins(:user).where('users.access_level_id = ?', 1).order('id  DESC')

By default the fields in where conditions are considered to be belonging to Request method in your query.

Hope this helps you. Please let me know if you face any issue.

Upvotes: 0

jon snow
jon snow

Reputation: 3072

As you requested, you can add where clause without requests as,

@req = Request.left_outer_joins(:user).where('access_level_id = ?', 1).order('id DESC')

But its good to keep relative aliasing for access_level_id. If its users then please use it like,

@req = Request.left_outer_joins(:user).where(users: { access_level_id: 1 }).order('id DESC')

Upvotes: 2

Related Questions