I have a relatively simple task which is exposing an issue with efficiency. In that it isn't very efficient and the server cries and quits. Here's the high level overview:

There's a category called "Locations". There are 791 locations. Users have three Category Fields; First Choice, Second Choice, Third Choice. The pick one location in each field. There are 1809 Users.

I need to generate a report that shows how popular each location is (how many Users chose [LOCATION] as their 1st, 2nd, and 3rd choice). A nice simple table that's 4 cells wide and 791 rows deep.

I keep running out of memory and execution time regardless of how high I take each limit. I've tried it in Twig and I've tried it in PHP as a plugin.

My strategy, via creating a Service and a Variable... :

{% set locations = craft.categories.group('locations').limit(null) %}
{% set users     = craft.users.group('members').limit(null) %}

{{ craft.reporting.locationPopularity(locations,users) }}

The ReportsService.php file:

public function locationPopularity($locations = null, $users = null) {
    $response = "";

    foreach($locations as $location) {
        $firstChoiceCount = 0;
        $secondChoiceCount = 0;
        $thirdChoiceCount = 0;

        foreach($users as $user) {
            if($user->firstChoiceLocation->last() == $location){ $firstChoiceCount += 1; }
            if($user->secondChoiceLocation->last() == $location){ $secondChoiceCount += 1; }
            if($user->thirdChoiceLocation->last() == $location){ $thirdChoiceCount += 1; }

        $response = $response . "
            <p>First Choice: $firstChoiceCount, Second Choice: $secondChoiceCount, Third Choice: $thirdChoiceCount.</p>";

    return $response;

How do I get this to work? 1,430,919 loop iterations seems to be a no go.

The following works, but 2400+ queries...

{% for location in craft.categories.group('locations').limit(null).find() %}
    {% set firstChoiceCount = craft.users.relatedTo({
        element: location,
        field: 'firstChoiceLocation'
    }).limit(null).count() %}

    {% set secondChoiceCount = craft.users.relatedTo({
        element: location,
        field: 'secondChoiceLocation'
    }).limit(null).count() %}

    {% set thirdChoiceCount = craft.users.relatedTo({
        element: location,
        field: 'thirdChoiceLocation'
    }).limit(null).count() %}

    ... output ...
{% endfor %}

First of all ->last() will always produce 2 queries, one fetches the total amount of results, the other one fetches the result with the offset given by the first query -> you can reduce the amount of queries by using first() if there is only one choice.

Furthermore: I would make use of eager loading so you don't even fetch the fields again and again (like currently)

{% set users = craft

Besides that - if you don't execute your $users query Craft might fetch the results again and again in certain circumstances so you don't have an array of elements but a query that is fired every single time

In the end I would highly suggest to not loop each user again and again, instead fetch the number of related users for each category via custom Query. You can just count relations.targetId from the relations table where the sourceIds are your category ids and loop the result within PHP. I could show it for Craft 3 but I didn't use Craft 2 syntax for over a year

  • I tried Eager loading but then the last filter doesn't work in PHP (did in Twig). I use Last because Locations can be nested, and so first pulls only the top level of location, and not the one I care about (the last). I don't follow what you're saying about relations - that approach, when I tried, was awful for performance? It ran a huge relational DB query 791 times? – Matt Wilcox Oct 16 '18 at 14:05
  • It was my understanding that the way I have it here, I run two DB queries (one to get all users, one to get all locations) and everything else is just PHP looping through those results. When I did nested DB query... it was worse than what I have here. – Matt Wilcox Oct 16 '18 at 14:08
  • 1
    Well not exactly craft.categories.group('locations').limit(null) will return a query, not an array of users. Craft will cache it internally but as soon as you loop over this query it is executed. That's why I said you should consider to use eager-loading Furthermore I didn't mean to create full queries via Craft. I mean to create custom queries on Yii/DB level in order to not fetch them every single time you loop over them. Of course the last function (why filter?) won't work since the query is already executed – Robin Schambach Oct 16 '18 at 14:50
  • I see, that makes sense. I had it in my head that what I was getting back was essentially just a couple of hashes I could loop over, but it seems to be some sort of Craft object with associated overheads. I need to learn about the Craft DB and Custom Queries I think. Two areas I know nothing about. – Matt Wilcox Oct 16 '18 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.