Active record query on Many to many relationship

Question

I have a model called Event and another called Product. An event has many products and a product has many events (through the join model called Eventproduct). I am trying to design a query that will select all products that are not in any event thats current date range matches that of another event, so when the user creates an event with a date range it will display the products that are available so that the same product cannot be at 2 events at once. Is this possible with the active records query interface or will I need to write my own specific SQL query.

My migrations looks like:

class CreateProducts < ActiveRecord::Migration
  def change
    create_table :products do |t|
      t.string :make
      t.string :model
      t.integer :wattage
      t.boolean :dmx
      t.decimal :price
      t.timestamps
    end
  end
end


class CreateEvents < ActiveRecord::Migration
  def change
    create_table :events do |t|
      t.datetime :start_date
      t.datetime :end_date
      t.timestamps
    end
  end
end


class AddContactToEvent < ActiveRecord::Migration
  def change
    add_column :events, :name, :string
    add_column :events, :location, :string
    add_column :events, :contact_number, :string
  end
end

class CreateEventproducts < ActiveRecord::Migration
  def change
    create_table :eventproducts do |t|
      t.references :product
      t.references :event

      t.timestamps
    end
    add_index :eventproducts, :product_id
    add_index :eventproducts, :event_id
  end
end

Here are the associated models:

class Event < ActiveRecord::Base
  attr_accessible :end_date, :start_date, :products, :lightings, :name, :location, :contact_number, :product_ids
  has_many :products, :through => :Eventproduct
  has_many :Eventproduct
  validates_presence_of :name, :message => "can't be blank"
  validates_presence_of :location, :message => "can't be blank"
  validates_presence_of :contact_number, :message => "A telephone number is needed so that we can contact you if we have any problems"
  validates_presence_of :start_date, :message => "can't be blank"
  validates_presence_of :end_date, :message => "can't be blank"
end

class Eventproduct < ActiveRecord::Base
  belongs_to :product
  belongs_to :event
  # attr_accessible :title, :body
end


class Product < ActiveRecord::Base
  validates :price, numericality: {greater_than_or_equal_to: 0.01}
    attr_accessible :make, :model, :wattage, :dmx, :price
end

Answer 1

I figured out a query that could help you. You'd have to work out the conditions for the time range and the logic for it.

The query should look something like

Product.joins(:events).where("events.start_date <= :start_date", {start_date: Time.now})

The where clause should contain your logic to filter the events you don't need. Again that piece of code should get you started. So to answer your question, it is possible. Look at the query you get in return and work around that to make the condition that fits your needs. Also, take a look at this link which should help you modify the where clause the way that I did: http://guides.rubyonrails.org/active_record_querying.html

Hope this helps you!

Update:

You might have to do some set difference along with a Product.all to include those products that do not have events at all because that query will return empty if the product does not have a event in the EventProduct table. It might not be efficient but it should work depending on what you need.

Product.all - Product.joins(:events).where("condition reversed")

That should return all the Products that do not meet your condition including those that do not have events yet.

Answer 2

Try this one: Product.includes(:Eventproduct).where(eventproducts: { event_id: nil }).group('products.id').

Note that it is the name of the table inside where condition. Also do not forget to add association for Eventproduct to your Product model: has_many :Eventproduct