sort by checkbox field with multiple options and by postDate desc

Question

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.

Answer 1

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() %}

Answer 2

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')) }}