Complicated find with habtm conditions

Rails
1

Hi forum!

Please have a look at this link first:
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… :frowning:

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

Regards,

Pascal

2

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

3

On Nov 27, 11:05 am, Frederick C. [email protected]
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.