Add support for sorting grouped fields for a REST Request

[tdwesten]

I'am are trying to sort the response of a rest request by a grouped field in the data column.

Example url: /api/collections/vacancies/entries?sort=publication_period.publication_start_date

this results in this malfunction sql query:

select
  *
from
  `entries`
where
  `collection` = 'vacancies'
  and `status` = 'published'
order by
  json_unquote(
    json_extract(
      `publication_period`.`data`,
      '$."publication_start_date"'
    )
  ) asc
limit
  50
offset
  0

Error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'publication_period.data' in 'order clause' 

[ryanmitchell]

What do you mean by a grouped field?

[tdwesten]

I mean a nested field like this:

{
   "title":"Docent klas 1",
   "school":"f967796a-4258-402b-ace1-6a240a3ed9a8",
   "short_description":"Vrijeschool Zutphen VO zoekt per direct medewerker managementondersteuning en communicatie voor 28 uur per week. Reageren kan uiterlijk 14 september.",
   "location":"zwolle",
   "starting_immediately":true,
   "start_date":null,
   "education_type":"voortgezet-onderwijs",
   "fte":1,
   "contact_information_title":null,
   "contact_information_content":null,
   "publication_start_date":null,
   "publication_end_date":null,
   "alt_seo_options":null,
   "alt_seo_meta_title":null,
   "alt_seo_meta_description":null,
   "alt_social_options":null,
   "alt_seo_social_title":null,
   "alt_seo_social_description":null,
   "alt_seo_social_image":null,
   "updated_by":"1",
   "starting_date":null,
   "image":null,
   "page_header":{
      "title":"test",
      "introduction":null,
      "image":null
   },
   "page_builder":[

   ],
   "contact_information":{
      "title":null,
      "content":null
   },
   "duplicated_from":"966fa2b6-8053-4a4c-ba49-72c94130c7f5",
   "vacancy_location":"zwolle",
   "function":"leraar",
   "publication_period":{
      "publication_start_date":"2024-03-05", // <- this field!
      "publication_end_date":null
   }
}

[ryanmitchell]

Thanks, it's the dot syntax thats stopping it from working as it will treat that as table_name.column_name.

Try: /api/collections/vacancies/entries?sort=publication_period->publication_start_date

[tdwesten]

Wow! Yeah that works! Thanks!