Getting child collection with find_by_sql

rubyonrails-talk
1

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?

2

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).

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?

3

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.