Magento 2: Add a new Field to Collections with Custom SQL Columns

Introduction

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 created_at and 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:

$collection = $block->getCollection();

So the following statement was added to highlight the SQL-Query to get a starting point for the further development:

echo( $collection->getSelect() );

This resulted in in the output of

SELECT
	main_table.*
FROM
	blog_posts AS main_table
WHERE
	(status = '1')

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 created_at.

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

$collection = $block->getCollection();

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:

$collection->getSelect()->columns( array("eero_sort_date" => new Zend_Db_Expr("COALESCE(eero_custom_date, created_at)")) );

To implement the final Ordering we can use:

$collection->getSelect()->order("eero_sort_date DESC");

And to summarise the complete code base to implement a custom SQL Field in order to Sort a Magento 2 Collection:

$collection = $block->getCollection();
$collection->getSelect()->columns( array("eero_sort_date" => new Zend_Db_Expr("COALESCE(eero_custom_date, created_at)")) );
$collection->getSelect()->order("eero_sort_date " . $this->getConfig('general_settings/default_sort'));

The solution then disembogues in the following Result:

SELECT
	main_table.*, COALESCE(eero_custom_date, created_at) AS eero_sort_date
FROM
	blog_posts AS main_table
WHERE
	(status = '1')
ORDER BY
	eero_sort_date DESC