For a Magento 2 project I’m working on, it is a requirement to implement a custom field in the admin panel. With this basic input field it shall be possible to manually set the publishing date of a post in the build-in blog system. This implementation however, led into the presence of the two columns
eero_custom_date in the corresponding database table respectively.
This solution introducted a new question to solve: How to evaluate the order of the blog posts in the post-overview page, with two dates present instead of the default one? This article will highlight the steps I’ve taken to solve this issue.
Building the necessary SQL
Starting off, I’ve had the
posts.phtml file to work with, where the post-overview page is implemented. Right at the beginning of this file, the initialisation of the collection is present:
So the following statement was added to highlight the SQL-Query to get a starting point for the further development:
This resulted in in the output of
This basically is a rather simple SQL to work with. Now the requirement is to distinguish between posts with custom dates and those without. If a post is in the possession of a value in the
eero_custom_date field, this value should be taken instead if the default
created_at one. Otherwise the default one is used, when
eero_custom_date is invalid or simply
NULL. The required bahaviour basically screams short-circuit operator which is implemented in SQL in Form of the
COALESCE() Statement. From this, the Statement
COALESCE(eero_custom_date, created_at) is derived: If
eero_custom_date is valid take this, otherwise use
Now that we have implemented a unified sorting field for the blog posts, it is now possible to actually sort the results with a simple
ORDER BY eero_sort_date DESC.
Now that the basis of the SQL is standing, we can proceed with the implementation in the Magento 2 environment.
Implementing in Magento 2
The Line of
can be left unmodified, as it serves as a starting point for further modifications in the next steps.
To allow the extension of the
SELECT Statement with the added
COALESCE() in the next line, we can use:
To implement the final Ordering we can use:
And to summarise the complete code base to implement a custom SQL Field in order to Sort a Magento 2 Collection:
The solution then disembogues in the following Result: