[BUG] Loading of content in taxonomy pages extremely inefficient

[ToBe998]

The Frontend Controller has a method for taxonomy pages. In this method, it will load the records array for the current taxonomy slug. It does this in a very inefficient way.

Current process This is the current process:

• One Query loads all matching contenttypes and contentids for the current taxonomy
• A Loop goes through each found contentid/type -- the record for this contentid/type is loaded -- the record is hydrated (loading taxonomies, relations for it)

So, if you have a taxonomypage that should display 20 records, it will execute (depending on number of relations) 2-x queries per record, 40 to several hundreds in total. (In my case an article has two taxonomies and two relations. i need to display 36 records, resulting in 180 queries!)

Desired process This is trying to mimic current Bold functionality. With optimized queries (even with Doctrine) you could make this with even less and faster queries.

• One Query loads all matching contenttypes and contentids for the current taxonomy
• All found contentids will be distributes into one id array per contenttype
• A loop goes through all found contenttypes (typically only one) -- All records of this contenttype will be loaded in one request -- All records of this contenttype will be hydrated in one request

In this version, you would have only 2-x queries per found contenttype, not per contentid. This is actually supported by the old storage layer still in use in the Frontend controller. The new storage layer has something similar, probably? in above scenario, this will get us from 180 queries to 5.

Here is a basic code example im using in such a scenario inside an extension (blidly ported to what the Frontend controller does. Untested, but you get the idea):

        // Group ids to fetch by contenttype
        $typedIds = array();
        foreach ($taxrows as $item) {
            $typedIds[$item["contenttype"]][] = $item["content_id"];
        }

        // Get real content from bolt
        $content = array();
        foreach ($typedIds as $type => $ids) {
            $result = $this->app["storage"]->getContent($type, array("id" => implode(" || ", $ids)));
            if ($result) {
                $result = $result instanceof Content ? array($result) : $result; // Bolt returns either array of Content or one single Content depending on number of results -.-

                foreach ($result as $item)
                    $content[] = $item;
            }
        }

Note: This call does change sort order, but the taxonomies don't support sort order anyways now. You can easily sort them according to the id's in $taxrow in a fast array operation if needed, though.

Details

• Relevant Bolt Version: 3.2 - 3.5
• Install type: Composer install
• PHP version: 5.6
• Used web server: Apache

Reproduction

Just open a taxonomy page with many matching records and checkout the executed queries inside the profiler

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. Maybe this issue has been fixed in a recent release, or perhaps it is not affecting a lot of people? It will be closed if no further activity occurs, because we like to keep the issue queue concise and actual. If you think this issue is still relevant, please let us know. Especially if you’d like to help resolve the issue, either by helping us pinpointing the cause of a bug, or in implementing a fix or new feature.

[ToBe998]

Please remove stale. The issue has a big impact and is easy to fix. The solution is already in the ticket.

[SvanteRichter]

@ToBe998 I added the keep label which keeps the stalebot away. Could you please PR your solution so it can be reviewed as such? Thanks!

[ToBe998]

@SahAssar I added a code sample above. There is not a real PR yet as in my experience, my code style is not your desired one. I think it's probably much faster and less effort for both of us if one of you guys adapts the code. Also i don't know how that will fit in with all those storage layer changes.

I can of course create a PR if you think that will work?