Rails PostgreSQL checking an array

Question

I have looked heavily into replicating the answer provided here Postgresql: comparing arrays results in "malformed array literal" error However I still receive an error.

I have two tables Locations, and Customers. Locations belongs to customers

addresses = ["1 Mullberry St", "24 Western Ave", "923 Shetland Dr"]    

Customer.joins(:location)
  .where("'#{addresses}'::text[] && locations.street1")
  .pluck(:email)

Below is the SQL and the error that I receive

SELECT "customers"."email"
  FROM "customers"
    INNER JOIN "locations" ON "locations"."customer_id" = "customers"."id"
      WHERE ('{'1 Mullberry St', '24 Western Ave', '923 Shetland 
Dr'}'::text[] && locations.street1)

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "1"

Dave Slutzkin · Accepted Answer · 2017-12-05 20:50:16Z

1

Are you trying to get the emails from customers which have their location.street1 in that list?

If that's the case then the SQL you want to end up with is more like:

SELECT "customers"."email"
FROM "customers" INNER JOIN "locations" ON "locations"."customer_id" = "customers"."id"
WHERE locations.street1 IN ('1 Mullberry St', '24 Western Ave', '923 Shetland Dr')

To do that in Rails should be as simple as:

Customer.joins(:location)
  .where("locations.street1" => addresses)
  .pluck(:email)

answered Dec 5, 2017 at 20:50

Dave Slutzkin

1,62215 silver badges19 bronze badges

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

1 Comment

tnaught Over a year ago

Thank you, I did not realize you could use location.DOT. in that syntax. I switched to the colon rather than the hash rocket.

Collectives™ on Stack Overflow

Rails PostgreSQL checking an array

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related