Предложение Rails - JOIN не включает строку с нулевым внешним ключом

Допустим, у меня есть эти данные (обратите внимание на нуль author_id в третьей книге):

     Authors
-------------------------
  id  |  name
-------------------------
  1   |  Susan Collins
  2   |  Steven Meyer
-------------------------

   Books
--------------------------------------
 id  |  author_id  |  title
--------------------------------------
 1   |      1      |  Hunger Games
 2   |      2      |  Twilight
 3   |     nil     |  Games of Throne
--------------------------------------

У меня есть панель поиска для книг. Я хочу, чтобы его можно было найти по названию книги и имени автора.

Например, когда я ищу «Сьюзан». Он будет возвращать книги с названием «Сьюзан» или автором по имени, например «Сьюзен».

Проблема в том: когда я ищу "Игра", я получаю только "Голодные игры". Он не выбирает «Игру престолов», потому что author_id равно нулю.

Я сделал SQLFiddle здесь.

Это SQL, сгенерированный из моего кода Rails:

SELECT books.* FROM books
  INNER JOIN authors ON authors.id = books.author_id
  WHERE (
    ( LOWER(books.title) LIKE LOWER("%game%") )
    OR
    ( LOWER(authors.name) LIKE LOWER("%game%") )
)

И результат возвращает только 1 строку: "Голодные игры". Никакой «Игры престолов».

Есть ли способ включить строку с нулевым внешним ключом?

Я приму SQL-запрос в качестве ответа, я попытаюсь сам разобраться в коде Rails.

Спасибо

[РЕДАКТИРОВАТЬ]

Вот код Rails, который генерирует SQL: (я использую гем Squeel)

search = "%#{params[:search]}%" # the query is taken from param

Books.joins(:author).where {
  ( lower(title) =~ lower(search) ) |
  ( lower(author.name) =~ lower(search) )
}

mysql ruby-on-rails squeel
person hrsetyono    schedule 04.11.2014    source источник
comment
Вы уверены, что используете сортировку с учетом регистра? И как должен выглядеть набор результатов MySQL?   -  person Strawberry    schedule 04.11.2014
comment
да, я уже конвертирую регистр с помощью LOWER, так что регистр не имеет значения. Результат SQL просто возвращает «Голодные игры». добавлю в редакцию   -  person hrsetyono    schedule 04.11.2014

Ответы (2)


arrow_upward
6
arrow_downward

Вам нужен LEFT JOIN вместо INNER JOIN : http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Замените left на inner в Sql Fiddle, и вы получите книги с авторами и без них.

В рельсах вы можете иметь левое соединение, заменив joins на includes. Поскольку он хранит данные из первой таблицы без отношений, его часто используют, когда вы хотите быстро загрузить отношения, чтобы предотвратить проблему запроса N+1.

person Syl    schedule 04.11.2014
comment
Вау, спасибо! никогда раньше не изучал SQL должным образом. Так что я действительно не знаю об этом соединении LEFT RIGHT INNER. - person hrsetyono; 04.11.2014
comment
Я думаю, что этот конкретный ресурс довольно не одобряется внутри SO (meta.stackexchange.com/questions/87678/) - person Strawberry; 04.11.2014
comment
Изменил его на единственную другую английскую ссылку, которую я смог найти. Я думаю, это должно быть в порядке на SO ^^ blog.codinghorror. com/a-visual-explanation-of-sql-joins - person Syl; 04.11.2014

arrow_upward
0
arrow_downward

Может быть, это то, что вы после ...

SELECT b.* 
  FROM books b
  LEFT
  JOIN authors a
    ON a.id = b.author_id
   AND a.name LIKE '%game%'
 WHERE b.title LIKE '%game%';
person Strawberry    schedule 04.11.2014
comment
Спасибо за ответ! К сожалению, я должен выбрать ответ @Syl, потому что он/она ответил на него первым. - person hrsetyono; 04.11.2014