CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3searchactiverecord
carpamon
carpamon

Reputation: 6623

Advanced search queries in Rails 3.1

I want to perform an advanced query for the Task model (simplified versio:

class Task
    belongs_to :user_task
    belongs_to :customer
end

class UserTask
    has_many :tasks
    belongs_to: :user
    attr_accessible :date
end

class Customer
    has_many :tasks
end

What I want to build is a search form for de Task model with the followings fields:

I created a TaskSearch resource for saving searches and can imagine a big and ugly SQL string for querying the database but I am not sure this is the best way to do it.

Which would be the ActiveRecord approach?

Thanks in advanced.

Upvotes: 1

Views: 773

Answers (1)

fkreusch
fkreusch

Reputation: 1349

class Task
  belongs_to :user_task
  belongs_to :customer

  scope :from_date, lambda { |date| joins(:user_task).where("user_tasks.date > ?", date) }
  scope :to_date, lambda { |date| joins(:user_task).where("user_tasks.date < ?", date) }
  scope :with_customers, lambda { |customer_ids| joins(:user_task).where("user_tasks.user_id IN (?)", customer_ids) }
end

Hopefully this will work. The thing is, as we are joining tables, you might get multiple results for the same task. You might have to add a distinct clause to the select() method, like this:

select("DISTINCT tasks.*")

Upvotes: 5

Related Questions