[Dbix-class] is joining a table twice with nesting possible?

[James Marca]

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.