How to filter an activerecord find_all


#1

I have a nice hierarchical table structure like this:

divisions
has_many groups

groups
belongs_to division
has_many subgroups

subgroups
belongs_to group
has_many units

units
belongs_to subgroup

I have a report which is based on units, but i want to be able to filter
the units by which subgroup, or which group, or which division. I also
want to sort them by division.name,then group.name, then subgroup.name.

What’s the syntax i need? I’ve got this far…
@units = Unit.find_all :include => XXX, :order => XXX

Oh, and another topic - i finally figured out how to deploy rails with
lighttpd and scgi on a windows server the other day!


#2

chris hulbert wrote:

I have a nice hierarchical table structure like this:

divisions
has_many groups

groups
belongs_to division
has_many subgroups

subgroups
belongs_to group
has_many units

units
belongs_to subgroup

I have a report which is based on units, but i want to be able to filter
the units by which subgroup, or which group, or which division. I also
want to sort them by division.name,then group.name, then subgroup.name.

What’s the syntax i need? I’ve got this far…
@units = Unit.find_all :include => XXX, :order => XXX

Oh, and another topic - i finally figured out how to deploy rails with
lighttpd and scgi on a windows server the other day!

I think this might work if you add it to your find,

:include => ‘divisions’,
:conditions => [“divisions.id = ?”, division_id],
:order_by => ‘divisions.name DESC’

You may run into trouble with the ‘group’ column name since there is a
‘group by’ sql command.

You might also want to consider using acts_as_tree to handle the
group/subgroup thing in one table if that makes sense.

_Kevin


#3

On 2006-01-24 19:08:18 -0500, Kevin O.
removed_email_address@domain.invalid said:

subgroups

:order_by => ‘divisions.name DESC’

You may run into trouble with the ‘group’ column name since there is a
‘group by’ sql command.

Doesn’t Rails escape table and column names with backtics (``) ? If
not, it should, imo :slight_smile:

Ben


#4

Benoit G. wrote:

On 2006-01-24 19:08:18 -0500, Kevin O.
removed_email_address@domain.invalid said:

subgroups

:order_by => ‘divisions.name DESC’

You may run into trouble with the ‘group’ column name since there is a
‘group by’ sql command.

Doesn’t Rails escape table and column names with backtics (``) ? If
not, it should, imo :slight_smile:

Ben

It might work fine, but my personal policy is to avoid table names that
might be mistaken for SQL commands. But then, I’m a belt and suspenders
kind of guy when it comes to this. BTW, I haven’t tested it to see if
anything bad happens.

_Kevin


#5

Groups isn’t my issue here. Anyway its groupS, plural, so it doesn’t
interfere with ‘group’ as in ‘group by’. I checked before i used this
table design - i’m not silly.

But yeah, my question still remains: how to filter a find_all by a field
several joins away.

I’ve gotten it working with a find_by_sql, but that’s a bit ‘brute
force’ don’t you think? I’d like something nicer if its possible.