J Jill sale tops,Yes zili app download latest version uptodown.REGISTER NOW GET FREE 888 PESOS REWARDS!

Resolved berttervoert
(@berttervoert)

10 months, 3 weeks ago
Hi,

I have created a pod which gets sorted on a custom date field. The title of this pod is a number. Now the following has occurred: two entries have been made on the same day and the the pod with the highest number gets shown first, but this should be the other way round; the pod with the lowest number should be shown first.

Now I have found some information that you can sort on multiple fields, but I can’t seem to get that working in my php-template. The format of the query I use is as follows:
```
//  build the query to get al posts for custom post type gedichten
$params = array (
  'orderby' =>  array (   'datum_geschreven' => 'DESC' ),
  'limit'	=> 6,
  'pagination'	=> true,
);
//  run the find
$wdtv_pods = pods ( 'gedicht', $params );
```
The second field on which to sort is ‘title’. How do I put this in the ‘orderby’ part?

Thanks in advance!
Bert

The page I need help with: [log in to see the link]

Viewing 3 replies - 1 through 3 (of 3 total)

Plugin Support Paul Clark
(@pdclark)

10 months, 3 weeks ago
See:
- https://docs.pods.io/code/pods/find/
  The datatype of orderby is a string, not an array.
- https://docs.pods.io/searching-filtering-sorting/sorting/sorting-date-fields-using-cast/
  MySQL requires CAST() to treat text representing a time as a date or date plus time.
- https://www.mysqltutorial.org/mysql-basics/mysql-natural-sorting/
  The syntax is a MySQL WHERE query; what Pods is primarily doing is rewriting the named fields to the appropriate real tables, columns, and necessary JOINs.
Unexpected results are likely occurring because the provided orderby is not valid in several ways: The datatype of input, the datatype of the date, and the naming of the field.

The query you are looking for would likely be:
```
'orderby' => 'CAST( datum_geschreven.meta_value AS DATETIME ) DESC',
```
This alone may resolve the issue, as posts will be sorted by both date and time if time is available in the field.

If the field is only date, and it is desirable for content occurring on the same date to then be sorted in “natural” order of numbered titles, it would be either:
```
'orderby' => "CAST( datum_geschreven.meta_value AS DATE ) DESC, CAST(  REGEXP_SUBSTR( title.meta_value, '^\\d+' ) AS SIGNED ) ASC, REGEXP_SUBSTR( title.meta_value, '\\D$' )",
```
…if by “title” you mean a meta field named “title”.

Or:
```
'orderby' => "CAST( datum_geschreven.meta_value AS DATE ) DESC, CAST( REGEXP_SUBSTR( t.post_title, '^\\d+') AS SIGNED ) ASC, REGEXP_SUBSTR(name, '\\D$')",
```
…if by “title” you mean post_title stored in the wp_posts table.

If that does not get expected results according to your data structure, this would work similarly:
```
'orderby' => 'CAST( datum_geschreven.meta_value AS DATE ) DESC, title.meta_value ASC',
```
or
```
'orderby' => 'CAST( datum_geschreven.meta_value AS DATE ) DESC, t.post_title ASC',
```
The difference in these approaches is complexity versus accuracy and speed, depending on the nature of your data: e.g., you indicated that the titles are prefixed by a number. Also, the first approach expects a field with DATE and TIME, while the second is only DATE.

If the numbers in the title are only 0 through 9, the last approach will work. However, if a number goes to 10 or 11, one will discover that when a computer is asked to sort text (a string), it will be sorted alphabetically, not numerically.

The third linked article goes into this in detail, but the short story is that sorting numbers stored as text alphabetically will result in something like 1, 10, 11, 2, 20, 21, 3, instead of 1, 2, 3, 10, 11, 20, 21. This is default behavior in almost all programming and database languages: sorting numbers within text in “natural human order” almost always requires specifying to the computer that the number is an integer (or float, etc.), rather than text.

The queries containing REGEXP_SUBSTR are saying, “after sorting by the date, then sort first by any number at the beginning, treating the number at the beginning as a number (integer), not text, then, if there is still sorting to be done, sort by anything in the title that is not a number alphabetically.”
Thread Starter berttervoert
(@berttervoert)

10 months, 3 weeks ago
Hi,

thank you for your answers. I managed to get it working with the following piece of code:
```
'orderby' => 'CAST( datum_geschreven.meta_value AS DATE ) DESC, t.post_title ASC',
```
Thread Starter berttervoert
(@berttervoert)

10 months, 2 weeks ago
After some further reading I found the following piece of code which should work for titles with numbers greater then 9:
```
<span style="color: #a31515;">'CAST( datum_geschreven.meta_value AS DATE ) DESC, CAST( t.post_title AS INT) DESC'</span><span style="color: #000000;">,</span>
```

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘how to let query sort on two fields?’ is closed to new replies.

Tags