Active record query on Many to many relationship
active-record-query, activerecord, ruby-on-rails-3
Solution
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.
Problem
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 ```