Answered Gallery component only showing 50% of gallery items? (v1.5.2 on MODX 2.2.6-advanced)

- ☆ A M B ☆
- 1,056 Posts
pixelchutes Reply #1, 13 years, 9 months ago

Description of Problem:
This is very weird... After Batch uploading many images into Gallery (across multiple albums), I realized that not all images were showing up. In fact, it seems to only show half of what the limit (e.g. Per Page) setting is set to.

For example:
One album with 15 images only shows 5. (Default of 10 per page set). Increasing to 15, then 8 are shown. If I set it to 30, I can only then see all 15 images.

I did not receive any kind of import errors, nothing in the error log, etc. In fact, the Total # next to the pagination is correct when editing the album. Checking the database, I see all 15 images exist. When I monitor the results of the mgr/item/getList call, I can see that the results count is indeed less, so it appears to be a server-side issue.

As a workaround, the only way I can even see/edit all of the images in the Gallery component albums is if I manually set the limit to double the amount in the album. I've never had this issue before. Further, it does not seem to be limited to any one, specific album.

Has anyone else seen this issue before? I am running the latest MODX + Gallery, and have tried re-installing both MODX and the Gallery component, so I'm thinking it may either be a bug or some kind of corruption? (CentOS 5 / PHP 5.3.19 / MySQL 5.5.28)

This question has been answered by pixelchutes. See the first response.
[ed. note: pixelchutes last edited this post 13 years, 9 months ago.]
Mike Reid - www.pixelchutes.com
MODx Ambassador / Contributor
[Module] MultiMedia Manager / [Module] SiteSearch / [Snippet] DocPassword / [Plugin] EditArea / We support FoxyCart
________________________________
Where every pixel matters.
discuss.answer
- ☆ A M B ☆
- 1,056 Posts
pixelchutes Reply #2, 13 years, 9 months ago
Update!

OK! Making some progress here... I did forget to mention that I added multiple tags (at least two, separated by comma) when importing the new images.

On a hunch, I decided to attempt ruling out Gallery "tags" by simply renaming the existing
```
modx_gallery_tags
```
table, and re-creating an empty one manually in the database.

Sure enough all of the images started to show up, as well as functional pagination (based on correct totals), etc. When I change the table back, the problem resurfaces.

I'm still looking into the specific issue related to the tags, starting with the SQL:
```
SELECT GROUP_CONCAT(Tags.tag) FROM '.$this->modx->getTableName('galTag').' AS Tags
```
Edit:

After comparing the results of the getList query against an empty modx_gallery_tags table vs. a populated one, I noticed that the result count was doubled. After some review, I could not see any difference between the duplicated rows, so I applied a GROUP BY `galItem`.`id`, which corrected the issue:

core/components/gallery/processors/mgr/item/getlist.class.php (~Line: 55)
```
public function prepareQueryAfterCount(xPDOQuery $c) {
        $c->select($this->modx->getSelectColumns('galItem','galItem'));
        $c->select(array(
            'AlbumItems.rank',
            'album' => 'Album.id',
            '(
                SELECT GROUP_CONCAT(Tags.tag) FROM '.$this->modx->getTableName('galTag').' AS Tags
                WHERE Tags.item = galItem.id
            ) AS tags'
        ));
        $c->groupBy('id'); // Added by pixelchutes

        return $c;
    }
```
So far so good, everything appears to be working exactly as expected with no obvious errors or side effects.

I have not fully reviewed the original SQL to determine if the duplication was expected, however for those interested, here is the full modified SQL with GROUP BY:
```
SELECT 
    `galItem`.`id`,
    `galItem`.`name`,
    `galItem`.`filename`,
    `galItem`.`description`,
    `galItem`.`mediatype`,
    `galItem`.`url`,
    `galItem`.`createdon`,
    `galItem`.`createdby`,
    `galItem`.`active`,
    `galItem`.`duration`,
    `galItem`.`streamer`,
    `galItem`.`watermark_pos`,
    AlbumItems.rank,
    Album.id AS album,
    (SELECT 
            GROUP_CONCAT(Tags.tag)
        FROM
            `modx_gallery_tags` AS Tags
        WHERE
            Tags.item = galItem.id) AS tags
FROM
    `modx_gallery_items` AS `galItem`
        JOIN
    `modx_gallery_album_items` `AlbumItems` ON (galItem.id = AlbumItems.item
        AND `AlbumItems`.`album` = '7')
        JOIN
    `modx_gallery_albums` `Album` ON Album.id = AlbumItems.album
        LEFT JOIN
    `modx_gallery_tags` `Tags` ON `galItem`.`id` = `Tags`.`item`
GROUP BY `galItem`.`id`;
```
@splittingred, I will submit a PR of https://github.com/pixelchutes/Gallery/commit/8959d3dcc4d0d4f16b29b0b49c20f306aa103bf2 to the Gallery's "develop" branch. [ed. note: pixelchutes last edited this post 13 years, 9 months ago.]
Mike Reid - www.pixelchutes.com
MODx Ambassador / Contributor
[Module] MultiMedia Manager / [Module] SiteSearch / [Snippet] DocPassword / [Plugin] EditArea / We support FoxyCart
________________________________
Where every pixel matters.