CODEWITHSUNDEEP
ruby-on-railsarrayspostgresql
tnaught
tnaught

Reputation: 301

Rails PostgreSQL checking an array

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"

Upvotes: 0

Views: 41

Answers (1)

Dave Slutzkin
Dave Slutzkin

Reputation: 1622

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)

Upvotes: 1

Related Questions