Need help with extremely custom embed in ModX

Hello,

I'm using Gallery2 inside ModX using a custom 'snippet' (ie. PHP code) to interface with the Gallery2 Database from within ModX. Currently, it executes some SQL against the g2_* tables to get a list of albums along with thumbnails, and a list of items inside the gallery, along with thumbnails.

The query I came up with works great for the Album listing and even works ok for listing items in the albums. The problem comes when I use the gallery2 software to re-scale photos or add watermarks and such. I notice these become 'derivative' images, and I can't for the life of me come up with a single query that would account for this and return me the g2_itemId that corresponds to the latest derivative image.

I'm hoping that someone here has more insight into the database and could provide me with an updated query that would do what I need. Below is the class and code I use to get the info from Gallery2 (relevant sections for brevity). Apologies in advance for style or PHP ignorance, I'm mainly a Perl developer

this is the URL to display the photo of an image inside an album, this is where I can't get it to show the latest version. putting an & at the end at least shows the right size, but wont show the watermark

$gal2_album_photos_url = '/gallery2/main.php?g2_view=core.DownloadItem&g2_itemId=%s&';

This method returns the actual g_id's of images inside the album (taking g_id of album as parameter). This must be the query that needs modification so it will return the g_id/serialNumber of the latest derivative image

        function getAlbumPhotoIds( $g_id, $offset, $limit )
        {
            global $modx;

            $sql_count =<<<SQL
SELECT
       count(1) AS count
FROM
     g2_ChildEntity c
     JOIN g2_Entity e USING ( g_id )
WHERE
        e.g_entityType = 'GalleryPhotoItem'
AND     c.g_parentId IN ( $g_id )
SQL;
                      $photo_count = Gallery2::prepareDBResults( $modx->db->query( $sql_count ));

            $sql =<<<SQL
SELECT
       e.g_id AS g_id,
       e.g_serialNumber AS g_serialNumber
FROM
     g2_ChildEntity c
     JOIN g2_Entity e USING ( g_id )
     JOIN g2_ItemAttributesMap ia ON ia.g_itemId= e.g_id
WHERE
        e.g_entityType = 'GalleryPhotoItem'
AND     c.g_parentId IN ( $g_id )
ORDER BY ia.g_orderWeight
LIMIT $limit
OFFSET $offset
SQL;
    
            $album_photos = Gallery2::prepareDBResults( $modx->db->query($sql) );
            return array($album_photos, $photo_count[0]['count'] );
        }

Show the photos in the album here. Shows the thumbnails of the photos fine, but when I click on the generated link using $gal2_album_photos_url and the 'g_id' returned from getAlbumPhotoIds, it's not showing the latest version of the photo (re-scaled and watermarked)

else
{    
    # show the photos in the album
    list($album_photos, $photo_count) = Gallery2::getAlbumPhotoIds( $album_id, $offset, $limit );
    $output .= '<div id="photos" title="' . $photo_count  .'">';
    foreach ( $album_photos as $photo )
    {
        $photo_thumbnail = Gallery2::getPhotoThumbnailId( $photo['g_id'] );
        
        $output .= '<div class="thumbnail"><a href="' . sprintf($gal2_album_photos_url, $photo['g_id']) . '" onClick="' . $photo_url_pre . sprintf($gal2_album_photos_url, $photo['g_id']) . $photo_url_post .'"><img class="thumbnail_image" src="' . sprintf( $gal2_album_photos_url, $photo_thumbnail[0]['g_id']) . '"></a></div>';
    }
    $output .= "</div>";
}

To see it in action, the demo site is up at http://bn.mauvaisgarcons.net - and then click on 'Paparazzi'. The gallery2 url is http://bn.mauvaisgarcons.net/gallery2

Sweeet, thanks for the hint. I was able to get it to work properly by embedding the G2 completely using embed.php (before i was just hitting the database), and then calling

fetchThumbnailsByItemIds, and passing in the thumb id. From there I could access the object for that Item, and use the derivativeSourceId property to get it to show the latest version of the photo.