Блог

Главная - Вопросы по программированию - Предложение Rails Raw SQL WHERE

Предложение Rails Raw SQL WHERE

#sql #ruby-on-rails #ruby #postgresql

#sql #ruby-на-рельсах #рубин #postgresql #ruby-on-rails #ruby

Вопрос:

Я создаю необработанные SQL-запросы в свою базу данных PG в Rails. Когда я использую только одно условие даты в предложении WHERE, работает отлично, но когда я использую 2 условия даты, это работает не так, как ожидалось. Чтобы просмотреть результаты, я выполняю для каждого из них через объект results и печатаю его на консоли.

Только с одним условием:

 connection = ActiveRecord::Base.connection()
results = connection.execute("SELECT * 
                              FROM training_employees 
                              LEFT JOIN trainings 
                              ON training_employees.training_id = trainings.id 
                              WHERE trainings.date >='2014-06-29'")
results.each do |row|
  puts row
end

ВОЗВРАТ

 {"id"=>"1", "employee_id"=>"2", "training_id"=>"1", 
 "created_at"=>"2014-06-29 06:44:13.261074", 
 "updated_at"=>"2014-06-29 06:44:13.261074", 
 "name"=>"Incendios", "date"=>"2014-06-30 02:43:00", 
 "expected_attendance"=>"20", "service_company_id"=>"1"}
{"id"=>"1", "employee_id"=>"3", "training_id"=>"1", 
 "created_at"=>"2014-06-29 06:44:13.261074", 
 "updated_at"=>"2014-06-29 06:44:13.261074", 
 "name"=>"Incendios", 
 "date"=>"2014-06-30 02:43:00", 
 "expected_attendance"=>"20", "service_company_id"=>"1"}
{"id"=>"1", "employee_id"=>"7", "training_id"=>"1", 
 "created_at"=>"2014-06-29 06:44:13.261074", 
 "updated_at"=>"2014-06-29 06:44:13.261074", 
 "name"=>"Incendios", "date"=>"2014-06-30 02:43:00", 
 "expected_attendance"=>"20", "service_company_id"=>"1"}
{"id"=>"1", "employee_id"=>"9", "training_id"=>"1", 
 "created_at"=>"2014-06-29 06:44:13.261074", 
 "updated_at"=>"2014-06-29 06:44:13.261074", 
 "name"=>"Incendios", "date"=>"2014-06-30 02:43:00", 
 "expected_attendance"=>"20", "service_company_id"=>"1"}
{"id"=>"1", "employee_id"=>"10", "training_id"=>"1", 
 "created_at"=>"2014-06-29 06:44:13.261074", 
 "updated_at"=>"2014-06-29 06:44:13.261074", 
 "name"=>"Incendios", "date"=>"2014-06-30 02:43:00", 
 "expected_attendance"=>"20", "service_company_id"=>"1"}
=> #<PG::Result:0x007fa8b9cfc038 
@connection=#<PG::Connection:0x007fa8b994fcf0 
              @socket_io=nil, @notice_receiver=nil, 
              @notice_processor=nil>>

Когда я выполняю запрос с двумя условиями

 results = connection.execute("SELECT * 
                              FROM training_employees 
                              LEFT JOIN trainings 
                              ON training_employees.training_id = trainings.id 
                              WHERE trainings.date >='2014-06-29' 
                              AND trainings.date <='2014-06-30'")
results.each do |row|
  puts row
end

Возвращайте только это, а не список записей, как при одном условии

  => #<PG::Result:0x007fa8b75c5398 
      @connection=#<PG::Connection:0x007fa8b994fcf0 @socket_io=nil, 
                    @notice_receiver=nil, @notice_processor=nil>>

Я протестировал условия по отдельности и возвращаю то, что я ожидаю (существующие записи между диапазоном дат)

Это мои модели

 Training(id: integer, name: string, date: datetime, 
         expected_attendance: integer, created_at: datetime, 
         updated_at: datetime, service_company_id: integer) 
TrainingEmployee(id: integer, employee_id: integer, 
                 training_id: integer, created_at: 
                 datetime, updated_at: datetime)

Я использую ruby 2.1.1 и Rails 4.1.0.

Ответ №1:

Ваша проблема в том, что на самом деле вы получаете только полночь 30-го числа. Когда ваши типы будут преобразованы для сравнения, вот как на самом деле выглядит ваш запрос:

 SELECT * 
FROM training_employees 
LEFT JOIN trainings 
       ON training_employees.training_id = trainings.id 
WHERE trainings.date >='2014-06-29 00:00:00.000000' 
      AND trainings.date <='2014-06-30 00:00:00.000000'

…. поэтому, конечно, никакие записи из trainings with date = "2014-06-30 02:43:00" не будут включены. Правильный способ запроса даты / времени / временных меток — использовать эксклюзивную верхнюю границу, то есть trainings.date < '2014-07-01' .

Это также приводит к тому, что записи вообще не возвращаются, потому что на самом деле у вас нет LEFT JOIN , фактически у вас есть INNER JOIN . Вот почему; когда WHERE предложение выполняется, все, что является LEFT JOIN отредактированным и не имеет записей, будет выполнять эти условия null и заставит базу данных исключить их. Если вы действительно хотите LEFT JOIN , вам нужно переместить условия в ON предложение. Если вы действительно хотите INNER JOIN , вам все равно следует переместить их в ON предложение, поскольку это делает соединение более очевидным.

В сторону: что trainings.date на самом деле представляет? Это ужасно двусмысленное название…

Комментарии:

1. тренинги. дата представляет дату, когда предлагается обучение. Спасибо!

2. @rcrivera — тогда оно должно быть названо как offered_On , или offered_At (это более вероятно, если включена информация о времени). Как правило, старайтесь не указывать тип в именах переменных / столбцов. (Есть несколько вещей, которые, кажется , включают эту информацию, но не совсем, например, calendar_date или business_date , где «дата» — это понятие, которого было бы неудобно избегать).

Ответ №2:

Все временные метки в вашем date на самом деле больше, чем 2014-06-30, потому что все пять из них:

 "date"=>"2014-06-30 02:43:00"

Дата 2014-06-30 будет соответствовать временной метке 2014-06-30 00:00:00 , поэтому компоненты времени суток ваших временных меток вмешиваются в логику вашего запроса.

Решение состоит в том, чтобы использовать date столбец для хранения дат, а не временную метку. Тогда у вас не будет никаких проблем с часовым поясом, временем суток ….