<p>First of all <code>-&gt;last()</code> 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 <code>first()</code> if there is only one choice.</p>

<p>Furthermore: I would make use of <a href="https://docs.craftcms.com/v2/templating/eager-loading-elements.html" rel="nofollow noreferrer">eager loading</a> so you don't even fetch the fields again and again (like currently)</p>

<pre class="lang-TWIG prettyprint-override"><code>{% set users = craft
    .users
    .group('members')
    .limit(null)
    .with([
        'firstChoiceLocation', 
        'secondChoiceLocation', 
        'thirdChoiceLocation'
    ])
    .find() 
%}
</code></pre>

<p>Besides that - if you don't execute your <code>$users</code> 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</p>

<p>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 <code>count</code> <code>relations.targetId</code> 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</p>

CC BY-SA 4.0
2018-10-16T14:05:42.160Z
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
CC BY-SA 4.0
2018-10-16T14:08:04.910Z
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
CC BY-SA 4.0
2018-10-16T14:50:12.047Z
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
CC BY-SA 4.0
2018-10-16T15:04:24.260Z
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
CC BY-SA 4.0
2018-10-16T13:25:16.723Z
2018-10-16T13:33:33.000Z
2018-10-16T13:33:33.000Z
Robin Schambach
<p>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:</p>

<p>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.</p>

<p>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.</p>

<p>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.</p>

<p>My strategy, via creating a Service and a Variable... :</p>

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

{{ craft.reporting.locationPopularity(locations,users) }}
</code></pre>

<p>The ReportsService.php file:</p>

<pre><code>public function locationPopularity($locations = null, $users = null) {
    $response = "";

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

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

        $response = $response . "
            &lt;h2&gt;$location-&gt;title&lt;/h2&gt;
            &lt;p&gt;First Choice: $firstChoiceCount, Second Choice: $secondChoiceCount, Third Choice: $thirdChoiceCount.&lt;/p&gt;";
    }

    return $response;
}
</code></pre>

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

<p>The following works, but 2400+ queries...</p>

<pre><code>{% 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 %}
</code></pre>

<plugin-development>
Reducing memory usage?
# Reducing memory usage?

(Asked by: username_0 on 2018-10-16)

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 . "
            $location->title
            First Choice: $firstChoiceCount, Second Choice: $secondChoiceCount, Third Choice: $thirdChoiceCount.";
    }

    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 %}

**Tags:** plugin-development


---

## 1 answers

### Answer by username_1 on 2018-10-16. Score: 1

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
    .users
    .group('members')
    .limit(null)
    .with([
        'firstChoiceLocation', 
        'secondChoiceLocation', 
        'thirdChoiceLocation'
    ])
    .find() 
%}

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

> **Comments:**
> **username_0**: 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?  
> **username_0**: 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.  
> **username_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  
> **username_0**: 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.  

---

