1

Given:

Channel: Nutrition. 550k entries. It has entries field pointing to the "restaurants" channel.

Channel: Restaurants. 300k entries.

I'm trying to display restaurants that have at least one nutrition associated with them. Currently using the following snippet:

                {% set nutritionIds = craft.entries ({
                    section: 'nutrition',
                }).ids() %}

                {% set entries = craft.entries ({
                    section: 'restaurants',
                    orderBy: 'title',
                    limit: 40,
                    relatedTo: {
                        sourceElement: nutritionIds,
                    }
                }) %}

Please help me to optimize this query. Page load time is 50 seconds, memory usage is 250mb. Dedicated server.

1
  • As a last resort, you could write a job that compiles a single optimized database table (or two). A lot of effort but you will bypass all of Craft complexity guaranteeing a very fast query.
    – gioppe
    Sep 21 '21 at 13:57
1

Are the nutrition entries associated with restaurant entries through a single field or multiple fields? If it's only one field and you only want to query for restaurants that have any nutrition associated with them, you don't need to load all the IDs. Instead, you can just use :notempty: to find restaurants where the nutrition entries field is not empty. Assuming your entries field is named nutrition:

{% set restaurants = craft.entries()
    .section('restaurants')
    .orderBy('title')
    .limit(40)
    .nutrition(':notempty:')
    .all()
%}

See Querying Elements with Entries Fields in the documentation.

Now if the relation to nutrition entries is spread across multiple fields, you'll probably have to write a custom where clause to achieve the same result.

2
  • Moritz, entries in the restaurants channel have no relations (entries field) to nutrition. Instead, all entries in the nutrition channel are associated to a restaurant. i.imgur.com/F6knHlU.png
    – Mark H.
    Sep 21 '21 at 13:14
  • @MarkH. Ah I didn't catch that, that's unfortunate. In this case, you could use a custom innerJoin() statement to the relations table to filter out any restaurants that don't have a relation in that field. I've recently done something similar, see the final code example in my question here. If that doesn't work is is still not fast enough, you could give the Reverse Relations plugin a try to be able to filter like that.
    – MoritzLost
    Sep 21 '21 at 15:28

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.