CODEWITHSUNDEEP
ruby-on-railsactiverecord
Joe
Joe

Reputation: 15341

How do I select all child rows corresponding to a set of parent ids

How do I select all child rows corresponding to a certain parent table using ActiveRecord queries.

create table foo (id, ...)
create table bar (id, foo_id, ...)

select * from foo where key = ? 
# The above return a single row or multiple rows.

select * from bar where foo_id in <ids from the above output>

How do I achieve this?

Upvotes: 1

Views: 1234

Answers (4)

Jay Truluck
Jay Truluck

Reputation: 1519

Using only queries with ActiveRecord 

foo = Foo.where(:key = ?)
Bar.where(:foo_id => foo)

Using associations and eager loading :

#foo model
class Foo < ActiveRecord::Base
  has_many :bars
end

#bar model
class Bar < ActiveRecord::Base
 belongs_to :foo
end

#query
Foo.includes(:bars).where(:key => ?).collect(&:bars)

Upvotes: 1

Frederick Cheung
Frederick Cheung

Reputation: 84124

Arel will do this:

Profile.where(user_id: User.select("id").where(exclude_from_analytics: true))
#=> Profile Load (395.1ms)  SELECT `profiles`.* FROM `profiles` WHERE `profiles`.`user_id` IN (SELECT id FROM `users` WHERE `users`.`exclude_from_analytics` = 1)

Whether you actually want to do a subselect is another thing - its worth trying to write the query as a join:

Bar.joins(:foo).where(:foos => {:key => key})

assuming that you have setup Bar with a belongs_to :foo association. The joins method will also accept a raw sql fragment

Upvotes: 0

bullfrog
bullfrog

Reputation: 1633

You should be able to do 

Bar.find_all_by_foo_id([1,2,3,4])

In which case you will have to find all the foo ids previously. But if you need it in one query sub-query which ChuckE mentioned is best.

Upvotes: 0

ChuckE
ChuckE

Reputation: 5688

Humm... I don't know if your question is whether something like that can be done with ARel or not (which, it cannot). What you want is to provide matches using a sub-query, which you can do in SQL, and therefore using ActiveRecord::Base.find_by_sql.

ActiveRecord::Base.find_by_sql("select * from bar where foo_id in 
(select id from foo where key = #{key})")

Upvotes: 0

Related Questions