sql - How to join tables and apply where clause for active record RAILS

Question

This seems like a very simple thing to do but despite looking through the rails docs it's not working like I expect. I'm likely making an incorrect assumption somewhere. Maybe I'm trying to get activerecord to be used for something it is not intended for?

I'm trying to join my Books table and Checkout_logs table and only return records matching user_id and with returned_date of null (nil)

I'm trying to do something like this example in sql (except list all the book info not just title and get user_id from parameters)

select b.title,c.due_date from 
(select * from books) b
JOIN
(select * from checkout_logs) c
ON b.id = c.book_id
where c.user_id = 1 and returned_date is null

This is my attempt

books_controller.rb

def my_books    
    @books = Book.joins(:checkout_logs).where(user_id: params[:user_id], returned_date: nil)
  end

and view

my_books.html.erb

<table>
  <thead>
    <tr>
      <th>Title</th>
      <th>Author</th>
      <th>Genre</th>
      <th>Subgenre</th>
      <th>Pages</th>
      <th>Publisher</th>      
      <th>due_date</th>
      <th colspan="1"></th>
    </tr>
  </thead>

  <tbody>
    <% @books.each do |book| %>
      <tr>
        <td><%= link_to book.title, book %></td>
        <td><%= book.author %></td>
        <td><%= book.genre %></td>
        <td><%= book.subgenre %></td>
        <td><%= book.pages %></td>
        <td><%= book.publisher %></td>
        <td><%= book.due_date %></td>
        <td><%= link_to 'Return', books_return_path %></td>        
      </tr>
    <% end %>
  </tbody>
</table>

book.rb

class Book < ApplicationRecord
    has_many :checkout_logs, dependent: :destroy
end

checkout_log.rb

class CheckoutLog < ApplicationRecord
  belongs_to :user
  belongs_to :book
end

It gives an error upon loading the view that books.user_id doesn't exist or books.due_date doesn't exist. I understand books doesn't have those things but I thought that by joining it with checkout_log I would be able to access them that way?

I'd prefer to learn how to do this in activerecord because I assume it is the best practice. I'm open to using another method. I have another page that does something similar using AREL, but I'd prefer to clean that up too if I can learn how to use activerecord better.

question from:https://stackoverflow.com/questions/65617392/how-to-join-tables-and-apply-where-clause-for-active-record-rails

Answer 1

You can debug your queries by passing .to_sql to them

 @books = 
   Book
   .joins(:checkout_logs)
   .where(user_id: params[:user_id], returned_date: nil)

in this case, I think the problem is that the where conditions are made against books table instead of checkout_logs table. You can fix this by doing

.where(checkout_logs: { user_id: params[:user_id], returned_date: nil })