CODEWITHSUNDEEP
ruby-on-railsruby
d3bug3r
d3bug3r

Reputation: 2586

Ruby on Rails array not returning results

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

Upvotes: 0

Views: 52

Answers (2)

Emu
Emu

Reputation: 5895

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. :)

Upvotes: 2

born4new
born4new

Reputation: 1687

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 :).

Upvotes: 0

Related Questions