title: Getting notes through chapter or book in MySQL
body: <p>I have three models:</p>
  
  <p>notes, chapters and books</p>
  
  <p>A note can either have a reference to a chapter (chapter_id) OR a reference to a book (book_id).  Also, a chapter has a reference to a book (book_id).</p>
  
  <p>Each chapter is referencing to a client (client_id) and each client is referencing to a user (user_id)</p>
  
  <p>I want to find all the distinct notes that a specific user can read.  Those are the chapter notes that belong to that user AND the book notes from those chapters.</p>
  
  <p>Currently I have this query:</p>
  
  <pre><code>SELECT * FROM notes n 
  JOIN chapters c ON (c.id = n.chapter_id OR c.book_id = n.book_id)
  JOIN clients cl ON (cl.id = c.client_id)
  JOIN users u ON (u.id = cl.user_id)
  WHERE u.id = my_user_id
  </code></pre>
  
  <p>Since I want to run this on my model because I am paginating afterwards with kaminari, I am doing it like this (using Note model):</p>
  
  <pre><code>Note.joins(
  %(
  JOIN chapters c ON (c.id = n.chapter_id OR c.book_id = n.book_id)
  JOIN clients cl ON (cl.id = c.client_id)
  JOIN users u ON (u.id = cl.user_id)
  ).
  where("u.id = :id", id: given_user_id).
  distinct
  </code></pre>
  
  <p>This query runs pretty slow and I have a feeling this is due to do the <code>OR</code> in the JOIN clause (I have never seen or done that before), because I have tried with two separate queries (one to fetch the chapter notes and another one to fetch the book notes) and it runs faster.</p>
  
  <p>I would like to know what is a proper way to solve this problem making sure that the results are paginatable.</p>