1

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
  • It would be good to know a little bit more about your structure. Normally craft should not have such huge problems with 700 entries unless you created many fields thus your content table is really huge. Do you need the entire entry with all relations and all fields or do you only need some of them? You could eventually thinking about making your custom db query and fetch the raw content+element table record - in order to boost your performance if you only need information like the title, some text fields and string content. – Robin Schambach Nov 14 '17 at 21:31
  • @RobinSchambach I provided more information in my edit. – user2909019 Nov 14 '17 at 22:03
  • Ok now I see, yeah that's a little disadvantage that should be solved with craft 3 if you are not able to lazy load your entries via ajax or something you should write a plugin and load only the fields you need. If you are new to craft and have no idea how to do this I can provide you some basic code to fetch these fields + the related categories which will be much faster – Robin Schambach Nov 14 '17 at 22:15
  • @RobinSchambach looks like both you and Brad seem to be suggesting the same thing. If you don't mind, some basic code would be great! I'm certainly new to Craft plugins. – user2909019 Nov 14 '17 at 22:18
  • Alright, let me get some sleep (it's too late here) then I'll create an answer how to create a simple plugin with a db command that should return all enabled/active entries from a section with the related categories. Are there special values you need from the categories (images, other relations) or just strings (title, name, descriptions and so on) – Robin Schambach Nov 14 '17 at 22:25
2
  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

  • I appreciate all of the time you've spent on this. I have a couple questions. But first, I made a second edit to hopefully provide all the information you'd need. For the $categoryFieldId and $typeFieldId -- would I need to get the IDs for all of my fields (name, address, type, country, etc.)? Is that what those variables are referring to? Or are those two variables referring to my two category fields (type and country)? Is $categoryFieldId supposed to be $countryFieldId? – user2909019 Nov 15 '17 at 17:58
  • I updated the code but in order to get your fields I need the field handles not the field names.. but you could insert them by yourself as well. I provided all you need. $typeFieldId is the id of your relationField, you need it in order to fetch the related elements from those fields. Everything else is in the craft_content table that's why I asked for your field types. relations are not in the craft_content table – Robin Schambach Nov 15 '17 at 20:13
  • This worked great! The only thing that was a little weird was that type was outputting duplicates of its title and also its title in all different languages. I ended up adding DISTINCT to 'GROUP_CONCAT(DISTINCT type.title) as typeTitle' and an AND condition to $query->join("content as type", "type.elementId = typeRelation.targetId AND type.locale = '$locale'");. This seems to work but was just wondering if this seems correct? Thanks! – user2909019 Nov 15 '17 at 23:57
  • Sry for responding so late. I noticed another bug yesterday and it took some time to fix it properly. With my first code you won't be able to fetch entries without any relations, read my edit to fix it. And indeed you are right, I forgot about the locale. I tried to include every important condition but I forgot that one. Please let me know if the code is fast enough – Robin Schambach Nov 17 '17 at 6:31
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.

  • Thank you for your suggestions. It seems like option 3 is the way to go. We're already implementing option 1 on our normal page, and that's the issue our client is trying to get around (option 2 would be similar). So could I just store that data in a JSON file or something that updates every time a user edits or creates a new entry? Would I then just pass that data to the template? – user2909019 Nov 14 '17 at 22:23
  • You could store it statically and update it as needed. If it's updated often, might just be easier to return the massaged data straight from the database, though. – Brad Bell Nov 14 '17 at 23:03

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.