CODEWITHSUNDEEP
ruby-on-rails
adamvanbart
adamvanbart

Reputation: 67

Rails index action with a WHERE clause

I am trying learn Rails so I know there are some simple concepts I could be missing.

I have a list of staff, each belonging to a given project. Users who log into the system should only view/manage staff belonging to their project (users are also attached to a given project). This means I have project_id in both User and Staff models.

I want current_user to only see records from his/her own project. I have implemented the authentication system in Michael Hartl's book, so I have a current_user method already defined and working.

Here is my index action:

def index
@staffs = Staff.where("current_user.project_id = ?", 'staff.project_id') 
end

However, I get an error:

no such column: current_user.project_id

I am out of ideas. How can I implement this?

Upvotes: 0

Views: 903

Answers (2)

Jonathan Bennett
Jonathan Bennett

Reputation: 1065

You would want to do:

def index
    @staffs = Staff.where(project_id: current_user.project_id)
end

This will execute the sql code select … from staff where staff.project_id = 123;, assuming the value of current_user.project_id is 123. This is happening because the where method takes care of mapping the key you give (project_id) to the value (the value of current_user.project_id).

The sql that would come from your where method would be select … from staff where current_user.project_id = "staff.project_id";. The database column should be the first part of the where method, and the value to be the second part.

You could add this to you base controller (ApplicationController) to make it available everywhere, something like:

class ApplicationController < ActionController::Base
    before_action :current_staff

    def index
    end

    def current_staff
        @staffs = Staff.where(project_id: current_user.project_id) if current_user # make sure user is signed in
    end
end

If you want to use this in a select you could add a helper method like this:

module ApplicationHelper
    def options_for_staff_projects_select(selected = nil)
        project_ids = @staffs.collect(:project_id) # get list of project ids from staff
        options = Project.where(id: project_ids).map { |x| [x.name, x.id] }

        options_for_select options, selected
    end
end

# in your form call:
<%= f.select :project_id, options_for_staff_projects_select(model.project_id), include_blank: 'Choose Project' %>

Upvotes: 2

kishore cheruku
kishore cheruku

Reputation: 521

Staff.where("current_user.project_id = ?", 'staff.project_id')

here current_user.project_id is not a column for Staff model. In active record where is expecting the first argument to be the column name of given model. So here model is Staff and column name you have to check is project_id and conditional value is current_user.project_id So you have to use condition like below.

Staff.where("project_id = ?", current_user.project_id)

Upvotes: 1

Related Questions