CODEWITHSUNDEEP
mysqlrubypostgresqlruby-on-rails-4
railsie
railsie

Reputation: 181

how to combine where and order for SQL query

I have projects and grants that are connected through a match. The grant has an expiration date. I want to sort the matches of a project by the grants expiration date.

This get's me the matches based on expiration date:

def self.expires_date_between(from, to)
  Match.where(:grant_id => Grant.where("expires_at  >= ? and expires_at <= ?", from, to))
end

And this shows the matches based on a specific date range.

def current_matches
  range = user.current_period_range
  matches.expires_date_between(range[0], range[1])
end

This works great to show the grants based on a specific time period but the grants aren't showing in order of date (expires_at). It appears that grants are being sorted by the date they were created instead. How can I change my query so that the grants are listed in order of expiration date?

I tried:

def self.expires_date_between(from, to)
  Match.where(:grant_id => Grant.where("expires_at  >= ? and expires_at <= ?", from, to).order('expires_at asc'))
end

This doesn't throw an error but it also doesn't change the order of the grants. Any ideas?

Upvotes: 3

Views: 55

Answers (1)

Jacob Brown
Jacob Brown

Reputation: 7561

Assuming you have defined your associations in your models, this should work (but has not been tested):

Match
  .joins(:grant)
  .merge(Grant.where("expires_at  >= ? and expires_at <= ?", from, to))
  .order('expires_at asc')

Upvotes: 3

Related Questions