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

RA Jones ra.jones at dpw.clara.co.uk
Tue May 8 14:59:23 GMT 2007


Will Hawes wrote:
> On 08/05/07, RA Jones <ra.jones at dpw.clara.co.uk> wrote:
>> I haven't shown my code as I don't think this is the right way to go
>> about it. Basically I want the DBIC equivalent of:
>>
>> select var1, var2, var3, var101, etc
>> from table1 a
>>    left join table2 b on a.id = b.id
>>    left join table3 c on a.id = c.id
>>    etc
>> where a.var1 = 'foo' and c.var10 = 'bar'
>>
>> It's probably easier than I imagine....
>
> Should be as easy as this:
>
> http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Using_joins_and_prefetch 
>
I read through the DBIC Cookbook a few times but didn't find anything 
which seemed appropriate. The example you cite works for retrieving data 
on a CD, and so is based on the CD schema, pulling also artist->name 
from the artist table. My application doesn't have an obvious table 
(schema) to base the query on as the users search could be based on any 
one of many fields in many tables - perhaps it doesn't matter, just pick 
any one of the many possible tables?

Presumably the usage in a catalyst controller would be:

my $rs = $c->model('Schema::PickAnyTable')->search({ ... }) instead of 
$schema->resultset('CD')->search({...})?

and I can't use the equivalent of 'artist.name' => 'Bob Marley' as the 
generated sql uses table aliases (ie me.id, me.other_fields, table2.id, 
table2.other_fields, etc), so I would need name => 'Bob Marley' and hope 
'name' is a unique field. Or should I rely on knowing in advance that 
the table corresponding to the selected  Schema gets the 'me' alias, and 
all the rest get their table name as the alias? A case of what is the 
Best Practice [TM] here.
--
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
**********************************************************************




More information about the Dbix-class mailing list