Trouble with a lending library query

Let's say you operate a library.

You have so many patrons and so many books you need a Rails app and database to keep your sanity.

The basic schema looks something like this:

create_table :books do |t|
  t.bigint :id
  t.string :title
end

create_table :patrons do |t|
  t.bigint :id
  t.string :name
end

create_table :check_outs do |t|
  t.references :book, foreign_key: true
  t.references :patron, foreign_key: true
  t.datetime :checked_out_at, null: false
  t.datetime :checked_in_at
end

A simple "check out" keeps track of where a book is at any given time (checked out to someone or in the library's possession).

In this application you want to be see which books are available for patrons to check out.

I have thought of two situations that would make a book 'available'.

1. The book is new and has never been checked out.
2. The book was checked out but has since been checked in.

The first is situation is easy. Look for books that don't have any check_outs...

class Book < ApplicationRecord
  has_many :check_outs

  scope :never_checked_out, -> { left_joins(:check_outs).where(check_outs: { book_id: nil }) }
end

The second situation is stumping me for some reason.

I can query the books and join the check outs table to see which books are currently checked out... (pretty much the opposite of what I want)

...
  scope :currently_checked_out, -> { left_joins(:check_outs).where(check_outs: { checked_in_at: nil }) }
...

... and the logical opposite query lets me know which books have been returned in the past...

...
  scope :books_returned, -> { left_ joins(:check_outs).where.not(check_outs { checked_in_at: nil }) }
...

... but... this books_returned query will also return any book that had been previously checked in, even if it is currently checked out.

How would I put together a query that can return only the books in my possession?

Once I get these queries to work, I could also use some help figuring out how to merge them into a single database query. I've gotten as far as this (bonus if you have a better solution!):

...
  scope :available, -> { books_returned + never_checked_out }
...

Hey Peter! What you want to do is have a state/status column on your Book. This way the initial state is 'Available' and then when a check-out record is created that state/status on the book can be changed to "Checked-out". When someone is returning that book and you update the 'checked_in_at' datetime column you can update the status of the book back to Available.

This way you don't have to do weird queries but can just query for all books that are 'Available" or "Checked-out"

With AASM I was able to do something like this:

aasm column: :status do
    state :available, :initial => true
    state :checked_out

   event :checkout do
      transitions :from => :available, :to => :checked_out
    end

    event :checkin do
      transitions from: :checked_out, to: :available
    end

end

On book's you would just have an aasm column or a status column which is of string type.

Tabish, you helped bring me to a solution!

I think you're right. Querying this information was rather complex and a state machine design is better.

I didn't want to add a gem since I avoid dependencies when I can, so this is what I came up with.

class Book < ApplicationRecord
  has_many :check_outs

  enum state: [:available, :checked_out, :damaged]

  def change_state
    damaged! if damage_info.present? # If there's a note about the poor condition, switch to damaged

    if book_checked_out?
      checked_out!
    else
      available!
    end
  end

  private

  def book_checked_out?
    check_outs.where(checked_out_at: nil).present?
  end
end

class CheckOut < ApplicationRecord
  belongs_to :patron
  belongs_to :book

  validates_uniqueness_of :book, scope: :checked_out_at # make sure no book can be checked out twice at the same time
  after_save :change_book_state

  private

  def change_book_state
    book.change_state
  end
end

Basically using ActiveRecord callbacks to change the state and enums to keep the state and add the available query.

Much simpler than the crazy join queries I was writing and failing at before.

Thanks for the tip!

No problem! Yea the states I had were much more and I ended up using the gem but your stuff works so glad I could help!