Rails - JOIN clause doesn't include row with null Foreign Key

Question

Let's say I have this data (notice the null author_id in third book):

     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
--------------------------------------

I have a search bar for Books. I want it to be searchable by Book's title and Author's name.

For example when I search for "Susan". It will return books are titled like "Susan" or authored by name like "Susan".

The problem is: When I search for "Game", it only returns "Hunger Games". It doesn't select "Game of Throne" because the author_id is null.

I made the SQLFiddle here.

This is the SQL generated from my Rails code:

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

And the result only returns 1 row: "Hunger Games". No "Game of Thrones".

Is there a way to include the row with null Foreign Key?

I will accept SQL query as answer, I will try to figure out the Rails code myself.

Thanks

[EDIT]

Here's the Rails code that generate the SQL: (I use Squeel gem)

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

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

Are you sure that you're using a case sensitive collation? And what should the MySQL result set look like? — Strawberry, Nov 04 '14 at 10:12
yes, I already convert the case using `LOWER`, so the case doesn't matter. The SQL Result just returns "Hunger Games". I will add it to the edit — hrsetyono, Nov 04 '14 at 10:13

Syl · Accepted Answer · 2014-11-04T10:24:51.010

7

What you want is a LEFT JOIN instead of an INNER JOIN : http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Replace left by inner in your Sql Fiddle and you will get the books with and without authors.

In rails you can have a left join by replacing joins by includes. Because it keeps data from the first table without relations, it is often used when you want to eager load relations to prevent the N+1 query problem.

edited Nov 04 '14 at 10:24

answered Nov 04 '14 at 10:15

Syl

3,719
6
35
59

Wow thanks! never properly learn SQL before. So I don't really know about this LEFT RIGHT INNER join. – hrsetyono Nov 04 '14 at 10:17
2

I think that particular resource is rather frowned upon within SO (http://meta.stackexchange.com/questions/87678/discouraging-w3schools-as-a-resource) – Strawberry Nov 04 '14 at 10:19
1

Changed it to the only other English link I could find. I guess this one should be OK on SO ^^ http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ – Syl Nov 04 '14 at 10:25

score 0 · Answer 2 · answered Nov 04 '14 at 10:17

0

Maybe this is what you're after...

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%';

answered Nov 04 '14 at 10:17

Strawberry

33,750
13
40
57

Thanks for the answer! Unfortunately I have to select the answer by @Syl because he/she answered it first. – hrsetyono Nov 04 '14 at 10:19

Rails - JOIN clause doesn't include row with null Foreign Key

2 Answers2