[Dbix-class] Prefetch multiple many_to_many?

[Ben Staude]

Hi,

I have a setup like the many_to_many-example Actor/Role in the docs, but the 
Actor has another many_to_many relationshiop to, for example, languages:

Actor->many_to_many(roles=> 'actor_roles', 'role');
Actor->many_to_many(languages => 'actor_languages', 'language');

When selecting an Actor, I'd like to prefetch his roles and languages. I'm not 
yet sure whether this really makes sense with respect to performance for my 
application, but at least I want to understand what's going wrong when trying 
to do so:

# Get actors by ids
my @actors = $schema->resultset('Actor')->search(
                { 'me.id' => { -in => \@actor_ids } },
		{ prefetch => { actor_roles => 'role',
		                actor_languages => 'language' } })->all;

# Build a string-list of each actors roles:
foreach my $actor (@actors) {
	my $role_titles = join ', ', map { $_->role->title } $actor->actor_roles );
}

There are two issues with this:

1.) I cannot use 

map { $_->title } $actor->roles

This yields correct results, but ends up in another SELET ... from actor_role 
LEFT JOIN role ON ... for every $actor. The prefetched data isn't being used. 
Prefetching only one many_to_many doesn't change this behaviour.

2.) Doing it as shown in the code above, I get each role (each actor_role, to 
be precise) more than once if the actor has more than one language.
Of course, the initial SELECT (which is using multiple left joins) yields much 
more rows than we have actors, depending on the combination of actors, roles 
and languages. But as DBIC is able to sort out the "correct" number of 
actors, I'd have hoped it would be able to do so also for actor_roles and 
actor_languages. It works fine if I prefetch only one of the many_to_many 
relationships.

Is it at all *possible* to do what I tried above? If not, should DBIC warn/ 
die on trying to do so? Or am I just doing sth. wrong both with 1.) and 2.)?

Thank you very much for any hints!!

Ben