[Dbix-class] Fixing my relationships to get working queries

[Peter Rabbitson]

On 01/19/2015 06:41 PM, John Stoffel wrote:
>
> Sorry, I'm expecting to get back consistent results.

I am not entirely sure how to interpret that... :)

>  I've actually
> given up trying to make multi-step join or prefetch work for me,
> because I was running out of time.

Sorry about that - the mailing list had issues, I only received your 
emails today.

> Just to refresh the conversation,
>
> ...
>
> I get this monstrosity:
>
>      SELECT me.name_id, me.full_name, me.last_name, me.first_name,
>      me.comments, account.account_id, account.account_number,
>      account.boxfolder_id, account.name_id, account.url, account.comments,
>      account.volume, account.box, account.folder, account.range FROM
>      (SELECT me.name_id, me.full_name, me.last_name, me.first_name,
>      me.comments FROM names me WHERE ( full_name REGEXP ? ) ORDER BY
>      full_name ASC LIMIT ?) me LEFT JOIN account account ON account.name_id
>      = me.name_id WHERE ( full_name REGEXP ? ) ORDER BY full_name ASC:
>      '[[:<:]]carpenter[[:>:]]', '10', '[[:<:]]carpenter[[:>:]]'

This (as you called it) "monstrosity" is there for a good reason. 
However I can't really explain it without the actual code that produced 
it (hint - the thing below is *not* what produced the above query - it 
is missing a rows => spec)

>
> my $rs = $schema->resultset('Name')->search({ full_name => { regexp =>
> 					      '[[:<:]]'.$name.'[[:>:]]' },
>                                              },
>                                              {
>                                               prefetch => [ 'account' ],
>                                               order_by => { -asc => 'full_name' },
>                                              });

If you have time and want to really understand what was going on - let's 
start over with an actual code/query example.

Cheers