Make two queries in the same table in Mysql

Asked

Viewed 160 times

0

I have a table in Mysql database where I will make a query. This query needs to be done as follows:

For those who have a plan on the site:

  • Allow searching by: name, category (different table), address, neighborhood (different table), keywords;

For those who have no plan or this with the expired plan:

  • Allow searching by: name;

It is also worth remembering that for both queries should be related to the city selected at the entrance of the site.

It is possible to make a single query that brings me the result I need.

mysql ruby-on-rails select

1 answer

1

Your question has become a little broad, but leaving the principle that you already have this form ready, that the city selected on the site is persisted in memory and that being with the expired plan or is not an information that is already ready to be used, you can do the following:

For those who have valid plan

You can use the Stars of the Active Record to chain a number of conditions:

class MyModel < ApplicationRecord
  scope :by_name, -> (name) { where(name: name) }
  scope :by_category, -> (category) { where(category: category) }
  scope :by_address, -> (address) { where(address: address) }
  scope :by_neighborhood, -> (neighborhood) { where(neighborhood: neighborhood) }
end

Consumption gets something:

# todos com o nome "Vinicius" e bairro "Jardins"
MyModel.by_name("Vinicius").by_name("Jardins")

# todos com endereço "7th Ave" ou bairro "Hell's Kitchen"
MyModel.by_address("7th Ave").or.by_neighborhood("Hell's Kitchen")

The support of the method #or is only available from Rails 5.2.

If you want to do partial searches with LIKE, just adjust the scopes to:

scope :by_name, -> (name) { where("name LIKE ?", "%#{name}%") }

It is the best way to do partial searches in texts with Active Record, avoiding injection of SQL.

For those who have no plan or are defeated

With the scopes created, it is easier to search by name:

MyModel.by_name("Vinicius")

Performance and N+1

To avoid the queries N+1, previously load the associations you want to compare, in your case categoria and bairro, or simply compare with the ID.

With scopes, it will always do a single query, the way it was written. If you want to be sure what is running in the database, you can use the method #to_sql:

MyModel.by_name("Vinicius").to_sql
=> "SELECT * FROM my_model WHERE name = 'Vinicius'"

If you couldn’t reach the level of performance you need, I suggest you leave the Active Record helpers and write SQL:

PaymentDetail.find_by_sql(<<-SQL 
  SELECT * 
  FROM payment_details
  INNER JOIN projects 
          ON projects.id = payment_details.project_id
  ORDER BY payment_details.created_at DESC
SQL)

More complex queries tend to make Ruby code very difficult to read, and sometimes even harmful. This happens in most Orms. In these cases, I choose to write SQL.

Browser other questions tagged mysql ruby-on-rails select

You are not signed in. Login or sign up in order to post.