orderby for multiple fields doesn’ work correctly

I just tested this locally by recreating your setup and my orderby worked properly, I just used post_title, not t.post_title.

You can check your SQL Query that is being used by adding one of the following URL’s to your page URL with the output:

?pods_debug_sql=1 will output all PodsData::select() queries; ‘select’ queries only run on things like Pods::find() and Pods::fetch() and in relationships for field output
?pods_debug_sql_all=1 will output all queries that go through pods_query() / PodsData::query()

(From our documentation page on Troubleshooting with URL Parameters)

It might help troubleshoot to see what query is actually being created in your case.

Hi.
I’ve found the problem.
Year is stored as date. So for year 2013 it was 2013-11-29. (the year was 2013 but the day of import was 27th November 2018).
When someone edited one record the year 2013 stayed but day and month changed after save. So 2013-11-29 became 2013-02-10.
When sorting by year using DESC, so it sorted exactly by the date.

Maybe there is a glitch with saving of dates.
Or if I want to have just year, than change Field type from “Date” to “Plain text”.

Anyway thank you for the tip with ?pods_debug_sql=1.

What are you using for your import? WP All Import is my preferred for making sure dates come in correctly (because you can set the format for the import at the meta level). That’s always going to be a problem with using post meta for date fields, because they really are just longtext in the database. It’s the display/etc. or CAST’ing that makes them act the way you want.

If you want to confirm you always get your dates in the right ‘date’ order, use:
CAST(date_field.meta_value as DATE) DESC

This will route the field through the CAST function on the SQL side. Glad our tip helped!

Yes. I used WP All Import. This is very good one.

My decision was to use “date” format. But I need just year. It means that month and day which are added makes problems with sorting. Records have to have the exact dates from particular year to be sorted by year and then by title correctly.

One solution could by to sort records by year when date is “trimmed” just to year.
I am not sure how to achieve that using PODS.
Like ...'orderby' => 'date(intranet-year.meta_value, Y) DESC, post_title ASC',...
which is bit nonsense but it shows my idea.

Is that possible?

You’d be using DatePart:
https://www.w3schools.com/sql/func_sqlserver_datepart.asp

so something like:

'orderby' => 'DATEPART(year, intranet_year.meta_value) DESC, post_title ASC'

Ahaaa…
DATEPART is not for MySQL/MariaDB.

But there is DATE_FORMAT which works!

'orderby' => 'DATE_FORMAT(intranet-year.meta_value, "%Y") DESC, post_title ASC'

Thanks for your wisdom, aura and everything!

• This reply was modified 5 years, 9 months ago by soulwasted.

Awesome! Didn’t realize you were on MariaDB. I’m glad you found something that worked.

Have an awesome weekend!