Find/sql help - I'm confused


#1

Hello,

Somehow I just can’t seem to organise this in my head. So far I have
three tables/models.

Group
id

GroupCategory
id, name

GroupCategoryValue
group_category_id
group_id
value

So a group has many group_category_values which hold values for the
categories which the group is associated with.

What I want to do is:

Find all groups which, given an hash of (group_category_id’s and
values) have every group_category and every value.

For example

Group

(:id => 1, :name => Iain’s group)
(:id => 2, :name => Another group)

GroupCategories
(:id => 1, :name => Category1)
(:id => 2, :name => Category2)
(:id => 3, :name => Category3)

GroupCategoryValues
(:group_id => 1, :group_category_id => 1, :value => “hello”)
(:group_id => 1, :group_category_id => 2, :value => “my”)
(:group_id => 1, :group_category_id => 3, :value => “world”)

(:group_id => 2, :group_category_id => 1, :value => “hello”)
(:group_id => 2, :group_category_id => 2, :value => “my”)
(:group_id => 2, :group_category_id => 3, :value => “mum”)

So if I gave the method {1 => “hello”, 2 => “my”} both groups 1 and 2
should be returned
but if I gave the method {1 => “hello”, 2 => “my”, 3 => “mum”} only
group 2 should be returned.

Any ideas how to do this nicely? I tried using several methods and
just got confused.

Also, the reason I want to do it in an sql/find way is because the
resulting groups need paginating with the will_paginate plugin?

hope you can help

Iain