On Tue, Oct 21, 2008 at 09:05:52AM +0100, Howe, Tom (IT) wrote: > I'm not sure if this works but I would try something like... > > join => [ { cds=>ripped }, { cds=>ripped } ]
Ha! Awesome advice. I guess in my late-night hacking I didn't try that combination. To sum up problem and solution, reverting to my actual case of images and dimensions, not the artists, cds, and albums trivial example. I have a table with image data called images (time stamp, id, etc), a three-way join table called images_transforms_blobs that stores the record of up to 4 transforms per image. Transform.id=1 is no op, transform.id=2 is thumbnail, transform.id=3 is a pixie (smaller, square thumbnail), tr.id=4 is a display image to fit the screen, and anything greater than 4 is a custom rotation/brightening/etc/ transform done per image. I want to pull down in one record the rotated/transformed image's dimensions, and the thumbnail image's dimensions. I can do it in two records with stock dbic, but that requires post processing using a hash, and then re-sorting the list back in to temporal order---a waste of cpu cycles. In sql, I would just say (simple case) mysql> select images.id, images.create_timestamp, tb.width as thumbw, tb.height as thumbh, ob.width as origw, ob.height as origh from images join images_transforms_blobs ttb on (images.id=ttb.image_id) join images_transforms_blobs tob on (images.id=tob.image_id) join blobs tb on (tb.id=ttb.blob_id) join blobs ob on (ob.id=tob.blob_id) where tob.transform_id > 4 and ttb.transform_id=2 and images.id = 48029; +-------+---------------------+--------+--------+-------+-------+ | id | create_timestamp | thumbw | thumbh | origw | origh | +-------+---------------------+--------+--------+-------+-------+ | 48029 | 2008-10-14 14:07:48 | 192 | 128 | 3008 | 2000 | +-------+---------------------+--------+--------+-------+-------+ 1 row in set (0.00 sec) So to get this nested duplicate join in my real application, my perl is my $biglist = [ $c->model('GraceDBIC::Images')->search( { 'upload_group_id' => $group, 'images_transforms_blobs.transform_id' => 2, 'images_transforms_blobs_2.transform_id' => { '>', '4' }, }, { 'join' => [ { 'images_transforms_blobs' => 'blobs', }, { 'images_transforms_blobs' => 'blobs', }, ], 'order_by' => 'create_timestamp', '+select' => [ 'blobs.width', 'blobs.height', 'blobs_2.width', 'blobs_2.height', 'images_transforms_blobs.transform_id', ], '+as' => [ 'thumbwidth', 'thumbheight','iwidth', 'iheight', 'tid' ], 'prefetch' => [ { 'imageaoi' => 'aoi', }, { 'note' => 'gracenote', }, ], } ) ]; And the generated sql is (slightly formatted from the debug dump) SELECT me.id, me.filename, me.upload_group_id, me.create_timestamp, me.del, blobs.width, blobs.height, blobs_2.width, blobs_2.height, images_transforms_blobs.transform_id, imageaoi.image_id, imageaoi.aoi_id, aoi.aoi_id, aoi.aleft, aoi.atop, aoi.awidth, aoi.aheight, note.note_id, note.image_id, gracenote.id, gracenote.image_id, gracenote.title, gracenote.comment, gracenote.del FROM images me LEFT JOIN images_transforms_blobs images_transforms_blobs ON ( images_transforms_blobs.image_id = me.id ) LEFT JOIN blobs blobs ON ( blobs.id = images_transforms_blobs.blob_id ) LEFT JOIN images_transforms_blobs images_transforms_blobs_2 ON ( images_transforms_blobs_2.image_id = me.id ) LEFT JOIN blobs blobs_2 ON ( blobs_2.id = images_transforms_blobs_2.blob_id ) LEFT JOIN image_aoi imageaoi ON ( imageaoi.image_id = me.id ) LEFT JOIN aoi aoi ON ( aoi.aoi_id = imageaoi.aoi_id ) LEFT JOIN image_note note ON ( note.image_id = me.id ) LEFT JOIN grace_notes gracenote ON ( gracenote.id = note.note_id ) WHERE ( images_transforms_blobs.transform_id = ? AND images_transforms_blobs_2.transform_id > ? AND upload_group_id = ? ) ORDER BY create_timestamp : '2', '4', '1163' Very cool, and no need to hack the 'from' parameter option. Cheers, James -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk