Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-10-16T14:05:42.160Z
Answers.Text: 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?
Answers.UserDisplayName: Matt Wilcox
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-10-16T14:08:04.910Z
Answers.Text: 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.
Answers.UserDisplayName: Matt Wilcox
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-10-16T14:50:12.047Z
Answers.Text: 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
Answers.UserDisplayName: Robin Schambach
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-10-16T15:04:24.260Z
Answers.Text: 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.
Answers.UserDisplayName: Matt Wilcox
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-10-16T13:25:16.723Z
Answers.LastActivityDate: 2018-10-16T13:33:33.000Z
Answers.LastEditDate: 2018-10-16T13:33:33.000Z
Answers.UserDisplayName: Robin Schambach
Body: <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>
  
Tags: <plugin-development>
Title: Reducing memory usage?
ThreadText: # 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 . "
              <h2>$location->title</h2>
              <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 %}
  
  ```
  
  **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](https://docs.craftcms.com/v2/templating/eager-loading-elements.html) so you don't even fetch the fields again and again (like currently)
  
  ```TWIG
  {% 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.  
  
  ---
  
  