:::: MENU ::::
Posts tagged with: collection

Magento 2 use custom query

Today we will discuss how in Magento 2 custom query works. Sometime magento 2 model is not enough to get data from database as per our requirements. So to fulfill this requirement we use custom query. In this example we will use select custom query, custom delete query, custom insert query and custom update query. So let’s start with example.

Get connection

First of all we will establish a connection to execute the query.

 $this->_resources = \Magento\Framework\App\ObjectManager::getInstance()->get('Magento\Framework\App\ResourceConnection');          
        $connection= $this->_resources->getConnection();

Insert Query

It is simple to use insert query just set the values for columns. Use the inset method.

$array=array(‘column1’=>'column1','column2'=>'column2');

$connection->insert($this->_resources->getTableName(test_table), $array);

Select Query

Selecting table data in custom query you can use following code.

$select = $connection->select()->from(['o' =>  $this->_resources->getTableName(test_table)]);
$result = $connection->fetchAll($select);

       foreach ($result as $data) {
           echo $data['id'];
            }

Update Query

Updating table data in custom query you can use following code.

$id = 1;
 $sql = "Update " . $this->_resources->getTableName('test_table') . " Set `title` ='test title' where `id` = ".$id;
     $connection->query($sql);

Delete Query

To delete table data in custom query you can use following code.

$id = 1;
$condition = $this->getConnection()->quoteInto('id = ?', $id);
            $this->getConnection()->delete($this->getTable('test_table'), $condition);

Join Table

To join table in custom query you can use following code.

        $select = $connection->select()->from(['o' =>  $this->_resources->getTableName('test_table')])->joinleft(
        ['test_table1' => $this->_resources->getTableName('test_table1')],
        'o.id = test_table1.id',
        ['column1','column2']
    );
Author: Qaisar Satti
Category: Magento 2
Last Modified: April 19, 2018


Magento 2 create basic module frontend part 4

As we finished with the basic module structure and admin part we move to Magento 2 create basic module frontend .Now this tutorial will be cover the create frontend router, create layout file, create frontend controller, create frontend block, add custom pagination. Now we start with frontend.

Create frontend router

Create routes.xml in following directory

QaisarSatti\HelloWorld\etc\frontend\

<?xml version="1.0"?>
<!--
/**
* Hello World
   *
   * @category    QaisarSatti
   * @package     QaisarSatti_HelloWorld
   * @author      Muhammad Qaisar Satti
   * @Email       [email protected]
   *
*/
-->
     <config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xsi:noNamespaceSchemaLocation="urn:magento:framework:App/etc/routes.xsd">
        <router id="standard">
            <route id="helloworld" frontName="helloworld">
                <module name="QaisarSatti_HelloWorld" />
            </route>
        </router>
    </config>

Create frontend controller

Create Index.php in following directory

QaisarSatti\HelloWorld\Controller\Index\

<?php
/**
 * Hello World
    *
    * @category    QaisarSatti
    * @package     QaisarSatti_HelloWorld
    * @author      Muhammad Qaisar Satti
    * @Email       [email protected]
    *
 */

namespace QaisarSatti\HelloWorld\Controller\Index;

class Index extends \Magento\Framework\App\Action\Action

{

    public function execute()
    {  
       
        $this->_view->loadLayout();
        $this->_view->renderLayout();
    }

}

Create layout file

Create helloworld_index_index.xml in following directory

QaisarSatti\HelloWorld\etc\frontend\

<?xml version="1.0"?>
<!--
/**
* Hello World
   *
   * @category    QaisarSatti
   * @package     QaisarSatti_HelloWorld
   * @author      Muhammad Qaisar Satti
   * @Email       [email protected]
   *
*/
-->
<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:noNamespaceSchemaLocation="urn:magento:framework:View/Layout/etc/page_configuration.xsd">
        <body>
            <referenceContainer name="content">
                <block class="QaisarSatti\HelloWorld\Block\HelloWorld" name="HellosWorld" template="QaisarSatti_HelloWorld::HelloWorld.phtml"></block>
            </referenceContainer>
        </body>
    </page>

Create frontend block

Create HelloWorld.php in following directory

QaisarSatti\HelloWorld\Block\

<?php
/**
 * Hello World
    *
    * @category    QaisarSatti
    * @package     QaisarSatti_HelloWorld
    * @author      Muhammad Qaisar Satti
    * @Email       [email protected]
    *
 */


namespace QaisarSatti\HelloWorld\Block;

class HelloWorld extends \Magento\Framework\View\Element\Template
{
      protected $_coreRegistry = null;
    protected $_collectionFactory;
    protected $_productsFactory;
    protected $_helloworldFactory;
   public function __construct(
        \Magento\Backend\Block\Template\Context $context,
        \Magento\Framework\Registry $registry,
        \QaisarSatti\HelloWorld\Model\ResourceModel\HelloWorld\CollectionFactory $helloworldFactory,
       array $data = []
   ) {
 
       $this->_coreRegistry = $registry;
       $this->_helloworldFactory = $helloworldFactory;
       parent::__construct($context, $data);
   }
   public function getHelloCollection()
        {
          $page=($this->getRequest()->getParam('p'))? $this->getRequest()->getParam('p') : 1;
           //get values of current limit
          $pageSize=($this->getRequest()->getParam('limit'))? $this->getRequest()->getParam('limit') : 5;

          $helloCollection = $this->_helloworldFactory->create();
          $helloCollection->setPageSize($pageSize);
          $helloCollection->setCurPage($page);
          return $helloCollection;
        }
   public function _prepareLayout()
   {
   
       parent::_prepareLayout();
       $this->pageConfig->getTitle()->set(__('Sample Post'));

       if ($this->getHelloCollection()) {
            $pager = $this->getLayout()->createBlock('Magento\Theme\Block\Html\Pager','qaisarsatti.blog.pager')->setAvailableLimit(array(5=>5,10=>10,15=>15,20=>20));
            $pager->setShowPerPage(true);
            $pager->setCollection($this->getHelloCollection());
            $this->setChild('pager', $pager);
            $this->getHelloCollection()->load();
        }
        return $this;
   }
   public function getPagerHtml(){
        return $this->getChildHtml('pager');
    }


}

Create frontend phtml

Create HelloWorld.phtml in following directory

QaisarSatti\HelloWorld\view\frontend\templates\

<?php
/**
 * Hello World
    *
    * @category    QaisarSatti
    * @package     QaisarSatti_HelloWorld
    * @author      Muhammad Qaisar Satti
    * @Email       [email protected]
    *
 */

$blogCollection = $block->getHelloCollection();
foreach ($blogCollection as $blog) {

    echo $blog->getTitle().'<br />';
    echo '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$blog->getContent().'<br />';
}

 if ($block->getPagerHtml()): ?>
<div class="order-products-toolbar toolbar bottom">
<?php echo $block->getPagerHtml(); ?>
</div>
<?php endif ?>
Author: Qaisar Satti
Category: Magento 2
Last Modified: February 9, 2018


Magento 2 best Seller product collection

Let’s talk about using sales report collection so today we talk about how to get Magento 2 best seller product collection. Best seller collection in depend on number of time product quantity is sold. First we have to inject the Bestsellers\CollectionFactory in you block or helper any other file you want to use it. You can get the report of monthly,yearly and daily and also custom date. So let start with coding. In this example I am going to inject into my block.

Step 1: Create a block file

<?php
    /**
    * Simple Hello World Module
    *
    * @category    QaisarSatti
    * @package     QaisarSatti_HelloWorld
    * @author      Muhammad Qaisar Satti
    * @Email       [email protected]
    *
    */

namespace QaisarSatti\HelloWorld\Block;
class BestSeller extends \Magento\Framework\View\Element\Template
{
 
    protected $_collectionFactory;
   public function __construct(
       \Magento\Backend\Block\Template\Context $context,
      \Magento\Sales\Model\ResourceModel\Report\Bestsellers\CollectionFactory $collectionFactory,
               array $data = []
   ) {
 
       $this->_collectionFactory = $collectionFactory;
             parent::__construct($context, $data);
   }


public function getBestSellerData(){
     
$bestSellerProdcutCollection = $this->_collectionFactory->create()
                    ->setModel('Magento\Catalog\Model\Product')
                    ->setPeriod('month') //you can add period daily,yearly
                   ;
           
       return $bestSellerProdcutCollection;
   }
}

Step 2: Show Output

Getting collection in phtml file from block.

<!--/**
    * Simple Hello World Module
    *
    * @category    QaisarSatti
    * @package     QaisarSatti_HelloWorld
    * @author      Muhammad Qaisar Satti
    * @Email       [email protected]
    *
    */
-->

    <?php
        $bestSeller =  $block->getBestSellerData(); ?>
        <h1>Best Seller Collection.....</h1>
        <ul>
            <?php foreach ($bestSeller as $product) {
                ?>
                <li><?php  echo $product->getProductName();?>--<?php  echo $product->getQtyOrdered();?></li>
            <?php } ?>
</ul>
Author: Qaisar Satti
Category: Magento 2
Last Modified: January 5, 2018


Magento 2 create custom model

Every custom module that need to interact with database for that we need to create model. So today we talk about how to create magento 2 create custom model. Model consist of three files model file, resource model file and collection. So let create the our model file.

Step 1: Model file

Model file is pointing out our resource model. Here is example code model file on Github.

<?php


namespace QaisarSatti\HelloWorld\Model;



    class Helloworld extends \Magento\Framework\Model\AbstractModel  
    {  
        protected function _construct()
        {
            $this->_init('QaisarSatti\HelloWorld\Model\ResourceModel\Helloworld');
        }
    }

Step 2: Create Resource model file

In resource model file we initialize table name and primary key column name. Here is example code resource model file on Github.

<?php


namespace QaisarSatti\HelloWorld\Model\ResourceModel;


use \Magento\Framework\Model\ResourceModel\Db\AbstractDb;

class Helloworld extends AbstractDb

{

    protected function _construct()

    {

        $this->_init('blog_posts', 'post_id');

    }

}

Step 3: Collection model

In collection model file have initialize model and resource model. Here is example code collection model file on Github.

<?php


namespace QaisarSatti\HelloWorld\Model\ResourceModel\Helloworld;


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


class Collection extends AbstractCollection

{

    protected function _construct()
    {

        $this->_init(
        'QaisarSatti\HelloWorld\Model\Helloworld',
        'QaisarSatti\HelloWorld\Model\ResourceModel\Helloworld');

    }

}

Here is complete example code on Github

Author: Qaisar Satti
Category: Magento 2
Last Modified: January 2, 2018


Magento 2 get min price or max price from product collection

Today we learn about how to get min price and max price from product collection. First you have to get collection of product. Then their two predefined function for get min price getMinPrice and get max price getMaxPrice Now you can get the collection following tutorial Magento 2 get product collection.

Now we implement the code for getting min price and max price.

protected $_productCollectionFactory;

public function __construct(
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productFactory
    ) {
        $this->_productCollectionFactory = $productFactory;
    }
    public function getProductCollection()
    {
      $productCollection = $this->_productCollectionFactory->create();
      $maxPrice = $productCollection>getMaxPrice();
     $minPrice = $productCollection>getMinPrice();
    }

}

You can see the example of Min Price and Max Price in Layer collection.Follow Magento\Catalog\Model\Layer\Filter\AbstractFilter.php

$this->getLayer()->getProductCollection()->getMaxPrice();
$this->getLayer()->getProductCollection()->getMinPrice();
Author: Qaisar Satti
Category: Magento 2
Last Modified: December 15, 2017


Magento 2 get latest order collection

Today we learn how to get latest order collection. You can simply add the setOrder entity_id to DESC or use created_at to DESC to your order collection. If you want to add limit simply add setpagesize

setPageSize(yournumberhere);

protected $_orderFactory;
public function __construct(

\Magento\Sales\Model\ResourceModel\Order\CollectionFactory $orderFactory,

)
{

$this->_orderFactory = $orderFactory;

}
public function getLatestOrders() {
$orderCollection =$this->_orderFactory->create()->addFieldToSelect(array('*')); $orderCollection>setOrder('entity_id','DESC');
return $orderCollection;
}
Author: Qaisar Satti
Category: Magento 2
Last Modified: April 23, 2018


Magento 2 add pagination on custom collection

Today, we’ll talk about how to get custom collection with magento 2 default pagination or use pager with custom collection.For any suggestions & question, please feel free to drop a comment.

First Step:

Get collection for pagination.

public function getNews() {
 //get values of current page
$page=($this->getRequest()->getParam('p'))? $this->getRequest()->getParam('p') : 1;
 //get values of current limit
$pageSize=($this->getRequest()->getParam('limit'))? $this->getRequest()->getParam('limit') : 1;
$newsCollection = $this->newscollectionFactory->create(); $newsCollection->addFieldToFilter('is_active',1); $newsCollection->setOrder('title','ASC'); $newsCollection->setPageSize($pageSize); $newsCollection->setCurPage($page);
return $newsCollection;
}

Second Step:
Now simply add the pagination to you collection

protected function _prepareLayout() {
parent::_prepareLayout();
$this->pageConfig->getTitle()->set(__('News'));
 if ($this->getNews()) {
$pager = $this->getLayout()->createBlock( 'Magento\Theme\Block\Html\Pager', 'qaisarsatti.news' )->setAvailableLimit(array(5=>5,10=>10,15=>15))->setShowPerPage(true)->setCollection( $this->getNews() );
$this->setChild('pager', $pager);
$this->getNews()->load();
}
return $this;
}

Third Step:
Now create a function to get the child block of pager.

public function getPagerHtml() {
return $this->getChildHtml('pager');
 }

Fourth Step:
in your phtml file add this code then pagination will be showing with limits.

<?php if ($block->getPagerHtml()): ?>
<div class="order-products-toolbar toolbar bottom">
<?php echo $block->getPagerHtml(); ?>
</div>
<?php endif ?>
Author: Qaisar Satti
Category: Magento 2
Last Modified: December 29, 2017