CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
Alex
Alex

Reputation: 95

Rails .where for array isn't working as expected

I'm looking to find records where @messages.visiblity (array) matches the current_user.id with this simple call:

@messages = Message.where(visibility: [current_user.id])

However it's not returning anything! So frustrating. Here's a sample of a message that should be in that @messages collection:

2.3.3 :007 > Message.last
  Message Load (0.4ms)  SELECT  "messages".* FROM "messages" ORDER BY "messages"."id" DESC LIMIT $1  [["LIMIT", 1]]
 => #<Message id: 35, subject: "hello!", content: "can you see this", user_id: 40, created_at: "2020-08-06 03:22:59", updated_at: "2020-08-06 03:22:59", visibility: ["55", "49"], active: true>

Here the current user.id is 49.

It wont add that message to the @message instance until all user.ids are met, not just the one id that is 49. The record will be added to @messages if I do this:

@messages = Message.where(visibility: ["55", current_user.id]) #DON'T WANT THIS!

But that's ridiculous. Everywhere I'm looking says the first call should work, what am I doing wrong?

Here is my Message schema:

create_table "messages", force: :cascade do |t|
    t.string "subject"
    t.string "content"
    t.bigint "user_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.text "visibility", default: [], array: true

and here is the form that collects the data:

<%= form_with(model: @message) do |form| %>
<%= form.check_box(:visibility, {:multiple => true}, u.id, nil) %> - <%= u.email %>
<% end %>

Upvotes: 1

Views: 209

Answers (3)

Mayank
Mayank

Reputation: 727

You can use this also

Message.where("visibility && array[?]",current_user.id.to_s)

I am casting current_user.id to STRING, because visibility is a STRING ARRAY and it cannot be compared to integer value of ID

You can also specify it as below:

Message.where("visibility && array[?]",[current_user.id.to_s])

Or with Multiple value array as below:

Message.where("visibility && array[?]",['55','49'])

Multiple values are compared as AND operation.

Upvotes: 0

Anand Bait
Anand Bait

Reputation: 371

Following is the way how we can do it in databases irrespective of if database supports Array or not.

There is a pattern how serialized array is stored in database. ["55", "49"] will be stored as "55"\n "49"\n (or 55\n 49\n)

So you can search using LIKE operator

@messages = Message.where("visibility LIKE ?", "% #{current_user.id}\n%")

OR

@messages = Message.where("visibility LIKE ?", '% "#{current_user.id}"\n%')

Note: There is space between % and # in % #{current_user.id}

Upvotes: 0

spike 王建
spike 王建

Reputation: 1714

this should work:

Message.where('visibility @> ARRAY[?]::string[]', ["49"])

or this:

Message.where("'49' = ANY(visibility)")

Upvotes: 1

Related Questions