Skip to content

Sort by Date Sent

edited July 2021 in Contributions

If you'd like to be able to sort by date sent, see the code below.

Sorry for the repost; I must not have saved my ticket request properly because I can't find it anywhere now. But I decided instead of trying to open it as a new ticket I would just solve it myself, so here is my solution.

First, the problem. Last May, this ticket mentions that there's no option to sort by date because it already sorts chronologically, which isn't entirely accurate: https://sendy.co/forum/discussion/comment/7501

Currently, the default sort is based on the campaign id, descending. Nice and simple, except that that is the chronological order of the campaign being created, and doesn't reflect when that campaign was actually sent. Which, if you are scheduling campaigns in advance, or creating draft messages, can be a little confusing to find which emails went out in which order...for example: https://www.evernote.com/l/ALuwS416SoVJ0LFZckp1Plw6IYgk_1BCC9AB/image.png
(Note that in this screenshot, it's at least been a month since these were sent; it's much worse when they are all saying 1 week ago etc!)

The other problem is that if you change your sort order, you have to wait for the cookie to expire or manually delete the cookie that tracks how you want to sort in order to get back to chronological order, as sorting by campaign goes alphabetically and not by campaign id.

So, this solution I came up with will address the default sort, which is what I want 99.9% of the time to be the sent date in descending chronological order. In app.php on line 129:

 $q = 'SELECT * FROM campaigns WHERE userID = '.get_app_info('main_userID').' AND app='.get_app_info('app').' ORDER BY id DESC LIMIT '.$offset.','.$limit;

Replace that line with:

 $q = 'SELECT * FROM campaigns WHERE userID = '.get_app_info('main_userID').' AND app='.get_app_info('app').' ORDER BY CASE WHEN sent="" THEN IFNULL(send_date, 9900000000+id) ELSE sent END DESC LIMIT '.$offset.','.$limit;

What this will do is sort any draft messages first that haven't been sent yet, then chronologically will list all sent and scheduled campaigns based on the date they are scheduled to be sent or the date that they actually were sent.

Note that the first time you do this, if you are like us, you'll have a lot of drafts that never got sent to delete!

The problems with this solution as is:

  1. You can't sort by another column and then return to this sort without deleting the sendy sorting cookie in your browser.
  2. You can't sort chronologically earliest to latest (though I can't honestly think of why you would want to?)
  3. You have to change that line every time Sendy updates.

There is a solution to the third comment, I really should post my replacement scripts code one of these days (basically, I've setup a system where I can update something on the page before rendering it, or change code itself and have it eval(), from a custom directory, so that I can update Sendy without losing my customizations.) If anybody has implemented something similar, here's my actual function I'm using since there's a lot of escaped characters:

 function sortBySent($buffer)
 {
     $original = '$q = \'SELECT * FROM campaigns WHERE userID = \'.get_app_info(\'main_userID\').\' AND app=\'.get_app_info(\'app\').\' ORDER BY id DESC LIMIT \'.$offset.\',\'.$limit;';
     $new = '$q = \'SELECT * FROM campaigns WHERE userID = \'.get_app_info(\'main_userID\').\' AND app=\'.get_app_info(\'app\').\' ORDER BY CASE WHEN sent="" THEN IFNULL(send_date, 9900000000+id) ELSE sent END DESC LIMIT \'.$offset.\',\'.$limit;';
     // echo "<!--\n\n$original\n\n$new\n\n-->";
     $buffer = str_replace($original, $new, $buffer);
     return $buffer;    
 }

Comments

This discussion has been closed.