Optimizing Many-to-Many Query

[Neil Chaudhuri]

I have the following models:

Person
has n, :photos

Album
has n, :photos, :through => Resource
has n, :people, :through => :photos, :via => :person

Photo
belongs_to :person, :required => true
has n, :albums, :through => Resource


So here is what I want:

Given an album, I would like all the photos in that album grouped by
the person they belong to. Ideally, I would love to be able to query
an album for all the people in it, and that collection of people would
have ONLY those photos of theirs in the album. After all, the person
probably has a ton of other photos in other albums that are
irrelevant.

I have tried a ton of queries. Some are just syntactically off; others
are not quite right and way too slow anyway. Any insight into a proper
query or in reorganizing my model is really appreciated.

Thanks.

[Clifford Heath]

On 19/03/2012, at 6:55 AM, Neil Chaudhuri wrote:
> Person
> has n, :photos
>
> Album
> has n, :photos, :through => Resource
> has n, :people, :through => :photos, :via => :person
>
> Photo
> belongs_to :person, :required => true
> has n, :albums, :through => Resource

Neil,

Your Resource table has a photo_id and an album_id.
You will need two indices to traverse this efficiently in both directions,
one with the pair (photo_id, album_id) and one the other way around.
But that doesn't solve your basic problem. You have

Person -< Photo -< Resource >- Album

You're trying to make a :through relationship that goes through 3 links
from Album to Person. Not sue, but I don't think this is going to work.

> Given an album, I would like all the photos in that album grouped by
> the person they belong to.

You have no index that contains a (album, person) pair, so that grouping
is going to have to occur after retrieval.

Clifford Heath.

[Neil Chaudhuri]

Clifford,

Thanks for the insight. I'm still a bit unclear on how to model things
though. Are you saying that I need something like this?

Person
has n, :photos
has n, :albums

Album
has n, :photos, :through => Resource

has n, :people, :through => Resource

Photo
belongs_to :person, :required => true
has n, :albums, :through => Resource

This gives me an album-person index. It also creates a direct linkage
between person and album. Does this seem more plausible to you? Would
you suggest further improvements?

Thanks.

[Neil Chaudhuri]

As it turns out, these changes have significantly reduced query time,
so that's a good thing. However, I cannot claim victory because the
queries do not return precisely what I want, which is as mentioned
before:

Given an album, I would like all the photos in that album grouped by
the person they belong to.

In code terms, I would like an array of people such that when I call
person.photos, I get a collection of only those photos in the album
and not all photos belonging to that person. How can I write this
query?

Thanks.