6

How would I write this correctly?

Request.pending.where(.....)  ## Request.pending = request.state_id in (1..3)

where these are the conditions:

approver1_id = current_user and state_id = 1
or
approver2_id = current_user and state_id = 2
or
approver3_id = current_user and state_id = 3

It would be really nice if I could put these conditions in the model for use in other controllers/views, too, because I will use these conditions quite often throughout the app.

Katie M
  • 1,111
  • 6
  • 21
  • 31

3 Answers3

11

Try:

Request.pending.where(
  '(approver1_id= ? AND state_id= ?) OR
   (approver2_id= ? AND state_id= ?) OR
   (approver3_id= ? AND state_id= ?)',
  current_user.id,
  1,
  current_user.id,
  2,
  current_user.id,
  3
)

Edit: I forgot that you should use colons. And shouldn't it be 'current_user.id'? It is also unclear whether your Request use the three parameters approver1_id - approver3_id or just one approver_id per request.

Edit 2: Changed query to SQL.

AWM
  • 1,130
  • 11
  • 23
  • Hm, both of the above answers ignore the current_user condition - it shows requests meeting state_ids 1-3, but ignores the approver1-3. I tried it with current_user and current_user.id. – Katie M Oct 28 '13 at 15:27
  • A REQUEST has three approvers for three states. I am only pulling pending requests with the scope: `Request.pending = state_id of 1-3, which is: (Pending Approval 1, Pending Approval 2, Pending Approval 3).` If the Request is Pending Approval 1 and I am Approver 1, the record needs to show in my list - same if I am Approver 2 and the request is Pending Approval 2... etc. But, if I am Approver 1 and the request is Pending Approval 2, then I don't want to see the record in my list. – Katie M Oct 28 '13 at 15:36
  • @KatieM current user can be only one at a time right? That's why i'm checking for current_user.id only. Maybe you try to improve your DB schema. Otherwise make your question more specific. – Hare Kumar Oct 28 '13 at 15:39
  • A current_user can sometimes be a combination of approver 1, 2 and/or 3, so it has to be an OR condition where the state number matches the approver number - I'm not sure how to change the DB schema to fit these criteria, but I welcome suggestions. Maybe I can put something in the user model that finds my_pending_requests? That's why I'm here.. – Katie M Oct 28 '13 at 15:57
  • OK, I changed the query to SQL. It is not beautiful but it should work. – AWM Oct 28 '13 at 16:15
  • Indeed, that DB schema seems a bit clumsy. Why not define has_many approvers for Request, where an approver-type-id (1,2 or 3) of a User matches the state_id of the Request? – AWM Oct 28 '13 at 16:21
  • The messy SQL works with the clumsy DB schema. :) Thanks for the suggestion; I'm not sure how to define "match approver number to state number", but I will work on that, because that is what I want it to do...that's why I come here! – Katie M Oct 28 '13 at 16:35
8

To answer the second part of your question about reusing this query, you can just define a class method on Request that accepts a user parameter:

# usage: Request.pending_approval(current_user)
def self.pending_approval(user)
  pending.where("(approver1_id = :user AND state_id = 1) OR 
                 (approver2_id = :user AND state_id = 2) OR 
                 (approver3_id = :user AND state_id = 3)", 
                user: user)
end

If you want to be able to reuse the individual fragments of the query and combine them as needed, see this related answer (Note, the answer linked to is better than the accepted one, IMO).

Community
  • 1
  • 1
exbinary
  • 1,086
  • 6
  • 8
1

Well

First get all the state_id & store it in array. and then pass that array in where clause. It is similar to Mysql IN query.

Hence your query will be something like: 

state_id = [1, 2, 3]  
Request.pending.where(:state_id => state_id AND :approved_id => current_user.id)

I hope it will fetch you the desired result.

Hare Kumar
  • 699
  • 7
  • 23