Rails .where for array isn't working as expected

Question

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 %>

Why are you using an array instead of an association?

jvillian
– jvillian

2020-08-06 03:49:37 +00:00
Commented Aug 6, 2020 at 3:49 — jvillian
– jvillian, Commented Aug 6, 2020 at 3:49

spike 王建 · Accepted Answer · 2020-08-06 03:49:38Z

1

this should work:

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

or this:

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

answered Aug 6, 2020 at 3:49

spike 王建

1,7647 silver badges16 bronze badges

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

1 Comment

dev-cc Over a year ago

In addition, you can check the rails documentation where they suggest the same that @Spikie's answer

Anand Bait · Accepted Answer · 2020-08-06 12:00:16Z

0

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}

answered Aug 6, 2020 at 12:00

Anand Bait

3711 silver badge13 bronze badges

Comments

Mayank · Accepted Answer · 2020-08-06 12:24:16Z

0

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.

answered Aug 6, 2020 at 12:24

Mayank

7276 silver badges9 bronze badges

Collectives™ on Stack Overflow

Rails .where for array isn't working as expected

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related