Querying channel entries hits memory limit

Question

We have a channel that has about 700 entries but is paginated with a limit of 30 entries. Our client is looking to add a button so users can print out all entries. One of our proposed solutions was to redirect to a stripped down page (no header, footer, etc.) with all of the filtered entries that automatically engages the browser's print method. The problem is if no filters have been applied, Craft can't handle 700 entries (I've tested a bit and 130 seems to be the limit).

How should one go about this? Is upping our memory limit (currently 128M) the only way around this? Would it be better to generate a JSON object on channel entry save that we could then load in the print page's template?

Any suggestions would be appreciated!

EDIT

Here is some additional information. The page is basically just a list of companies. There is a field for the company's name, address, contact name, telephone, email, website, type (category), country (category), and also SEOmatic (which we obviously don't need to display). I haven't actually looked at the tables, though.

The error I am getting:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 79 bytes) in /Library/WebServer/Documents/site/craft/app/fieldtypes/PlainTextFieldType.php on line 59

EDIT 2

Channel: Companies

Field Layout:

• Meta: SEOMatic (don't need to output)
• Company Name: Plain Text
• Company Address: Plain Text
• Company Contact Name: Plain Text
• Company Email: Plain Text
• Company Telephone: Plain Text
• Company Website: Plain Text
• Company Type: Category (there can be MULTIPLE types, doesn't have any fields associated with it)
• Company Country: Category (there can only be ONE type, doesn't have any fields associated with it)

Categories:

• Company Types
• Company Countries

Answer 1

1. Read some pages in the craft docs that Brad linked to get to know the basic structure where to place your files
2. Go to Pluginfactory and create a plugin, you only have to check the "Variables" light switch to generate a PluginNameVariables file
3. Download the files and place them in your craft/app/>>pluginHandle<< folder like it is stated in the docs
4. Install the plugin

5. copy the following code into your craft/plugins/>>pluginHandle<</variables/>>pluginHandle<<Variables.php

   /**
    * @return mixed
    */
   public function getAllEntries(){
       // I want to create the code with the best possible performance, so I would
       // like to avoid using $field = craft()->fields()->getFieldByHandle();
       // and instead inserting the id directly, my ids are 13 and 14
       $countryFieldId = 13;
       $typeFieldId = 14;
       // insert your local here
       $locale = 'de';
       // insert your section handle, for my test system it is news
       $sectionHandle = 'news';
       $dbCommand = craft()->db->createCommand();
       // select all your needed fields, I don't want to loop through your field layout since
       // that would result in multiple queries and I want to have the best possible performance
       $dbCommand->select([
           'country.title as countryTitle',
           'GROUP_CONCAT(type.title) as typeTitle',
           // content is your entry
           'content.title as elementTitle',
           'content.field_body as elementText'
       ]);
       $dbCommand->from('elements as elements');
       $dbCommand->join('elements_i18n as elements_i18n', 'elements_i18n.elementId = elements.id');
       $dbCommand->join('content as content', 'content.elementId = elements.id');
       $dbCommand->join('entries as entries', 'entries.id = elements.id');
       $dbCommand->join('sections as sections', 'sections.id = entries.sectionId');
       $dbCommand->leftJoin('structures as structures', 'structures.id = sections.structureId');
       $dbCommand->leftJoin('structureelements as structureelements', '(structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)');
       $dbCommand->setGroup('elements.id');
   
       $dbCommand->andWhere(array(
           'sections.handle'   => $sectionHandle,
           'elements_i18n.locale'  => $locale,
           'content.locale'  => $locale,
           'elements.enabled'  => 1,
           'elements_i18n.enabled'  => 1,
           // you can check here for entries.expiryDate and these things here as well
       ));
       $dbCommand->order('postDate DESC');
       $dbCommand->setGroup('elements.id');
   
       $dbCommand->leftJoin("relations typeRelation", "typeRelation.sourceId = elements.id");
       $dbCommand->join("relations countryRelation", "countryRelation.sourceId = elements.id");
       $dbCommand->andWhere(array(
           'countryRelation.fieldId' => $countryFieldId
       ));
       $dbCommand->andWhere(array(
           'typeRelation.fieldId' => $typeFieldId
       ));
       $dbCommand->join("content as country", "country.elementId = countryRelation.targetId");
       $dbCommand->join("content as type", "type.elementId = typeRelation.targetId");
   
   
       $elements = $dbCommand->queryAll();
   
       return $elements;
   }
   

6. read the comments, try to follow the code and change the values (change ids, select values, fields and such)

7. in order to fetch the entries in your template you need to

   {% set entries = craft.test.getAllEntries() %}
   {% for entry in entries %}
       <pre>
           {{ dump(entry) }}
       </pre>
       {% set types = entry.typeTitle|split(',') %}
       <pre>
           {{ dump(types) }}
       </pre>
   {% endfor %}
   

please let me know if you need help

Edit
I made some more tests and noticed you won't fetch any entries with no relations. So please change this

$dbCommand->leftJoin("relations typeRelation", "typeRelation.sourceId = elements.id");
$dbCommand->join("content as type", "type.elementId = typeRelation.targetId");
$dbCommand->andWhere(array(
    'typeRelation.fieldId' => $typeFieldId
));

to this

$dbCommand->leftJoin("relations typeRelation", "typeRelation.sourceId = elements.id AND (typeRelation.fieldId = $typeFieldId)");
// join content where the ids match or where the target is null in order to get entries without relation
$dbCommand->leftJoin("content as type", "type.elementId = typeRelation.targetId OR typeRelation.targetId = null ");

And of course add your language param

Answer 2

Assuming you're referring to Craft 2, the EntryModels that get returned to a template do have a significant memory overhead attached to them you're probably running into. Doing the math, even bumping your php.ini memory_limit up to 256M is only going to get your around 260 results coming back with your given fields/configuration.

It's one of the big things we first improved in the upcoming Craft 3.

In the meantime, you've got a couple of options:

1. Paginate the results so you can do them in smaller batches.

2. AJAX/lazy load the results over multiple requests in smaller batches.

3. Write a plugin (https://craftcms.com/docs/plugins) that bypasses Craft's Element/Model loading and queries the database directly and returns only the data you explicitly need back to the template, without all of the model overheard.