CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecordruby-on-rails-5
jl118
jl118

Reputation: 331

Active record check if attribute is in an array based on another attribute

In my Ruby on Rails project I have a Message model which has attribute direction as either 'incoming' or 'outgoing'. Another attribute for Message is body which represents the actual content of the message. I'm using rails 5.1.2.

Now, I want to select messages while excluding incoming messages whose body is in an array, say ['a','b','c'].

For example, if I have the following messages in my database: 

{direction: 'outgoing', body: 'a'}
{direction: 'outgoing', body: 'b'}
{direction: 'outgoing', body: 'd'}
{direction: 'incoming', body: 'd'}
{direction: 'incoming', body: 'c'}

I want to only select the messages except the last one, i.e. {direction: 'incoming', body: 'c'}

I've tried

Message.where.not(["direction = ? and body != ?","incoming","['a','b','c']"]) This only returned me outgoing messages but the incoming message with body 'd' is missing.

Any suggestions? Thanks.

Upvotes: 0

Views: 410

Answers (2)

Dustin Harmon
Dustin Harmon

Reputation: 91

Instead of != I think you are looking for the IN() syntax for the matches on the body.

   [25] pry(main)> Message.where.not("direction = ? and body in (?)", "incoming", ['a','b','c'])
  Message Load (0.3ms)  SELECT "messages".* FROM "messages" WHERE (NOT (direction = 'incoming' and body in ('a','b','c')))
=> [#<Message:0x007fee8b29a690 id: 1, direction: "outgoing", body: "a", created_at: Thu, 20 Sep 2018 17:02:35 UTC +00:00, updated_at: Thu, 20 Sep 2018 17:02:35 UTC +00:00>,
 #<Message:0x007fee8b29a3e8 id: 2, direction: "outgoing", body: "b", created_at: Thu, 20 Sep 2018 17:02:35 UTC +00:00, updated_at: Thu, 20 Sep 2018 17:02:35 UTC +00:00>,
 #<Message:0x007fee8b299fb0 id: 3, direction: "outgoing", body: "d", created_at: Thu, 20 Sep 2018 17:02:35 UTC +00:00, updated_at: Thu, 20 Sep 2018 17:02:35 UTC +00:00>,
 #<Message:0x007fee8b299d58 id: 4, direction: "incoming", body: "d", created_at: Thu, 20 Sep 2018 17:02:35 UTC +00:00, updated_at: Thu, 20 Sep 2018 17:02:35 UTC +00:00>]

Upvotes: 1

Rohan
Rohan

Reputation: 2737

As per the description mentioned in the post, you want to reject elements using sql on an array object.

For this modify the query to accept array as an argument instead of string as mentioned in the post.

Message.where.not("direction = ? and body IN (?)","incoming",['a','b','c'])

Upvotes: 1

Related Questions