Rails - Jan 2006 - How to filter an activerecord find_all...

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!

-- 
Posted via http://www.ruby-forum.com/.

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

-- 
Posted via http://www.ruby-forum.com/.

On 2006-01-24 19:08:18 -0500, Kevin Olbrich 
<kevin.olbrich@duke.edu> said:
> 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.
Doesn''t Rails escape table and column names with backtics (``) ? If 
not, it should, imo :)

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

Benoit Gagnon wrote:
> On 2006-01-24 19:08:18 -0500, Kevin Olbrich
> <kevin.olbrich@duke.edu> 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 :)
> 
> 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

-- 
Posted via http://www.ruby-forum.com/.

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.

-- 
Posted via http://www.ruby-forum.com/.