0

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

enter image description here

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"

enter image description here

table "slide" enter image description here

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 <core@magentocommerce.com>
 */
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()
    );
}
}
2

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');
  • hmm .. its throwing an error : Unrecognized method 'addFilterToMap()' – fudu Aug 7 '18 at 10:43
  • i have edited code please check – Rutvee Sojitra Aug 7 '18 at 10:54
  • Yes, worked like a charm :) Thanks you very much, can you explain that code which you added? – fudu Aug 7 '18 at 11:25
  • This link will help you to understand about addfilterTomap function magento.stackexchange.com/questions/262/… – Rutvee Sojitra Aug 7 '18 at 11:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.