Rails where with multiple conditions

Question

In my application I have multiple user roles defined using an enum:

enum role: { staff: 0, clinician: 1, admin: 2 }

Staff users each belong to a university:

Staff Concern:

require 'active_support/concern'

module StaffUser
  extend ActiveSupport::Concern

  included do
    belongs_to :university
    has_many :patients
    has_many :referral_requests
    validates :university_id, presence: true, if: :staff?
  end

University Model

class University < ApplicationRecord
  has_many :staffs, -> { where role: :staff}, class_name: "User"
  has_many :clinicians, through: :lists
  has_many :whitelists
  belongs_to :market

  validates :market_id, presence: true
end

I have a dropdown select menu for Staff Doctor on a patients/new view where I want to display a list of staff users who belong to the same university as the current user, but I can't seem to get it to work. Currently, the dropdown only contains the prompt text. What am I doing wrong?

patients/new view:

<%= form_for(@patient) do |f| %>
  <%= render 'shared/error_messages', object: f.object %>
  <div class="checkbox">

    <h1>Tell us about your patient</h1>


    <h2>Insurance</h2>
    <% Insurance.all.each do |insurance| %>
      <%= check_box_tag "patient[insurance_ids][]", insurance.id, @patient.insurance_ids.include?(insurance.id), id: dom_id(insurance) %>
      <%= label_tag dom_id(insurance), insurance.name %><br>
    <% end %>

    <h2>Presenting Concerns</h2>
    <% Concern.all.each do |concern| %>
      <%= check_box_tag "patient[concern_ids][]", concern.id, @patient.concern_ids.include?(concern.id), id: dom_id(concern) %>
      <%= label_tag dom_id(concern), concern.name %><br>
    <% end %>

    <h2>Staff Doctor</h2>  

       <%= select_tag "patient[staff_doctor_id]", options_from_collection_for_select(User.where("role = ? AND university_id = ?", "staff", @user.university_id), "id", "name"), prompt: "Select this patient's therapist" %>

  </div>

  <%= f.submit "Submit", class: "btn btn-primary" %>
<% end %

Patients Controller:

class PatientsController < ApplicationController
    before_action :require_login

def new
    @user = current_user
    @patient = current_user.patients.build
end

def index
    authorize Patient
    @patients = policy_scope(Patient)
end

def show
    @patient = Patient.find(params[:id])
end

def edit
    @patient = Patient.find(params[:id])
end

def update
    @patients = Patient.all
    @patient = Patient.find(params[:id])
    if @patient.update_attributes(patient_params)
        flash[:success] = "Patient Updated!"
        render 'patients/index'
    else
        render "edit"
    end
end


def create
    @patient = current_user.patients.build(patient_params)
    if @patient.save
        flash[:success] = "Patient Created!"
        redirect_to new_referral_request_path(patient_id: @patient.id)
    else
        Rails.logger.info(@patient.errors.inspect)
        render 'patients/new'
end
end


private

def patient_params
    params.require(:patient).permit(:age, :staff_doctor_id, :user_id, insurance_ids: [], gender_ids: [], concern_ids: [], race_ids: [])

end
end

What do you get when you run User.where("role = ? AND university_id = ?", "staff", @user.university_id) in console, given the same @user? Btw, you'd need to use 0 instead of 'staff' in the query if you're using Rails4 because the filed is integer type, although I believe the string value works in Rails5 now. And does your user have a #name method? — EJAg
– EJAg, Commented Oct 11, 2017 at 19:24
@EJ2015 I don't think Rails 5 will automatically fix a parameterized query to use the enum mapping. Doing where(role: :staff) works though. — max
– max, Commented Oct 11, 2017 at 19:51

max · Accepted Answer · 2017-10-11 19:53:09Z

19

Scopes in ActiveRecord are chainable:

User.staff.where(university: @user.university)

Chaining .where or scopes creates AND clauses. So all the conditions must apply.

Using ActiveRecord::Enum creates scopes for each of the enum states. So this is equivilent to:

User.where(role: :staff, university: @user.university)

When using an ActiveRecord::Enum you need to remember that the database stores integers - not strings:

User.where('role = 0') # staff
User.where('role = ?', User.statuses[:staff])

But there is no need to use a SQL string for this query.

A much better way to create selects and checkboxes is by using the rails collection helpers:

<%= form_for(@patient) do |f| %>
  <%= render 'shared/error_messages', object: f.object %>
  <div class="checkbox">
    <h1>Tell us about your patient</h1>
    <h2>Insurance</h2>
    <%= f.collection_check_boxes(:insurance_ids, Insurance.all, :id, :name) %>

    <h2>Presenting Concerns</h2>
    <%= f.collection_check_boxes(:concern_ids, Concern.all, :id, :name) %>

    <h2>Staff Doctor</h2>  
    <%= f.collection_select(:staff_doctor_id, User.staff.where(university: @user.university), :id, :name, prompt: "Select this patient's therapist") %>
  </div>
  <%= f.submit "Submit", class: "btn btn-primary" %>
<% end %>

Not only is this a lot less code, but binding the inputs to the form builder ensures that they "hold the value" when validations fail.

edited Oct 11, 2017 at 19:53

answered Oct 11, 2017 at 19:37

max

103k15 gold badges113 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

max Over a year ago

Also make sure that you remove user_id from the patient_params method.

mike9182 Over a year ago

Thanks Max! I'm digging in to see if I can figure out why, but changing the form to use the rails collections helpers jumbles all the checkboxes together for some reason.

mike9182 Over a year ago

When i enforce that staff_doctor_id be present with a validation in the model and try to submit the form without selecting it, I get an undefined method error in patients#create for university in the where statement... why would that be?

max Over a year ago

Use css to get the checkboxes to appear the way you want to. Does your User class include the StaffUser module?

mike9182 Over a year ago

Thanks Max - It does

Collectives™ on Stack Overflow

Rails where with multiple conditions

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related