Getting child collection with find_by_sql

[Herman]

Hi all,

I have a simple Parent "has_many" Child model relationships, and I
have the following query.

Parent.find_by.sql(["select p.* from parent p inner join p.id on
c.parent_id where <some calculation> and c.enabled = :cond", {:cond =>
true}])

This got all the parent object out and it was good. However, when I
do Parent.children collection, it gives me back all the chile
regardless if the child is enabled or disabled (the condition check
for child in the query).

Is there way to do this?

[Michael Pavling]

Your find_by_sql just gets the Parent objects - the Child association
objects are populated by a separate sql request when you call
parent.children (you can confirm this by looking in the logs).

> Is there way to do this?

Set up a named_scope in Parent for :enabled_children.

BTW Is there really some reason why you have to use find_by_sql, and
you can't just use a normal finder?

[Chirag Singhal]

Hi Herman,

Not sure why you want to use find_by_sql here. If you all you really want to do if fetch all children along with parent and avoid multiple sql queries, then you can do something like this:

Parent.all(:include => :children, :conditions => ["children.enabled = ?", true])

This will do just one sql query, and get all parents, and all enabled children, so when you iterate over result set and call parent.children, it will give you just the enabled children without going back to the database to fetch them.

Hope this helps.