yellowreign
yellowreign

Reputation: 3638

Rails - Writing a Where Query With an Or Clause

I'm having trouble figuring out how to combine to queries into one with my Rails 5 app (mySQL). I want @activities to return the Activity records that meet the criteria for Query 1 or 2.

Can someone please help me? Thank you!

Query 1

@activities = Activity.where(owner_id: current_user.friend_ids, owner_type: "User")

Query 2

@activities = Activity.where(recipient_id: current_user.id)

Upvotes: 1

Views: 3470

Answers (4)

Akshay Borade
Akshay Borade

Reputation: 2472

Try this ............

@activities =  Activity.where("(owner_id in = ? and owner_type = ?) or recipent_id = ?", current_user.friend_ids,"User",current_user.id )

For Rails 5 :

@activities =  Activity.where(owner_id: current_user.friend_ids, owner_type: "User").or(Activity.where(recipient_id: current_user.id))

Hope this will work for you.

Upvotes: 4

rob
rob

Reputation: 2296

you can also use this way:

# load all ids for owner
activity_ids_by_owner = Activity.where(owner_id: current_user.friend_ids, owner_type: "User").pluck(:id)
# load all ids for recipient
activity_ids_by_recipient = Activity.where(recipient_id: current_user.id).pluck(:id)

# load all activities for founded ids
Activity.where(id: activity_ids_by_owner | activity_ids_by_recipient)

Upvotes: 0

John Hayes-Reed
John Hayes-Reed

Reputation: 1438

using AREL is a great way to get OR or AND queries going, its a more programatic way of getting the SQL queries you want as you can build them up and meta program them in easily, and it avoids using direct SQL strings which can get too big and complex and unreadable, but here is a snippet for your code to get you going:

table = Activity.arel_table
query = arel_table['owner_id'].in(current_user.friend_ids).and(arel_table['owner_type'].eq("User"))
query = query.or(arel_table['recipient_id'].eq(current_user.id))
@activities = Activity.where(query)

I strongly recommend looking up more about using AREL, here are some pages to see more:

definitive guide to arel
using arel to compose sql queries

EDIT

For some reason I decided to completely ignore the fact that you are runnin rails 5, in which case Akshay's answer / Chakreshwar Sharma's second answer is definately the way to go.

However I still recommend learning about and getting to grips with AREL is it can really help out in a lot of other cases where you might have more complex queries to write!

Upvotes: 3

Chakreshwar Sharma
Chakreshwar Sharma

Reputation: 2610

Try the below

@activities =  Activity.where("(owner_id in = ? and owner_type = ?) or recipent_id = ?", current_user.friend_ids,"User",current_user.id )

or

@activities =  Activity.where(owner_id: current_user.friend_ids, owner_type: "User").or(Activity.where(recipient_id: current_user.id))

Upvotes: 3

Related Questions