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

[James R. Leu]

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.

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.

The names used in the join are for relationships I've defined 
for PermissionMapping:

    __PACKAGE__->might_have(probe => 'ProbeTypeMapping',
        { 'foreign.probeTypeId' => 'self.probeTypeId' } );

    __PACKAGE__->has_one(permission => 'PermissionTypeData',
        { 'foreign.permissionTypeId' => 'self.permissionId' } );

The resulting query looks like:

    SELECT me.entityId, probe.shortName, permission.shortName
    FROM PermissionMapping me
    LEFT JOIN ProbeTypeMapping probe ON ( probe.probeTypeId = me.probeTypeId )
    JOIN PermissionTypeData permission ON ( permission.permissionTypeId = me.permissionId )
    WHERE ( me.userId = 109 )

On Tue, May 08, 2007 at 02:59:23PM +0100, RA Jones wrote:
> 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
> **********************************************************************
> 
> 
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: 
> http://www.mail-archive.com/dbix-class@lists.rawmode.org/

-- 
James R. Leu
jleu at mindspring.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
Url : http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070508/a19bd740/attachment.pgp