Using :group and :order with find()

Rails
1

I find that when using both the group and order parameters with find I
get unexpected results. Perhaps I’m doing this incorrectly OR perhaps
my expectations just need adjusting. Either way, here’s what I’m doing:

class State < ActiveRecord::Base
has_one :country
end

class Country < ActiveRecord::Base
has_many :states
end

State.find(:all, :include => :country, :group => ‘countries.id’, :order
=> ‘states.name’)

I expected to have received a collection of states grouped by country
and ordered by the state’s name within each group. Instead I received
a collection that was just ordered by the state’s name. Is there
anyway, besides the obvious find_by_sql DIY approach to get my desired
results using find?

Regards,
Jason

2

Jason F. wrote:

Sorry, correction:

class State < ActiveRecord::Base
belongs_to :country
end

3

On 8/22/06, Jason F. [email protected] wrote:

class Country < ActiveRecord::Base
anyway, besides the obvious find_by_sql DIY approach to get my desired
results using find?

Not quite sure what you’re looking for, but the first thing you might
want
to do is
to declare ‘Country’ as:

class Country < ActiveRecord::Base
has_many :states, :order => ‘name’
end

Then saying

Country.find(:all, :include => :state)

will load all the state/country pairs in one query.

The SQL terminology is not 100% transparent - “GROUP BY” is used to
create
partial results when using aggregate functions. A simple example would
be
using
SUM() to create totals - without grouping, the result is the total of
all
the selected
rows. With grouping, the result will be a set of subtotals.

Hope this helps,

Matt J.
[email protected]
President/Technical Director, Acme Art Company, acmeartco.org

4

Jason F. wrote:

I expected to have received a collection of states grouped by country
and ordered by the state’s name within each group. Instead I received
a collection that was just ordered by the state’s name. Is there
anyway, besides the obvious find_by_sql DIY approach to get my desired
results using find?

GROUP BY collects all the result rows with the same countries.id and
smashes them into a single result row (as Matt said, useful for doing
aggregate functions, like SUM and COUNT). You want to just ORDER BY
countries.id, states.name

ASE