Support archive

Low Alphabet with multiple orderby/sort

dguy 6 Feb 2013 20:02 question, complete

I'm loving Low Alphabet but have a sorting problem. Basically, I have the list sorting by title and then a custom date field. I can get the title sorting working fine but the second field doesn't seem to have any effect. I'm not sure if the date field needs formatting of some sort to work this way.
Here's the code:

{exp:low_alphabet:entries channel="history" orderby="title|transcript_date" sort="asc|desc" numbers="after" group_numbers="yes" numbers_label="#" alpha_filter="{segment_2}" disable="categories|member_data|pagination" parse="inward" } 
       ... <a href="#">{title}</a>  {transcript_date format='%F %d, %Y'}... 
 {/exp:low_alphabet:entries} 
 

Replies

1. Low 7 Feb 2013 09:58

   Hmm, it should work. What version are you on? And what type is the date field; a native date field?

2. dguy 7 Feb 2013 13:52

   Thanks for the reply, Low! I just updated to 1.0.9 (from 1.0.8) but the problem still exists. I'm using EE 2.5.5 with a native date field.
   
   The odd thing is that it works most of the time as it should. There are just a few areas where it doesn't.
   Here's an example of one that isn't working:
   Harber, Charlie March 27, 2002
   Harber, Charlie May 04, 2000
   Harber, Charlie May 26, 2000
   
   And one where it IS working right:
   Kennedy, Bettie September 12, 2001
   Kennedy, Bettie October 01, 2001
   Kennedy, Bettie February 11, 2002
   
   I've double checked the names to make sure they are exactly the same.

3. Low 7 Feb 2013 14:09

   I've checked with the exact same dates as above, but can't seem to replicate.
   
   Are you sure there aren't any trailing spaces in the names?
   
   Otherwise I might have to take a look myself. Any chance you can send me superadmin login credentials to hi at gotolow dot com?

4. dguy 7 Feb 2013 14:10

   If I change the orderly to orderby="url_title|transcript_date" it works. So I'm wondering if the name with a comma is throwing it off sometime. If we just use the url_title sorting we'd probably end up having to edit each entry so this isn't a good solution.

5. Low 7 Feb 2013 14:25

   Ah, I think I found it. The dates are stored as timestamps (a big number) but stored in the DB as a string. That means they're ordered as strings, not integers, so:
   
   1
   10
   2
   3
   31
   
   etc.
   
   instead of
   
   1
   2
   3
   10
   31
   
   And right between 2000 and 2002 the timestamp went from 9 to 10 digits, causing the sorting algorithm to sort it the way it does.
   
   Possible solutions are installing DropDate and saving the data as YYYYMMDD, which will always sort correctly -- I could come up with a MySQL statement that changes all existing to that format.
   
   Otherwise I need to update Low Alphabet to take native date fields like this into account.

6. dguy 7 Feb 2013 14:32

   Thanks so much for looking into that, Low. And btw, I did check for spaces but didn't see any.
   
   Do you know if DropDate will convert and native Date field without losing data? If so this would work fine. I'll look into it more.

7. Low 7 Feb 2013 14:36

   I'm not sure it does, but if it doesn't, an SQL query won't be hard to write. Something like...
   
   

   UPDATE exp_channel_data SET field_id_XX = FROM_UNIXTIME(field_id_XX, '%Y%m%d');

   
   
   ...where XX is the field ID.

8. Low 7 Feb 2013 14:43

   Actually, this query would be slightly better:
   
   

   UPDATE exp_channel_data SET field_id_XX = FROM_UNIXTIME(field_id_XX, '%Y%m%d') WHERE field_id_XX != '';

   
   
   ...and always remember to backup the DB first.

9. dguy 7 Feb 2013 15:55

   Eureka! This worked flawlessly. I had to use your mysql to convert the data. Now the sorting is exactly right everywhere. Thank you so much, Low! Love your add ons and your fantastic support!

10. Low 7 Feb 2013 16:07

    Cool, glad it worked out!

11. dguy 8 Feb 2013 17:37

    Well I spoke to soon. Although the conversion worked we have a new problem.
    
    When we go back to the entry and save it the date gets reset to some default UNIX time (1970) in the database. Going back to the entry shows no date selected. When I first setup the field it had 2 options UNIX Timestamp and YYYYMMDD (I selected the latter). I know you didn't create this fieldtype but if you have any ideas I would appreciate it. I'll be glad to give you access to look at it too.

12. dguy 8 Feb 2013 18:17

    It looks like this fieldtype write a format like this to the database:
    20130101UTC1800
    
    but what we have is:
    20130101
    
    Do you know of a way to append UTC1200 to the end?
    
    I'm still trying to figure out why new entries still don't show the date correctly.

13. Low 8 Feb 2013 19:51

    Ah, DropDate must have updated since I last saw it. I think a query like this should help:
    
    

    UPDATE exp_channel_data SET field_id_XX = CONCAT(field_id_XX, 'UTC1200') WHERE LENGTH(field_id_XX) = 8;

    
    
    ...where XX is the field ID again.
    
    And backup again to be sure.

14. dguy 8 Feb 2013 21:08

    Well I did this and then all the dates reset to 1970 on the front end. So I reverted to the backup.
    
    I tried to contact the developer for DropDate but I don't know if I'll get a reply.
    
    Is this something you might be able to fix? I would be glad to pay for a solution.

15. Low 8 Feb 2013 22:22

    Sure, I can take a look.
    
    You can send superadmin login credentials to hi at gotolow dot com.