Manipulate collections with Magento 2

Remember during the last lesson, we created a collection :

$jobCollection = $this->_job->getCollection()
                ->addFieldToSelect('*')
                ->addFieldToFilter('status', $this->_job->getEnableStatus())
                ->join(
                    array('department' => $this->_department->getResource()->getMainTable()),
                    'main_table.department_id = department.'.$this->_job->getIdFieldName(),
                    array('department_name' => 'name')
                );

We will see what we can do with collections.
Before that, take the file :
app/code/Maxime/Jobs/Block/Job/ListJob.php

And change the code to get the collection :

            $jobCollection = $this->_job->getCollection()->addStatusFilter($this->_job, $this->_department);

You can get the full PHP class here :

<?php
namespace Maxime\Jobs\Block\Job;
class ListJob extends \Magento\Framework\View\Element\Template
{

    protected $_job;

    protected $_department;

    protected $_resource;

    protected $_jobCollection = null;

    /**
     * @param \Magento\Framework\View\Element\Template\Context $context
     * @param \Maxime\Jobs\Model\Job $job
     * @param \Maxime\Jobs\Model\Department $department
     * @param \Magento\Framework\App\ResourceConnection $resource
     * @param array $data
     */
    public function __construct(
        \Magento\Framework\View\Element\Template\Context $context,
        \Maxime\Jobs\Model\Job $job,
        \Maxime\Jobs\Model\Department $department,
        \Magento\Framework\App\ResourceConnection $resource,
        array $data = []
    ) {
        $this->_job = $job;
        $this->_department = $department;
        $this->_resource = $resource;

        parent::__construct(
            $context,
            $data
        );
    }

    /**
     * @return $this
     */
    protected function _prepareLayout()
    {
        parent::_prepareLayout();


        // You can put these informations editable on BO
        $title = __('We are hiring');
        $description = __('Look at the jobs we have got for you');
        $keywords = __('job,hiring');

        $this->getLayout()->createBlock('Magento\Catalog\Block\Breadcrumbs');

        if ($breadcrumbsBlock = $this->getLayout()->getBlock('breadcrumbs')) {
            $breadcrumbsBlock->addCrumb(
                'jobs',
                [
                    'label' => $title,
                    'title' => $title,
                    'link' => false // No link for the last element
                ]
            );
        }

        $this->pageConfig->getTitle()->set($title);
        $this->pageConfig->setDescription($description);
        $this->pageConfig->setKeywords($keywords);


        $pageMainTitle = $this->getLayout()->getBlock('page.main.title');
        if ($pageMainTitle) {
            $pageMainTitle->setPageTitle($title);
        }

        return $this;
    }

    protected function _getJobCollection()
    {
        if ($this->_jobCollection === null) {

            $jobCollection = $this->_job->getCollection()->addStatusFilter($this->_job, $this->_department);

            $this->_jobCollection = $jobCollection;
        }
        return $this->_jobCollection;
    }


    public function getLoadedJobCollection()
    {
        return $this->_getJobCollection();
    }

    public function getJobUrl($job){
        if(!$job->getId()){
            return '#';
        }

        return $this->getUrl('jobs/job/view', ['id' => $job->getId()]);
    }

    public function getDepartmentUrl($job){
        if(!$job->getDepartmentId()){
            return '#';
        }

        return $this->getUrl('jobs/department/view', ['id' => $job->getDepartmentId()]);
    }
}

We will manupilate datas on the Collection class :
app/code/Maxime/Jobs/Model/ResourceModel/Job/Collection.php

Create the following method which will be used to retrieve the collection we want :

public function addStatusFilter($job, $department){
        $this->addFieldToSelect('*')
            ->addFieldToFilter('status', $job->getEnableStatus())
            ->join(
                array('department' => $department->getResource()->getMainTable()),
                'main_table.department_id = department.'.$department->getIdFieldName(),
                array('department_name' => 'name')
            );

        return $this;
    }

I put the objects department and job on paramaters to use their methods.

Show the SQL of the collection

In order to display the SQL of the collection, you can use the following code :

var_dump($this->getSelect().'')

or

var_dump($this->getSelect()->__toString();)

If you go on the jobs’ page on frontend, you will se your request.

Apply filters on collection

You must have notice the addFieldToFilter on our collection, which create a filter on “status” field.

This code :

$this->addFieldToSelect('*')
     ->addFieldToFilter('status', $job->getEnableStatus());

Is the same as this code :

$this->addFieldToSelect('*')
      ->addFieldToFilter('status', array('eq' => $job->getEnableStatus()));

The operator “eq” is not alone, we can put other operators on the array key :

Here is an example of each operator and it’s SQL (it’s just an example, there is no logic on my requests) :

AND contidion with collections

You can add a “AND” condition on your collection SQL, you have to put many addFieldToFilter :

$this->addFieldToSelect('*')
            ->addFieldToFilter('status', array('eq' => $job->getEnableStatus()))
            ->addFieldToFilter('date', array('gt' => date('Y-m-d')));

You will have this SQL : SELECT `main_table`.* FROM `maxime_job` AS `main_table` WHERE (`status` = 1) AND (`date` > '2016-03-01')

You can use the method as many as you want !

OR condition with collections

More difficult to use, the “OR” condition. You cannot make many calls of the method addFieldToFilter because it creates “AND” conditions. So, you have to create a two parts array. The first part will contains the columns, the second will contains the conditions for each column.
Here is an example in order to understand :

$this->addFieldToSelect('*')
            ->addFieldToFilter(
                array(
                    'status',
                    'date'
                ),
                array(
                    array('eq' => $job->getEnableStatus()),
                    array('gt' => date('Y-m-d'))
                )
            );

If you display the SQL, you will see the OR condition :
SELECT `main_table`.* FROM `maxime_job` AS `main_table` WHERE ((`status` = 1) OR (`date` > '2016-03-01'))

It’s simple conditions on only one column, for more complex conditions, you have to use Zend_Select methods. But before to learn that, I will explain you how to make joins.

Joins with collections

We will create a join with departments table.
I want all my “jobs” with the status active, and the department’s name :

$this->addFieldToSelect('*')
            ->addFieldToFilter('status', $job->getEnableStatus())
            ->join(
                array('department' => $department->getResource()->getMainTable()),
                'main_table.department_id = department.'.$department->getIdFieldName(),
                array('department_name' => 'name')
            );

The Join method take 3 parameters :
– The first is the table name we want to join, we retrieve the name with the Magento method getTableName. The array key is the alias table (AS in SQL)
– The second is the join condition, here we put the department ID condition.
– The third is an array of the columns we want to put on our SELECT. If nothing is put, we will have a SELECT * on the department table. The array key is the column alias, the value is the column on the database table. If no key is setted, columns will not be renamed (no “AS” in SQL).

If you show the SQL, you will se an INNER JOIN :
SELECT `main_table`.*, `department`.`name` AS `department_name` FROM `maxime_job` AS `main_table`
INNER JOIN `maxime_department` AS `department` ON main_table.department_id = department.entity_id WHERE (`status` = '1')

We can make other joins, but we have to call the getSelecect() method before the join :

$this->addFieldToSelect('*')
            ->addFieldToFilter('status', $job->getEnableStatus())
            ->getSelect()
            ->joinLeft(
                array('department' => $department->getResource()->getMainTable()),
                'main_table.department_id = department.'.$department->getIdFieldName(),
                array('department_name' => 'name')
            );

The benefit to use the getSelect() on our collection class is we can always return “$this” to return a collection object. If you did the getSelect() on the block, you would not have a collection object, but a Zend_Select object which is more complicated to use than collection on the block.

Complex conditions with collections

Let’s practical !
I want to get all my jobs (with department name) with the conditions :
– Status is equal to 1
– Name is like “%sample%” or it’s date is greater than equal today

The final SQL will be :
SELECT `main_table`.*, `department`.`name` AS `department_name` FROM `maxime_job` AS `main_table`
LEFT JOIN `maxime_department` AS `department` ON main_table.department_id = department.entity_id WHERE (`status` = '1') AND ((`name` LIKE '%Sample%') OR (`date` >= '2016-03-02'))

You did the job ?
Here is what you can do :

$this->addFieldToSelect('*')
            ->addFieldToFilter('status', $job->getEnableStatus())
            ->addFieldToFilter(
                array(
                    'name',
                    'date'
                ),
                array(
                    array('like' => '%Sample%'),
                    array('gteq' => date('Y-m-d'))
                )
            )
            ->getSelect()
            ->joinLeft(
                array('department' => $department->getResource()->getMainTable()),
                'main_table.department_id = department.'.$department->getIdFieldName(),
                array('department_name' => 'name')
            );

Other exercise little bit harder, because there is many “OR” conditions :
– Statut is equal to 1
– Or statut is equal to 0 and the date is greater than equal today
– Or the ID is greater than equal to 0 and, whether the date is less than today, wheter department name is like “%mar%”

Very weird conditions, but it’s an exercise 😉
I give you the final SQL :
SELECT `main_table`.*, `department`.`name` AS `department_name`
FROM `maxime_job` AS `main_table`
LEFT JOIN `maxime_department` AS `department` ON main_table.department_id = department.entity_id
WHERE (main_table.status = 1)
OR (main_table.status = 0 AND main_table.date >= 2016-03-02)
OR (main_table.entity_id > 0 && (main_table.date < 2016-03-02 || department.name LIKE "%mar%"))

We cannot use “where” and “orWhere”, because we have nested conditions.
We have to use these two method for the first level, and fot the other use simple SQL.

Here is the structure of our call :

$this->addFieldToSelect('*')
            ->getSelect()
            ->joinLeft(
                array('department' => $department->getResource()->getMainTable()),
                'main_table.department_id = department.'.$department->getIdFieldName(),
                array('department_name' => 'name')
            )
            ->where(CONDITION1)
            ->orWhere(CONDITION2)
            ->orWhere(CONDITION3);

…
…
…

What did you do ?
I give you my version :

$this->addFieldToSelect('*')
            ->getSelect()
            ->joinLeft(
                array('department' => $department->getResource()->getMainTable()),
                'main_table.department_id = department.'.$department->getIdFieldName(),
                array('department_name' => 'name')
            )
            ->where('main_table.status = ?', $job->getEnableStatus())
            ->orWhere('main_table.status = ? AND main_table.date >= '.date('Y-m-d'), $job->getDisableStatus())
            ->orWhere('main_table.'.$job->getIdFieldName().' > 0 && (main_table.date < '.date('Y-m-d').' || department.name LIKE "%mar%")');

Why we have to do that ? Why we cannot use just SQL requests ?
It is for the SECURITY ! In order to avoid SQL injections, Magento use the method “quoteInto” in order to “secure” the SQL string. Furthermore, we retrieve a collection object, so it’s better to manage datas on the template page.

You can see the Zend_Db_Select class in order to see the available methods inside and make some tests 😉

Before continue, modify the code of this class to erase the tests we did on this lesson :
app/code/Maxime/Jobs/Model/ResourceModel/Job/Collection.php

Here is the full class content :

<?php
namespace Maxime\Jobs\Model\ResourceModel\Job;

use \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;

class Collection extends AbstractCollection
{

    protected $_idFieldName = \Maxime\Jobs\Model\Job::JOB_ID;

    /**
     * Define resource model
     *
     * @return void
     */
    protected function _construct()
    {
        $this->_init('Maxime\Jobs\Model\Job', 'Maxime\Jobs\Model\ResourceModel\Job');
    }

    public function addStatusFilter($job, $department){
        $this->addFieldToSelect('*')
            ->addFieldToFilter('status', $job->getEnableStatus())
            ->join(
                array('department' => $department->getResource()->getMainTable()),
                'main_table.department_id = department.'.$department->getIdFieldName(),
                array('department_name' => 'name')
            );
        
        return $this;
    }
}

On the next lesson, I will explain how to put CSS on your module 🙂