CODEWITHSUNDEEP
ruby-on-rails
Mark Denn
Mark Denn

Reputation: 2304

How to setup a search query

I am building a Rails 5 app and in this app I let my users add a search profile for apartments that they are looking for.

They can add the following to their User object:

So for example a user can be looking for an apartment that is at minimum (min_size) 100 ft2 and at maximum (max_size) 200 ft2. They only want to pay a minimum (min_price) of 500 dollar per month and a maximum (max_price) of 1200 dollars per month and the apartment should have a minimum (rooms) of 3 rooms.

I am getting information about apartments from a remote database periodically and at each time I want to check if there are any search profiles matching the apartment data.

How can I create a SQL where query checking this?

This is what I am trying as a class method on the User object.

def self.match(size, price, rooms)
  User.where("size_min >= ? AND size_max <= ? AND price_min >= ? AND price_max <= ? AND rooms == ?", size, size, price, price, rooms)
end

The query that is run is then

User Load (0.2ms)  SELECT  "users".* FROM "users" WHERE (size_min >= 100 AND size_max <= 100 AND price_min >= 600 AND price_max <= 600 AND rooms == 3) LIMIT ?  [["LIMIT", 11]]

But this does not look alright. I have a sample User object below that I want to match.

<User id: 12, provider: "email", allow_password_change: false, firstname: "John", lastname: "Doe", email: "[email protected]", phone: "32323", area: "San Antonio", rooms: 3, size_min: 100, size_max: 200, price_min: 500, price_max: 1200, is_admin: true, created_at: "2019-09-26 15:43:15", updated_at: "2019-09-26 22:06:34">

The object info for the apartment is not ranges but absolute data:

[{"rooms":"3","size":"80","price":"7315"}]

Upvotes: 0

Views: 27

Answers (1)

Sujan Adiga
Sujan Adiga

Reputation: 1371

The query you're writing seems to have incorrect inequality checks, try the following one instead,

def self.match(size, price, rooms)
  User.where("(? BETWEEN size_min AND size_max) AND (? BETWEEN price_min AND price_max) AND rooms = ?", size, price, rooms)
end

Ref: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_between

Upvotes: 1

Related Questions