Ruby on Rails array not returning results

Question

Following were my query in controller:

@cref = Creference.where("lower(name) LIKE ?", "#{@city.downcase}")

  if @cref.present?
    cities_array = @cref.map {|con| "%#{con.countries}%" } 
    #cities_array return --> ["%["Kuching", "Kota Kinabalu"]%"]
    @products.where("city ILIKE ANY ( array[?] )", cities_array) --> []
end

The product doesn't return any result despite there is city with above name in Product table. Thanks!!

SCHEMA

product:

create_table "products", force: :cascade do |t|
    t.integer  "user_id"
    t.string   "name"
    t.integer  "payment_type",        limit: 2
    t.datetime "created_at",                    precision: 6,             null: false
    t.datetime "updated_at",                    precision: 6,             null: false
    t.integer  "product_category_id"
    t.integer  "location_id"
    t.text     "description"
    t.text     "highlight"
    t.integer  "price_cents"
    t.integer  "zip"
    t.string   "country"
    t.string   "state"
    t.string   "city"
    t.string   "address"
    t.string   "apt"
    t.integer  "refund_day"
    t.string   "currency"
    t.integer  "refund_percent"
    t.integer  "refundable",          limit: 2,               default: 0
    t.integer  "step",                limit: 2,               default: 0
    t.integer  "discount",                                    default: 0
    t.string   "slug"
    t.integer  "status",              limit: 2,               default: 1
    t.integer  "verification",        limit: 2,               default: 0
  end

creference:

  create_table "creferences", force: :cascade do |t|
    t.string   "name"
    t.string   "countries",  default: [],              array: true
    t.datetime "created_at",              null: false
    t.datetime "updated_at",              null: false
  end

Maybe @products is empty or nil try Product.where("city ILIKE ANY ( array[?] )", cities_array) --> [] it should return products if matched any. — Muhammad Ali
– Muhammad Ali, Commented Apr 6, 2016 at 6:30
could you post the schema for Creference and Product model? — Emu
– Emu, Commented Apr 6, 2016 at 6:54

Emu · Accepted Answer · 2016-04-06 07:13:02Z

2

You can do something like that:

@cref = Creference.where("lower(name) LIKE ?", "#{@city.downcase}")

  if @cref.present?
    cities_array = @cref.collect {|con| con.countries }.flatten 
    #cities_array will return --> ["Kuching", "Kota Kinabalu"]
    @products.where("city IN (?)", cities_array) 
    # the IN query will find the city names that are in the cities_array
    # but in that case your product tables city name should be in lowercase
    # as you are fetching lowercase city names from Creference
end

N.B. I've not tested the code above. But it should work. :)

answered Apr 6, 2016 at 7:13

Emu

5,9253 gold badges36 silver badges53 bronze badges

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

Comments

born4new · Accepted Answer · 2016-04-06 06:45:38Z

0

First, check the value of cities_array to see what is returns.

Second, try to append to_sql at the end of the statement and to print it to see what the sql looks like:

puts @products.where("city ILIKE ANY ( array[?] )", cities_array).to_sql

Last but not least, Product.count to see that you actually have products in your table :).

answered Apr 6, 2016 at 6:45

born4new

1,68712 silver badges12 bronze badges

5 Comments

d3bug3r Over a year ago

This is the query i get

SELECT "products".* FROM "products" INNER JOIN "users" ON "users"."id" = "products"."user_id" WHERE "products"."step" = 5 AND "products"."status" = 1 AND ("users"."merchant_status" = 1 OR "users"."merchant_status" = 0 AND "products"."verification" = 1) AND (city ILIKE ANY ( array['%["Kuching", "Kota Kinabalu"]%'] ))

born4new Over a year ago

Is the last part valid SQL? I don't know which DBMS you use, but it looks weird to me. You can take this SQL statement and launch it on your dbms command line, so that you can see if you have a SQL or a Rails problem.

d3bug3r Over a year ago

I am using postgres. And yet, it doesnt return anything

born4new Over a year ago

Then go to your psql CLI and try to launch the query above.

born4new Over a year ago

Which is good, you have a valid SQL query. It just does not do what you want...Try to refactor your AR query to make it do what you need (maybe check Emu's answer below).

Collectives™ on Stack Overflow

Ruby on Rails array not returning results

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related