Magento 2 add array to collection in Admin Block Grid

Question

I am trying to add an array to collection in Block Admin Grid but i want able to do that,

 protected function _prepareCollection() {
        //echo "preparecollection function"; exit;
       $collectionlist = array();
       $collection = $this->_collectionFactory->create()
                          ->addAttributeToSelect('cb_exitintentt')
                          ->addNameToSelect()
                          ->addAttributeToSelect('group_id')
                          ->addAttributeToFilter('cb_exitintentt', 1)
                          ->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
                          ->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
                          ->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
                          ->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
                          ->joinAttribute('billing_country_id', 'customer_address/country_id', 'default_billing', null, 'left');

       foreach($collection->getdata() as $cusList ){

            $ordercollection = $this->_orderCollectionFactory->create()
                                   ->addFieldToSelect('*')
                                   ->addFieldToFilter('customer_id',$cusList['entity_id'])
                                   ->setOrder('created_at','desc');
          // $collection->getSelect()->from(null, array('last_order_date' => $data['0']['created_at']));
           $coutOrders = $ordercollection->count();
           if($ordercollection->getData()){
                $data = $ordercollection->getData();
                $sum = array_sum(array_column($data, 'base_grand_total'));
                $lastOrderCreatedDate =  $data['0']['created_at']; //exit;
                $collectionlist[] = array('entity_id' => $cusList['entity_id'],
                                          'email' => $cusList['email'],
                                          'created_at' => $cusList['created_at'],
                                          'group_id' => $cusList['group_id'],
                                          'billing_telephone' => $cusList['billing_telephone'],
                                          'billing_country_id' => $cusList['billing_country_id'],
                                          'billing_region' => $cusList['billing_region'],
                                         );
              // print_r($collectionlist);
           }
       }
    $attr = $collection->getSelect()->from(null, array('last_order_date' => $data['0']['created_at']));
    $this->setCollection($collection);

    return parent::_prepareCollection();
}

This is not working $collection->getSelect()->from(null, array('last_order_date' => $data['0']['created_at'])); — Ramesh
– Ramesh, Commented Sep 1, 2016 at 6:43

Amit Bera · Accepted Answer · 2016-09-01 07:57:02Z

This type of loop code is create for speedup and it does not works.

You need add a custom sql express to your connection

Try below query not sure about this:

protected function _prepareCollection() {
        //echo "preparecollection function"; exit;
       $collectionlist = array();
       $collection = $this->_collectionFactory->create()
                          ->addAttributeToSelect('cb_exitintentt')
                          ->addNameToSelect()
                          ->addAttributeToSelect('group_id')
                          ->addAttributeToFilter('cb_exitintentt', 1)
                          ->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
                          ->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
                          ->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
                          ->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
                          ->joinAttribute('billing_country_id', 'customer_address/country_id', 'default_billing', null, 'left');
    /* create Custom Sql query */
    $sql ='SELECT MAX(o.created_at)'
        . ' FROM sales_order AS o'
        . ' WHERE o.customer_id = e.entity_id ';
    $Orderexpr = new Zend_Db_Expr('(' . $sql . ')'); 

   $collection->getSelect()->from(null, array('last_order_date'=>$expr));

    $this->setCollection($collection);

    return parent::_prepareCollection();
}

check the query by echo $collection->getSelect()->__toString(); before $collection->getSelect()

Also at your xml. you need to add

<column name="last_order_date" class="Magento\Ui\Component\Listing\Columns\Date">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="filter" xsi:type="string">dateRange</item>
                <item name="dataType" xsi:type="string">date</item>
                <item name="component" xsi:type="string">Magento_Ui/js/grid/columns/date</item>
                <item name="label" xsi:type="string" translate="true">Last Order Date</item>
                <item name="sortOrder" xsi:type="number">100</item>
            </item>
        </argument>
    </column>

perfect query @Amit

Ramesh
– Ramesh

2016-09-01 10:07:46 +00:00
Commented Sep 1, 2016 at 10:07 — Ramesh
– Ramesh, Commented Sep 1, 2016 at 10:07

Stack Exchange Network

Magento 2 add array to collection in Admin Block Grid

1 Answer 1

Your Answer

Hot Network Questions

Magento 2 add array to collection in Admin Block Grid

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions