# Complicated find with habtm conditions

**URL:** <https://www.ruby-forum.com/t/complicated-find-with-habtm-conditions/121405>\
**Category:** Rails\
**Created:** [November 27, 2007, 5:57pm UTC](https://www.ruby-forum.com/t/complicated-find-with-habtm-conditions/121405 "2007-11-27T17:57:10Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![pascal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/e9a140/32.png) [@pascal](https://www.ruby-forum.com/u/pascal)\
**Post date:** [November 27, 2007, 5:57pm UTC](https://www.ruby-forum.com/t/complicated-find-with-habtm-conditions/121405/1 "2007-11-27T17:57:10Z")

</div>

Hi forum!

Please have a look at this link first:  
[http://www.odamoz.org.mz/reports/rpt\_own.asp](http://www.odamoz.org.mz/reports/rpt_own.asp)

I’m currently trying to refactor that in Rails, here’s some more info  
first:

- We are looking for Projects.
- A project has\_and\_belongs\_to\_many MDGs, Donors, Provinces and Sectors.

My initial idea would lead to a horrible performance, so any other ideas  
are appreciated.  
Here is it, though:

- Get all projects for each of the selected items
- Join and unify the result arrays using the & operator.

[code=]mdg\_prj = MDG.find(params[:mdgs]).projects  
donor\_prj = Donor.find(params[:donors]).projects  
province\_prj = Province.find(params[:provinces]).projects  
sector\_prj = DacSector.find(params[:sectors]).projects

prj\_result = mdg\_prj & donor\_prj & province\_prj & sector\_prj[/code]  
Image this with \>10000 projects… ☹

Does anyone of you have any idea? That’s really driving me nuts.

Regards,

Pascal

---

<div class="post-metadata">

**Author:** ![pascal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/e9a140/32.png) [@pascal](https://www.ruby-forum.com/u/pascal)\
**Post date:** [November 27, 2007, 6:17pm UTC](https://www.ruby-forum.com/t/complicated-find-with-habtm-conditions/121405/2 "2007-11-27T18:17:23Z")

</div>

On 27 Nov 2007, at 16:57, Pascal E. wrote:

> Sectors.

If you write out the joins, you’ll get 8 joins (1 for the join table  
and one for the actual table).  
That’s a fair few joins, but not the end of the world, and databases  
are likely to be better at doing this sort of thing than ruby.  
In mysql if join optimiser is getting it wrong you can force the  
ordering you supply

Fred

---

<div class="post-metadata">

**Author:** ![pascal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/e9a140/32.png) [@pascal](https://www.ruby-forum.com/u/pascal)\
**Post date:** [November 27, 2007, 11:49pm UTC](https://www.ruby-forum.com/t/complicated-find-with-habtm-conditions/121405/3 "2007-11-27T23:49:59Z")

</div>

On Nov 27, 11:05 am, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> and one for the actual table)…  
> … databases  
> are likely to be better at doing this sort of thing than ruby.

I agree. I would just call find\_by\_sql in this case. I find that a  
bit clearer than calling find with :joins.