[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