Magento 2.2.5: How to alter main_table before a column

Question

I'm doing "Banner Slider" in magento, here is what i've done til now:

Every thing was just great, but when i try to search with created_at (Created Time), it throw this error when i'm debugging.

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous, query was: SELECT COUNT(*) FROM slide AS main_table INNER JOIN banner_slide ON main_table.id = banner_slide.slide_id WHERE (banner_id = '1') AND (main_table.id IN('1', '2', '3')) AND (created_at <= '2018-08-07 23:59:59')

I know the error appear because i have 2 table that have "created_at" column (take a look at my table down there). So how to solve this is alter "main_table" before "created_at" in that SQL query. But i dont know how to do that.

table "banner_slide"

table "slide"

So here is my code:

C:\xampp\htdocs\magento\app\code\Aht\BannerSlider\Block\Adminhtml\Banner\Tab\Slide.php

<?php
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */

/**
 * Slide in banner grid
 *
 * @author      Magento Core Team <[email protected]>
 */
namespace Aht\BannerSlider\Block\Adminhtml\Banner\Tab;

use Magento\Backend\Block\Widget\Grid;
use Magento\Backend\Block\Widget\Grid\Column;
use Magento\Backend\Block\Widget\Grid\Extended;

class Slide extends \Magento\Backend\Block\Widget\Grid\Extended
{
    /**
     * Core registry
     *
     * @var \Magento\Framework\Registry
     */
    protected $_coreRegistry = null;

    /**
     * @var \Aht\BannerSlider\Model\SlideFactory
     */
    protected $_slideFactory;

    protected $_bannerSlideFactory;

    public function __construct(
        \Magento\Backend\Block\Template\Context $context,
        \Magento\Backend\Helper\Data $backendHelper,
        \Aht\BannerSlider\Model\SlideFactory $slideFactory,
        \Aht\BannerSlider\Model\BannerSlideFactory $bannerSlideFactory,
        \Magento\Framework\Registry $coreRegistry,
        array $data = []
    ) {
        $this->_slideFactory = $slideFactory;
        $this->_bannerSlideFactory = $bannerSlideFactory;
        $this->_coreRegistry = $coreRegistry;
        parent::__construct($context, $backendHelper, $data);
    }

    /**
     * @return void
     */
    protected function _construct()
    {
        parent::_construct();
        $this->setId('banner_slider_banner_slide');
        $this->setDefaultSort('id');
        $this->setUseAjax(true);
    }

    /**
     * @return array|null
     */
    public function getBanner()
    {
        return $this->_coreRegistry->registry('banner');
    }

    /**
     * @param Column $column
     * @return $this
     */
    protected function _addColumnFilterToCollection($column)
    {
        $field = $column->getFilterIndex() ? $column->getFilterIndex() : $column->getIndex();
        // Set custom filter for in category flag
        if ($column->getId() == 'in_banner') {
            $slideIds = $this->_getSelectedSlide();
            if (empty($slideIds)) {
                $slideIds = 0;
            }
            if ($column->getFilter()->getValue()) {
                $this->getCollection()->addFieldToFilter('main_table.id', ['in' => $slideIds]);
            } elseif (!empty($slideIds)) {
                $this->getCollection()->addFieldToFilter('main_table.id', ['nin' => $slideIds]);
            }
        } else {
            parent::_addColumnFilterToCollection($column);
        }
        return $this;
    }

    /**
     * @return Grid
     */
    protected function _prepareCollection()
    {
        $collection = $this->_slideFactory->create()->getCollection();
//            ->addFieldToSelect(array('id', 'name', 'image'));

        if ($this->getBanner()->getId()) {
            $this->setDefaultFilter(['in_banner' => 1]);

//            main_table được lấy tự động.
            $collection->getSelect()
                ->joinInner(['banner_slide' => 'banner_slide'], 'main_table.id = banner_slide.slide_id', ['banner_slide_id' => 'banner_slide.id'])
                ->where('banner_id = ?', $this->getBanner()->getId());
//            hiển thị câu lệnh sql:
//            echo $collection->getSelect();die();
        }


        $this->setCollection($collection);

        return parent::_prepareCollection();
    }

    /**
     * @return Extended
     */
    protected function _prepareColumns()
    {
        $this->addColumn(
            'in_banner',
            [
                'type' => 'checkbox',
                'name' => 'in_banner',
                'values' => $this->_getSelectedSlide(),
                'index' => 'id',
                'header_css_class' => 'col-select col-massaction',
                'column_css_class' => 'col-select col-massaction'
            ]
        );
        $this->addColumn(
            'id',
            [
                'header' => __('ID'),
                'sortable' => true,
                'index' => 'id',
                'header_css_class' => 'col-id',
                'column_css_class' => 'col-id'
            ]
        );
        $this->addColumn('name', ['header' => __('Name'), 'index' => 'name']);
        $this->addColumn('url', ['header' => __('URL'), 'index' => 'url']);
        $this->addColumn('image', ['header' => __('Image'), 'index' => 'image']);

        $this->addColumn(
            'created_at',
            [
                'header' => __('Created Time'),
                'type' => 'datetime',
                'index' => 'created_at'
            ]);
        $this->addColumn(
            'updated_at',
            [
                'header' => __('Last Update'),
                'type' => 'datetime',
                'index' => 'updated_at'
            ]
        );

        return parent::_prepareColumns();
    }

    /**
     * @return string
     */
    public function getGridUrl()
    {
        return $this->getUrl('banner/*/grid', ['_current' => true]);
    }

    /**
     * @return array
     */
    protected function _getSelectedSlide()
    {
        $slide = $this->getRequest()->getPost('selected_slide');
        if ($slide === null) {
            $bannerSlideFactory = $this->_bannerSlideFactory->create()->getCollection();
            $query = $bannerSlideFactory
                ->addFieldToFilter('banner_id',$this->getBanner()->getId())
                ->addFieldToSelect('slide_id');

            $result = array();
            foreach ($query as $rows){
                $slide_id = $rows['slide_id'];
                array_push($result, $slide_id);
            }
            return $result;
        }
        return $slide;
    }
}

C:\xampp\htdocs\magento\app\code\Aht\BannerSlider\Controller\Adminhtml\Manage\Grid.php

<?php
/**
 *
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace Aht\BannerSlider\Controller\Adminhtml\Manage;

use Aht\BannerSlider\Model\BannerFactory;

class Grid extends \Magento\Backend\App\Action
{
    /**
     * @var \Magento\Framework\Controller\Result\RawFactory
     */
    protected $resultRawFactory;

/**
 * @var \Magento\Framework\View\LayoutFactory
 */
protected $layoutFactory;

/**
 * News model factory
 *
 * @var \Aht\BannerSlider\Model\BannerFactory
 */
protected $bannerFactory;

public function __construct(
    \Magento\Backend\App\Action\Context $context,
    \Magento\Framework\Controller\Result\RawFactory $resultRawFactory,
    \Magento\Framework\View\LayoutFactory $layoutFactory,
    BannerFactory $bannerFactory
) {
    parent::__construct($context);
    $this->resultRawFactory = $resultRawFactory;
    $this->layoutFactory = $layoutFactory;
    $this->bannerFactory = $bannerFactory;
}

/**
 * Grid Action
 * Display list of products related to current banner
 *
 * @return \Magento\Framework\Controller\Result\Raw
 */
public function execute()
{
    $id = $this->getRequest()->getParam('id');
    $banner = $this->bannerFactory->create()->load($id);
    $this->_objectManager->get(\Magento\Framework\Registry::class)->register('banner', $banner);
    $this->_objectManager->get(\Magento\Framework\Registry::class)->register('current_banner', $banner);
    if (!$banner) {
        /** @var \Magento\Backend\Model\View\Result\Redirect $resultRedirect */
        $resultRedirect = $this->resultRedirectFactory->create();
        return $resultRedirect->setPath('banner/*/', ['_current' => true, 'id' => null]);
    }
    /** @var \Magento\Framework\Controller\Result\Raw $resultRaw */
    $resultRaw = $this->resultRawFactory->create();
    return $resultRaw->setContents(
        $this->layoutFactory->create()->createBlock(
            \Aht\BannerSlider\Block\Adminhtml\Banner\Tab\Slide::class,
            'banner.slide.grid'
        )->toHtml()
    );
}
}

Answer 1

add

addFilterToMap('created_at', 'main_table.created_at');

In

         $collection->getSelect()
            ->joinInner(['banner_slide' => 'banner_slide'], 'main_table.id = banner_slide.slide_id', ['banner_slide_id' => 'banner_slide.id'])
            ->where('banner_id = ?', $this->getBanner()->getId());
$collection->addFilterToMap('created_at', 'main_table.created_at');