Support » Plugin: WP Data Access » Date ordering altered by display format?

  • Resolved dizwell

    (@dizwell)


    Hello Pete:

    Short version: I have a table with a DATE column; select via SQL ordering by that column and it orders correctly: 01 Jan sorts before 05 Feb, despite ‘J’ coming after ‘F’ in the alphabet.

    I create a publication, ordering by that column, and it also orders correctly. Then I change the WordPress date formatting option, to try and get nicer (‘plain English’) formatting of the dates… and suddenly the publication displays its date results as though it were sorting by text, not date. Thus 8th January sorts before 11th February, which sorts before 23rd May -because 8 sorts before 11, textually.

    The page I link to contains complete information about the situation (though please ask me if you need any clarification).

    Basically, I want 31st January to sort before 1st April to sort before 11th May, and all before the 4th June… yet, by changing the Wordpres *display* format for dates, I seem to alter the way the underlying DATE-type data is being ordered.

    Am I doing something wrong or stupid? Or is there actually an issue here.

    Best regards
    Howard

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

Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Howard,

    Thank you for your detailed description! That helped to rebuild your publication and reproduce the error. I followed your instructions step by step while the ordering remained working fine. Until I disabled “Allow Paging?”… That’s the problem!

    When you show the publication the plugin performs a query. With “Allow Paging?” enabled the plugin performs an additional query whenever you change a page, search or reorder. When you perform a query, your dbms knows this is a date column and orders it as expected. With “Allow Paging?” disabled the plugin does not perform a query on the server. It just reorders the html table in the browser. Since the html table does not know the content is a date column, it orders the content as text.

    A quick solution to fix this would be to enable “Allow Paging?”. Is that possible? Or do you have reasons to keep it disabled?

    There might be a browser solution as well, but I have to do some research to see if it is possible to make it generic for the plugin. See:
    https://datatables.net/blog/2014-12-18

    Thanks,
    Peter

    Thread Starter dizwell

    (@dizwell)

    Thanks Peter: that explanation makes perfect sense. Especially as I know I’ve never previously fiddled with the WordPress default date format… but this was, indeed, ny first ever attempt at turning off pagination!

    I think I’ll be able to turn pagination back on (the reason it was ever off was because of a long and complicated series of experiments, none of which remain valid, but whose after-effects persist!)

    I’m glad the answer turns out to be ‘yes, there’s actually an issue’ *AND* ‘yes, you’re doing it wrong!’ šŸ™‚

    Since the problem is in the way the browser works, I’d say your work here was done, to be fair!

    Best wishes,
    Howard

    Thread Starter dizwell

    (@dizwell)

    Oh: one quick final thought. Would it be possible for the plug-in to consider a ‘Allow Paging’ off to be treated as ‘Switch paging on, but to a row limit of 999999999999999999′, or similar? So that, it is seen as being *effectively* off, in practice, but still actually performs the query on the server which causes dates to be regarded as such?

    The real reason I wanted the pagination off, I think, was simply to see what a table without the “Previous – 1.2.3.4 – Next’ buttons would look like in the footer.

    So then, if your plugin saw the row limit for a query set to some stupidly high special value, it would also need to know to switch of the pagination buttons.

    But… that’s the kind of cludgy workaround I’d knock up. It’s probably not up to your standards!

    Anyway: Vivaldi is looking fine once more, with proper ‘pretty’ dates.

    Thanks again,
    H

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Howard,

    You might consider to use the dom option in advanced table options to turn of pagination. The default dom value is: lfrtip
    Where p represent the pagina control

    To remove the pagination control add this to your advanced table options:
    { "dom": "lfrti" }

    But…. Your default order by gives you the first correct 100 rows. When a user reorders however, the plugin will perform a query over your whole table and you might see unwanted rows.

    You can either filter your table in your default where clause or create a view to filter your table.

    Does that make sense?

    Keep well,
    Peter

    Thread Starter dizwell

    (@dizwell)

    The DOM options (which I can never remember, but I kinda-sorta knew that a string of letters let me switch bits on and off) do the trick, so thanks for that. I doubt anyone much cares what music I listen to, so I don’t imagine the problem of ‘unwanted rows’ is going to be something to worry about, really!

    Thanks for all your help, as ever,
    Howard

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Date ordering altered by display format?’ is closed to new replies.