Make two queries in one table in MySql

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 search by: name, category (different table), address, neighborhood (different table), keywords;

For those who do not have a plan or who have a past due plan:

  • Allow search by: name;

It is also worth remembering that for both queries should be related to the city selected in the site entry.

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

    
asked by anonymous 15.06.2018 / 02:43

1 answer

1

Your question has been somewhat broad, but assuming you already have this form ready, that the city selected on the site is in memory and that the plan is overdue or is not information that is already ready for be used, you can do the following:

For those with a valid plan

You can use Active Record scopes 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

The 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")
  

#or method support is only available from Rails 5.2.

If you want to partial searches with LIKE , then only 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 SQL Injection .

For those who do not have a plan or are past due

With scopes created, it's simpler to search by name:

MyModel.by_name("Vinicius")

Performance and N + 1

To avoid N + 1 queries , first load the associations that are to compare, in your case categoria and bairro , or simply compare with the ID.

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

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

If you were not able to achieve the level of performance you need, I suggest leaving the Active Record helpers and type 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.

    
15.06.2018 / 04:46