2

Section handle: players field handle: teamName

The checkbox field has two options:

  • Cubs
  • Tigers

Not all players will have the teamName field checked.

I want to order players who are Cubs or Tigers first and order those players by their postDate desc, while ordering the non Cubs/Tigers players by their postDate desc.

{% set players = craft.entries.section('players').orderBy('teamName, postDate desc').all() %}

This kind of works, but it orders Cubs players before Tigers players, regardless of their postDate, so a Cubs player could of been added 30 days ago and still show up before a Tigers player who was added 2 days ago.

I know the teamName is being ordered, I don't know how to add the postDate desc to the teamName logic. It needs to support pagination.

2

The equivalent SQL would be something like

SELECT * FROM players ORDER BY teamName IS NOT NULL DESC, postDate DESC;

but I'm not sure how that would translate in a simple element query. It'd be something like:

{% set players = craft.entries.section('players').orderBy('teamName IS NOT NULL DESC, postDate desc').all() %}

1

I believe this won't work the way you want it with a single query, especially with reliable performance. The problem is that you actually want 2 different sort criteria being combined.

I would recommend you add an additional field just for sorting using this plugin: https://plugins.craftcms.com/preparse-field

It allows you to use TWIG to prepare a value while saving the entry which can then be used for sorting. With that you can implement some logic to create a field value like this

1-2021-03-03 (has a team)
1-2021-02-28 (has a team)
0-2021-03-01 (has no team)
0-2021-01-19 (has no team)

by using some TWIG code like this (untested):

{{ (element.teamName.count() ? '1' : '0') ~ '-' ~ (element.postDate | date('Y-m-d')) }}
2
  • I made 2 queries of ids, merged those ids then made another query to get those entries in the order of the ids in the ids array, adding fixedOrder() to the query so it respected the order the id of the entry appeared in the ids array. – Brad Apr 16 at 21:06
  • 1
    I thought of this as well, but it won't scale as nice and needs multiple queries. – Matthias Redl-Mann Apr 18 at 16:32

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.