Duplicate Collection View Displayorder

[Ghada El-Zoghbi]

Mahara: 19.10.04 ( 2019093018 )
OS: Linux 16.04 (PHP 7.0)
DB: Postgres
Browser: FF / Chrome

In certain situations, the users are able to create a collection where the displayorder of the pages are duplicated. i.e. 2 pages with displayorder = 0 in the collection_view table.

I have no idea how this situation came about. I only saw it in one of our production sites.

For 1 collection with 37 pages, there were:

* 2 pages with displayorder = 0
* 2 pages with displayorder = 1

The remaining pages were ordered correctly starting from 2 onwards.

The result was the error:

'A nonrecoverable error occurred. This probably means you have encountered a bug in the system'

When trying to go to the Pages and Collections screen.

The offending subquery which returned multiple records for a column is in lib/view.php::get_myviews_data() line 3804:

        $collselect = '
            UNION
            SELECT (SELECT view FROM {collection_view} cvid WHERE cvid.collection = c.id AND displayorder = 0) as id,
            null AS vid, c.name as title, c.name AS vtitle, c.description, null as type, c.ctime as vctime, c.mtime as vmtime, c.mtime as vatime,
            c.owner, c.group, c.institution, null as locked, null as ownerformat, null as urlid, null AS vvisits,
                   (SELECT COUNT(*) FROM {collection_view} cv WHERE cv.collection = c.id) AS numviews, c.id AS collid';

As a side note, we really should not be calling subqueries for a column. This has huge performance issues since that subquery will be run for every row.

I also note that there is another subquery to retrieve 'numviews'. Again, another performance hit.

To eliminate the performance hit, the following should be changed:

        $collselect = '
            UNION
            SELECT cvid.view as id,
            null AS vid, c.name as title, c.name AS vtitle, c.description, null as type, c.ctime as vctime, c.mtime as vmtime, c.mtime as vatime,
            c.owner, c.group, c.institution, null as locked, null as ownerformat, null as urlid, null AS vvisits,
            numviews.numviews AS numviews, c.id AS collid';

        $collfrom = '
            FROM {view} v
            LEFT OUTER JOIN {collection_view} cv ON cv.view = v.id
            LEFT OUTER JOIN {collection} c ON cv.collection = c.id
            INNER join {collection_view} cvid on cvid.collection = c.id AND cvid.displayorder = 0
            INNER JOIN (SELECT COUNT(*) as numviews, numcv.collection
                    FROM {collection_view} numcv
                    INNER JOIN {collection} numc ON numcv.collection = numc.id
                    GROUP BY numcv.collection) as numviews on numviews.collection = cv.collection';

and under 'mostcomments':

$collgroupby = ' GROUP BY cvid.view, numviews.numviews, c.id';

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/11384

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/11384
Committed: https://git.mahara.org/mahara/mahara/commit/cb889a0b610452bdb58d95554020c48b724b118f
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit cb889a0b610452bdb58d95554020c48b724b118f
Author: Robert Lyon <email address hidden>
Date: Mon Oct 19 12:03:26 2020 +1300

Bug 1898650: Adjusting the get_myviews_data() function

To make it less of a porformance issue

Change-Id: I31edb6faf1b114893e092e2f6d1cfaee0e67fb4d
Signed-off-by: Robert Lyon <email address hidden>

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/11744