# How should I select rows from a join-model based on more tha

**URL:** <https://www.ruby-forum.com/t/how-should-i-select-rows-from-a-join-model-based-on-more-tha/61791>\
**Category:** Rails\
**Created:** [May 27, 2006, 12:44pm UTC](https://www.ruby-forum.com/t/how-should-i-select-rows-from-a-join-model-based-on-more-tha/61791 "2006-05-27T12:44:43Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![alder](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a4c791/32.png) [@alder](https://www.ruby-forum.com/u/alder)\
**Post date:** [May 27, 2006, 12:44pm UTC](https://www.ruby-forum.com/t/how-should-i-select-rows-from-a-join-model-based-on-more-tha/61791/1 "2006-05-27T12:44:43Z")

</div>

A concrete example:

We’re building a system to organize the information about workshops  
being held in various conventions. A convention has more than one  
workshop, and each workshop can be held in more than one convention.  
Also each workshop has a host, who is specific to a workshop being  
held in a specific convention.

For example, Matz may host an “Introduction to Ruby” workshop in  
RubyConf. And \_why may host a the same workshop in RailsConf. This  
operates through the join-model convention\_workshops, which has the  
following rows: convention\_id, workshop\_id, host\_id, start\_time. It  
belongs\_to :convention, :workshop, and :host.

Associations make it very easy to select by one association. If I want  
to get the start\_time of all workshops held on RubyConf Matz, I just  
find RubyConf’s model instance and call  
#convention\_workshops.collect{|cw| cw.star\_time} on it. But suppose we  
want to select the start\_time for only the workshops hosted by Matz on  
RubyConf. How should we do that?

The SQL way would be:

SELECT convention\_workshops.start\_time  
FROM convention\_workshops, conventions, hosts  
WHERE conventions.id=convention\_workshops.convention\_id  
AND conventions.name=‘RubyConf’  
AND convention\_workshops.host\_id=hosts.id  
AND hosts.name=‘Matz’;

I’m pretty sure there shouldn’t be a need to write all that SQL for  
Rails, especially after we bothered to set up all the association on  
the code level. But what is it?

---

<div class="post-metadata">

**Author:** ![alder](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a4c791/32.png) [@alder](https://www.ruby-forum.com/u/alder)\
**Post date:** [May 27, 2006, 2:15pm UTC](https://www.ruby-forum.com/t/how-should-i-select-rows-from-a-join-model-based-on-more-tha/61791/2 "2006-05-27T14:15:43Z")

</div>

For each table you will need the models:

Table Model  
workshops -\> workshop.rb  
conventions -\> convention.rb  
hosts -\> host.rb

Relations:

Tables:  
create table convetions\_workshops (  
workship\_id int null,  
convention\_id int null,  
host\_id in null,  
start\_at datetime null  
);

In workshop.rb:  
has\_and\_belongs\_to\_many :conventions

In convention.rb  
has\_and\_belongs\_to\_many :workshops

---- If you have all set up as above the query would be

Query:  
@conventions = Convention.find(:all, :condition =\> “name = ‘RubyConf’”)  
@conventions.workshops.select {|p| p.host.name = ‘Matz’}  
or if you only want the times:  
@conventions.workshops.select {|p| p.host.name = ‘Matz’}.collect{|t|  
t.start\_at}

The other way is to decompose the sql into ruby syntax which would look  
like:  
Convention.find(:all, :select =\>"", :joins =\> “”, :conditions =\>"")

---

<div class="post-metadata">

**Author:** ![alder](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a4c791/32.png) [@alder](https://www.ruby-forum.com/u/alder)\
**Post date:** [May 27, 2006, 6:05pm UTC](https://www.ruby-forum.com/t/how-should-i-select-rows-from-a-join-model-based-on-more-tha/61791/3 "2006-05-27T18:05:18Z")

</div>

Alder G. wrote:

> For example, Matz may host an “Introduction to Ruby” workshop in  
> RubyConf. And \_why may host a the same workshop in RailsConf. This  
> operates through the join-model convention\_workshops, which has the  
> following rows: convention\_id, workshop\_id, host\_id, start\_time. It  
> belongs\_to :convention, :workshop, and :host.
> 
> Associations make it very easy to select by one association. If I want  
> to get the start\_time of all workshops held on RubyConf Matz, I just  
> find RubyConf’s model instance and call  
> #convention\_workshops.collect{|cw| cw.star\_time} on it. But suppose we  
> want to select the start\_time for only the workshops hosted by Matz on  
> RubyConf. How should we do that?
> 
> The SQL way would be:
> 
> SELECT convention\_workshops.start\_time  
> FROM convention\_workshops, conventions, hosts  
> WHERE conventions.id=convention\_workshops.convention\_id  
> AND conventions.name=‘RubyConf’  
> AND convention\_workshops.host\_id=hosts.id  
> AND hosts.name=‘Matz’;
> 
> I’m pretty sure there shouldn’t be a need to write all that SQL for  
> Rails, especially after we bothered to set up all the association on  
> the code level. But what is it?

You’re right that doing a SQL SELECT just for the start\_time is not  
idiomatic Rails. You’re on the right track with your collect() filter.  
The trick is getting the join model rows based on two set foreign keys.  
By the way, I suggest calling your join model something like  
Presentation or Engagement, rather than the convention\_workshops table  
name which is probably too easy to confuse with a habtm join table.

in Host:  
has\_many :engagements  
has\_many :workshops, :through =\> :engagements  
has\_many :conventions, :through =\> :engagements

matz = Host.find\_by\_name(“Matz”)  
matz\_engagements = matz.engagements.find(:all, :conditions =\>  
[“convention\_id = ?”, con\_id])

Now that you have the engagements, you can ask each for the workshop and  
its start\_time.

If you don’t like the query with the conditions option, you can hide  
that by using an association:

has\_many :engagements do  
def by\_convention(convention)  
find(:all, :conditions =\> [“convention\_id = ?”, convention])  
end  
end

Then you’d write  
matz\_engagements = matz.engagements.by\_convention(convention)

Of course you can do the symmetric thing in the Convention model.

–  
Josh S.  
[http://blog.hasmanythrough.com](http://blog.hasmanythrough.com)

---

<div class="post-metadata">

**Author:** ![alder](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a4c791/32.png) [@alder](https://www.ruby-forum.com/u/alder)\
**Post date:** [May 27, 2006, 8:18pm UTC](https://www.ruby-forum.com/t/how-should-i-select-rows-from-a-join-model-based-on-more-tha/61791/4 "2006-05-27T20:18:06Z")

</div>

On 5/27/06, Roland M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> create table convetions\_workshops (  
> In convention.rb

Thanks, Roland

I’m weighing your select solution against an alternative suggestion by  
Josh below. You’re welcome to chime in if interested 🙂

---

<div class="post-metadata">

**Author:** ![alder](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a4c791/32.png) [@alder](https://www.ruby-forum.com/u/alder)\
**Post date:** [May 27, 2006, 8:21pm UTC](https://www.ruby-forum.com/t/how-should-i-select-rows-from-a-join-model-based-on-more-tha/61791/5 "2006-05-27T20:21:27Z")

</div>

On 5/27/06, Josh S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > #convention\_workshops.collect{|cw| cw.star\_time} on it. But suppose we  
> > AND hosts.name=‘Matz’;  
> > name which is probably too easy to confuse with a habtm join table.
> 
> in Host:  
> has\_many :engagements  
> has\_many :workshops, :through =\> :engagements  
> has\_many :conventions, :through =\> :engagements
> 
> matz = Host.find\_by\_name(“Matz”)  
> matz\_engagements = matz.engagements.find(:all, :conditions =\>  
> [“convention\_id = ?”, con\_id])

Actually, I’d be looking for engagements when only the convention’s  
name (as a string) is known, so it would be:

matz.engagements.find(:all, :conditions =\> [“convention\_id = ?”,

Convention.find\_by\_name(‘RubyConf’).id])

But there’s the alternative:

matz.engagements.select {|e| e.convention.name == “RailsConf”}

Which should I pick? Seems like the former might be less efficient,  
entailing two queries instead of just one in the later. But I’m sure  
you’d have a clearer idea about this than me.

---

<div class="post-metadata">

**Author:** ![alder](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a4c791/32.png) [@alder](https://www.ruby-forum.com/u/alder)\
**Post date:** [May 27, 2006, 9:37pm UTC](https://www.ruby-forum.com/t/how-should-i-select-rows-from-a-join-model-based-on-more-tha/61791/6 "2006-05-27T21:37:25Z")

</div>

Alder G. wrote:

> Actually, I’d be looking for engagements when only the convention’s  
> name (as a string) is known, so it would be:
> 
> matz.engagements.find(:all, :conditions =\> [“convention\_id = ?”,  
> Convention.find\_by\_name(‘RubyConf’).id])
> 
> But there’s the alternative:
> 
> matz.engagements.select {|e| e.convention.name == “RailsConf”}
> 
> Which should I pick? Seems like the former might be less efficient,  
> entailing two queries instead of just one in the later. But I’m sure  
> you’d have a clearer idea about this than me.

If you already know the convention’s name, wouldn’t you have the model  
object for it already? Unless you are storing the name somewhere else  
besides the Convention object, which would be redundant. So I don’t  
think you’d need an extra query because you’d already have done it.

As for which to pick, only you can decide. You’ll have to look at your  
performance data to see which is faster for your setup. Do whatever is  
simplest for you, then see if you need to optimize it later.

–  
Josh S.  
[http://blog.hasmanythrough.com](http://blog.hasmanythrough.com)

---

<div class="post-metadata">

**Author:** ![alder](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a4c791/32.png) [@alder](https://www.ruby-forum.com/u/alder)\
**Post date:** [May 28, 2006, 12:14am UTC](https://www.ruby-forum.com/t/how-should-i-select-rows-from-a-join-model-based-on-more-tha/61791/7 "2006-05-28T00:14:24Z")

</div>

On 5/27/06, Alder G. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> operates through the join-model convention\_workshops, which has the  
> The SQL way would be:  
> the code level. But what is it?

This assumes the workshop has\_and\_belongs\_to\_many conventions.  
If ConventionWorkshop is a mode in its own right, you can do the  
‘find’ on it directly, making it easier to get at the start time.

Workshop.find :all, :include =\> [:conventions, :hosts],  
:conditions =\> [“conventions.name = ? and hosts.name = ?”, ‘RubyConf’,  
‘Matz’]

If ConventionWorkshop is a real model, you can say:

ConventionWorkshop.find :all, :include =\> [:hosts, :conventions], etc  
etc

---

<div class="post-metadata">

**Author:** ![alder](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a4c791/32.png) [@alder](https://www.ruby-forum.com/u/alder)\
**Post date:** [May 27, 2006, 11:25pm UTC](https://www.ruby-forum.com/t/how-should-i-select-rows-from-a-join-model-based-on-more-tha/61791/8 "2006-05-27T23:25:04Z")

</div>

On 5/27/06, Josh S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > Which should I pick? Seems like the former might be less efficient,  
> > entailing two queries instead of just one in the later. But I’m sure  
> > you’d have a clearer idea about this than me.
> 
> If you already know the convention’s name, wouldn’t you have the model  
> object for it already?

The convention-name string comes from user input.

> …  
> As for which to pick, only you can decide. You’ll have to look at your  
> performance data to see which is faster for your setup. Do whatever is  
> simplest for you, then see if you need to optimize it later.

Yes. I was wondering since I’m still vague about how the various  
method calls translate to actual database queries.

Assuming my newbish eyes haven’t missed some huge implementation  
detail of AR, I guess your method should be better for large datasets,  
especially if we only need to filter in a very small subset of it.  
However, the #select method would be better if the dataset is  
reasonably small and we later need some records we filtered out on the  
first operation - which would necessitate more queries if filtered on  
the database level.

> 

Thanks for helping me again, Josh. I’ve read your post about has\_many  
:through, and in fact that post was the one which led me to use the  
join-models mentioned above. Keep up the good work.

Regards,  
-Alder