Sorting by pub_date AND publishedon

Hi guys

I’ve got a scenario where I’ll be adding some articles to a site with dates from the past (old articles on a new site y’see).

When I write new articles, I just hit Save and the site orders the articles on the publishedon field as usual. For the old articles I’m importing, I really want to use pub_date to set the old date.

The tricky bit is how to make a query that takes into account both fields. I can’t order by pub_date then publishedon unfortunately, as those fields with no pub_date will always come first as pub_date gets set to zero so the newer pages would always appear before the older ones.

Is there a way to compare the two fields in a mySQL query somehow and order by both fields so the dates are in ascending order?

The alternative I worked on was to pull all articles from the DB, loop through the results and put them into an array, setting the publishedon date to teh value of the pub_date field if it was greater than zero, then doing an array_multisort to order the array by date. This works fine, but has the enormous problem of having to pull ALL pages from the database to get the pages sorted by date and make sure you’re not missing any, then do another query to just grab the full content of the latest 10 documents based off this array.

So basically I’d really like a way to not have to pull all the page id’s and dates, create two arrays, do an array_multisort and then do another query. If it’s possible to sort on both fields in one query and be able to set a limit as well then that would save potentially tonnes of overhead (my alternative above is alright for a few hundred articles maybe, but much beyond that is just wasting server resources).

Any help would be appreciated, and I also appreciate it’s not the easiest request

The best method if you want to control the date is to sort by pub_date...I would use a plugin to automatically set the pub_date if it is not set to be the current date. This way when you save a document you will automatically have the date set and you will also be able to manually set the date.

Here is a plugin that does this based on a TV value: http://modxcms.com/forums/index.php/topic,36496.0.html

Now why didn’t I think of that? Having one of those head-scratching days today

And it would be a simple query in phpMyAdmin to set the pub_date value to the publishedon value if the pub_date value is currently 0 for the current articles in the system:



Combine this with document parent ID’s and you could create the plugin to only do this for relevant sections of a site too, thus saving on some unnecessary data in the database.

Thanks very much Shane! Despite the one extra query when saving an article, it will save on potentially tens of thousands of extra queries when tens of thousands of visitors view the "latest articles" pages - a very fair trade-off in my book!

And here’s the plugin. It does have the parent ID’s hardcoded in because I was a bit to lazy to create a config setting for this, but suits my needs:



Checks if the article (document) is in a particular section (child of a particular document - 3 or 4 in this case), then finally checks to make sure the pub_date is empty before setting it’s value to the publishedon value if it is.

Just needs to have OnDocFormSave ticked on the System Events tab to work.

Neato.

Thanks again Shane!

Cool...glad to help...thanks for posting the code