[Dbix-class] Multi-table search - how to?

[RA Jones]

James R. Leu wrote:
> I too found the examples in the cookbook tough to translate to
> my application, but after thinking about it, the cookbook is trying
> to tackle a tough subject, and I think no matter what the learning
> curve with DBIC is going to be steep.  Keep chugging along, things willr
> click.
>   
Hope so!
> Here is an example of how I do a join with DBIC:
>
>     my @perm = $schema->resultset('PermissionMapping')->search({
>         userId => $userId
>     }, {
>         select => [
>             'me.entityId',
>             'probe.shortName',
>             'permission.shortName',
>         ],
>         as => [
>             'entityId',
>             'probeName',
>             'permissionName',
>         ],
>         join =>[
>             'probe',
>             'permission',
>         ],
>     });
>
> Where a row in PermissionMapping contains 3 fields, all which are
> are indices to other tables.
>   
OK thanks, I've got a variant of that working. But two issues remain - I 
have to do some ugly contortions to construct the 'where' clause 
depending on which of several fields (or combinations thereof) the user 
wishes to search on (I can probably improve that one quite easily). But 
also I still have to specify the foreign table aliases manually in the 
select (ie me.fields, table2.fields, table3.fields, etc). Probably no 
big deal in the long run, but is there no way round this as I can't know 
in advance which table will get the 'me' alias? Another consequence of 
this by the way is that I can't optimise the join, although MySQL5 will 
probably optimise if for me.
--
Richard Jones
Leeds, UK

**********************************************************************
This message  may  contain  confidential  and  privileged information.
If you are not  the intended  recipient please  accept our  apologies.
Please do not disclose, copy or distribute  information in this e-mail
or take any  action in reliance on its  contents: to do so is strictly
prohibited and may be unlawful. Please inform us that this message has
gone  astray  before  deleting it.  Thank  you for  your co-operation.

NHSmail is used daily by over 100,000 staff in the NHS. Over a million
messages  are sent every day by the system.  To find  out why more and
more NHS personnel are  switching to  this NHS  Connecting  for Health
system please visit www.connectingforhealth.nhs.uk/nhsmail
**********************************************************************