How to dispatch equally entries from different channels?

Question

I have entries from two different channels, where each channel has a different total of entries, and I want to display them equally by batch.

Example:

Channel #1 | Channel #2 | Channel #1
Channel #1 | Channel #1 | Channel #2
Channel #2 | Channel #1 | Channel #1

Assuming here we've got 9 entries, dispatched in 6 entries from Channel #1 and 3 entries from Channel #2.

Requirements:

• I'm using a pagination system with Sprig, so I'm not loading all entries at first load, only by batch of 9 entries: this should be taken in consideration as channel entries should be distributed equally during the pagination.
• If possible, the different entries should be dispatched on each row, so there are not displayed together (as they have different styles)

How can I do that using craft entries query?

Answer 1

You can use one entry query to fetch entries from multiple sections:

{% set entries = craft.entries()
    .section(['channel_one', 'channel_two'])
    .orderBy('postDate DESC')
    .limit(9)
    .all()
%}

By using a single entry query that fetches entries from both channels, you can use limit and offset normally. The only requirement is that they are sorted by one shared field (like the native postDate property).

For the display in rows and columns, I would just output a single list and display them using CSS Grid:

<ul class="entry-feed">
    {% for entry in entries %}
        <li class="entry-feed__item entry-feed__item--{{ entry.section.handle }}">
            {{ entry.title }}
        </li>
    {% endfor %}
</ul>

If you need to style the entries differently, this includes separate classes for both channels (i.e. entry-feed__item--channel_one). If you need to output different information for each channel, you can alter the display depending on the section:

{% switch entry.section.handle %}
    {% case "channel_one" %}
        {{ entry.field_one }}
    {% case "channel_two" %}
        {{ entry.field_two }}
{% endswitch %}

Fixed number of entries per channel per page

If you need a fixed number of entries per channel on each page (always 6 entries from channel one and 3 entries from channel 2), you probably need two separate queries. You can't use the paginate tag in this case, you'll have to do the pagination yourself by applying both a limit and an offset. For example:

{% set pageNumber = craft.app.request.pageNum ?? 1 %}
{% set channelOneEntries = craft.entries()
    .section('channel_one')
    .orderBy('postDate DESC')
    .limit(6)
    .offset(pageNum - 1) * 6)
    .all()
%}
{% set channelTwoEntries = craft.entries()
    .section('channel_two')
    .orderBy('postDate DESC')
    .limit(3)
    .offset(pageNum - 1) * 3)
    .all()
%}

Then you can mix those together into a single array you can loop over. How you do that depends on the desired outcome, i.e. what criteria you want the entries on a single page to be sorted by. For example, if you want a fixed position where the entries from channel two are always at indexes 1, 5 and 6:

{% set entriesForCurrentPage = [
    channelOneEntries[0] ?? null,
    channelTwoEntries[0] ?? null,
    channelOneEntries[1] ?? null,
    channelOneEntries[2] ?? null,
    channelOneEntries[3] ?? null,
    channelTwoEntries[1] ?? null,
    channelTwoEntries[2] ?? null,
    channelOneEntries[4] ?? null,
    channelOneEntries[5] ?? null,
]|filter(v => v is not empty) %}

Of course, there are different ways to join the arrays, for example just mixing the entries randomly. This depends on your requirements.

Simple solutions for hard problems

You're looking for a simple solution to a complex problem – it just doesn't exist.

I'm still not sure for the sort order, but I guess we can define it later using .orderBy()

You can't if you're using your fixedOrder approach. In this case, the order is fixed.

Isn't it possible to first get all ids (using .ids()) whatever the number of pages, order them as expected, then paginate them using .id(arrayOfIds).fixedOrder()

Now your problem becomes recursive: To figure out what to order the query by, you want to order the items manually and then tell the query the order. But you still don't know exactly how to order your items. In your example, the entries from channel two were in positions 2, 6, and 7. But why that order? Is the order based on the post date, fixed, random? Figure out how your entries are supposed to be ordered, and you will know what to put in your query.

I don't know how to get the best ratio and how to deal with decimal fractions while sorting the entries by batches. Your second example best match the problem but is using fixed number while I'm looking for an automatic ratio based on available totals. Also, I would need to make it works using one query, as I'm using pagination with Sprig.

You can't have it all for free. You want a custom sort order with no single field being responsible for ordering the results, but you also want it to work nicely with paginated database queries and without having to do lots of complicated maths and array splitting. That's just not how structured queries work.

Sure, your fixed order approach might work (I'm not even sure if pagination works with fixedOrder, but that's beside the point). But if you have already figured out the exact order of all entries across all pages, why do you need another query?

In my opinion, you have two options:

1. Write some complex logic to merge two sets of results (entries from channel one and two) based on your sorting rules. This will involve using the cardinality ratio to figure out how many items from both channels should go on each page, then writing a custom sorting algorithm to splice both arrays together in that way. That's definitely a lot of work, there's no single query parameter or simple function you can use to get this done, and I doubt someone here will be motivated do that work for you.
2. Challenge the requirements. Which leads me to …

Frame challenge

In my experience, situations like this usually arise if you haven't sufficiently understood or challenged the requirements. For example, you haven't figured out how items should be sorted beyond the channel distribution. This is a sign that even if you somehow get this working, it will result in bad UX. If you don't know how entries are sorted, how will your visitors understand why entries are displayed in that particular order? Something important could be hiding on page 19, but why?

It's very likely that your client expects something regarding the order that entries are presented in. Maybe they expect to be able to manually arrange which items should go on what page and in what order? In which case, you can either add a custom field for the sorting order or a global set with an entries field and use the order from that. Or maybe the client expects that the newest entries will be displayed first? In that case, you're back to ordering by the postDate.

In both cases, questioning and challenging the requirements will lead to a simpler and better, more robust solution. And don't assume the client always knows their exact requirements, sometimes a request doesn't make sense. For example, maybe the client insists that items from both channels should be equally distributed across pages, but also wants items to be ordered chronologically (or manually). But this doesn't make sense – not because SQL doesn't support it, but because it doesn't work logically.

Anyway, my recommendation would be to go back to the drawing board and figure out the motivation behind the feature you're building. What items are important and should be on page one? Why is item #1 in position one and item #5 in position five? Why are the items from channel two in positions 2, 6 and 7? Who makes that decision? Is it a manual or automatic process? Discuss these questions with your client, and I'm sure a simpler and better solution will present itself.

Answer 2

Here's how to prepare a Craft query that merge two channels of entries by distribute values evenly and setup the pagination system (with Sprig):

{% set channelOne = craft.entries.section('channelOne').orderBy('title DESC').limit(null).ids() %}
{% set channelTwo = craft.entries.section('channelTwo').orderBy('title DESC').limit(null).ids() %}

{% set longest = (channelOne | length > channelTwo | length) ? channelOne : channelTwo %}
{% set shortest = (channelOne | length > channelTwo | length) ? channelTwo : channelOne %}
{% set ratio = (longest | length / shortest | length) | round(0, 'floor' %}
{% set ids = [] %}

{% if shortest is not empty %}
    {% for i in 0..(shortest | length - 1) %}
        {% for j in 0..ratio %}
            {% set ids = ids | merge([longest[i * ratio + j]]) %}
        {% endfor %}
        {% set ids = ids | merge([shortest[i]]) %}
    {% endfor %}
    {% for i in (longest | length - ((shortest is not empty) ? longest | length % shortest | length : longest | length))..(longest | length) %}
        {% set ids = ids | merge([longest[i - 1]]) %}
    {% endfor %}
{% else %}
    {% set ids = longest %}
{% endif %}

{% set entryQuery = craft.entries.id(ids | unique).fixedOrder().offset(offset).limit(limit) %}
{% set entries = entryQuery.all() %}

Downside:

• Because the ratio is not always a round number, you could see an unexpected batch of few entries at the end (from the longest entries)

What could be improved:

• Refactore the Twig code for better readality and performance (as suggested by @MoritzLost: write it in PHP and stick inside a Twig extension in a custom module that provides as a custom filter or function)
• Eventually, and depending on the design, improve a little bit the display using CSS grid and/or JS if you would like to cheat the position in each row