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

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

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

Solution

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.