Sort Product Collection By Event Date Custom field

Question

I have created Events type products using Magento Virtual Product.

A custom product attribute "event_date" has created to mark the event as Active or Past event.

If the event_date field value is a past date then I showed a label "Past Event" with the Product.

We need to show the all active events first then all past events in product listing page

I successfully did that by setting Product Listing Sort = "event_date"

( From admin -> STORES -> Catalog -> Storefront -> Product Listing Sort by configuration)

I also set default sort order to "DESC" using catalog_category_view.xml.

After setting default sort order to "DESC", All Active Events are now coming first then Past events in product listing page.

Now the issue is:

I need to implement another second sorting, So the Active event will be listed by "ASC" order.

For Example :

Event :DemoA , Event Date = January 24, 2019

Event :DemoB , Event Date = January 10, 2019

Here both DemoA, DemoB events are active events as the "event_date" value is not past date but since I set the default sort order to "DESC" Event DemoA (January 24, 2019) comes first then Event DemoB(January 10, 2019 ) comes.

How can I show Event DemoB(January 10, 2019 ) first then Event DemoA(January 24, 2019)

Thanks in advance.

Answer 1

I have solved this by using a plugin for Magento\Catalog\Model\Layer and modifying the Sort Order using CASE syntax as described below.

1) Declare the plugin in di.xml under /app/code/Vendor/Module/etc/

2) create Layer.php under /app/code/Vendor/Module/Model/Plugin/

<?php

namespace Vendor\Module\Model\Plugin;

use Magento\Catalog\Model\Layer as CatalogLayer;
use Magento\Catalog\Model\ResourceModel\Product\Collection as ProductCollection;

class Layer
{


    protected $timezone;

    public function __construct(    
        \Magento\Framework\Stdlib\DateTime\TimezoneInterface $timezone
    ) {     
        $this->timezone = $timezone;
    }

     public function afterGetProductCollection($subject, $collection) 
     {
       $currentDate = date('Y-m-d');              
       $collection->getSelect()->order(
       new \Zend_Db_Expr(
        "CASE WHEN event_date >= '".$currentDate."' THEN event_date ELSE '9999-12-31' END ASC,
         CASE WHEN event_date <  '".$currentDate."' THEN event_date ELSE NULL END DESC")
       );       
       return $collection;
    }   

}