How can I write custom query in Magento 2 format

[aasim110]

I need following query in Magento 2 format, 

SELECT * FROM ( SELECT * FROM TABLE_NAME WHERE customer_id IN ( 0, 5 ) ORDER BY feed_id DESC ) AS t1 GROUP BY position limit 0,5

[Rakesh Jesadiya]

For details of all the SQL Operation, Custom SQL Query in Magento 2 Standard Way 

You can get custom collection query using Magento way by below way,

<?php
protected $_resourceConnection;
protected $_connection;
public function __construct(
    \Magento\Framework\App\ResourceConnection $resourceConnection
) {
    $this->_resourceConnection = $resourceConnection;
}
public function getCollection()
{	
    $this->_connection = $this->_resourceConnection->getConnection();
    //Your custom sql query
    $query = "SELECT * FROM ( SELECT * FROM TABLE_NAME WHERE customer_id IN ( 0, 5 ) ORDER BY feed_id DESC ) AS t1 GROUP BY position limit 0,5"; 

    $collection = $this->_connection->fetchAll($query);
    return $collection;
}

If issue solved, click Kudos/Accept as solutions.

[aasim110]

@Rakesh Jesadiya Thanks for response, I've already tried with this logic but default Pagination does not working on this collection. 

I need custom collection query like Magento default collection, so I can use filtering and sorting. 

so do you have any idea for this ?  

[Rakesh Jesadiya]

You can try with collection object as below way,

 I havent try this solutions but may be works for you,

public function __construct(
        \Vendor\Modulename\Model\CustomFactory $customFactory
    ) {
        $this->customFactory = $customFactory;
    }

public function getMyQuery(){    
	$collection = $this->customFactory->create()->getCollection();
	$collection->getSelect()->columns(
    array(
        't1' => new \Zend_Db_Expr('SELECT * FROM TABLE_NAME WHERE customer_id IN ( 0, 5 ) ORDER BY feed_id DESC')
        );
    )->group(
        'position'
    );
}

If issue Solved, Click Kudos/Accept as solutions.

[aasim110]

I have tried this but default pagination does not work on this collection.

[Rakesh Jesadiya]

For You above collection is working as per magento way? if working above way,

just pass preparelayout function in your block,

protected function _prepareLayout()
{
    parent::_prepareLayout();
    if ($this->getCollection()) {
        $pager = $this->getLayout()->createBlock(
            'Magento\Theme\Block\Html\Pager',
            'custom.pager'
        )->setCollection(
            $this->getCollection()
        );
        $this->setChild('pager', $pager);
        $this->getCollection()->load();
    }
    return $this;
}

where $this->getCollection() is your custom collection.

if issue solved, click kudos/accept as solutions.

[aasim110]

@Rakesh Jesadiya Following solution returns syntex error while using custom collection 

public function __construct( \Vendor\Modulename\Model\CustomFactory $customFactory ){
    $this->customFactory = $customFactory;
}

public function getMyQuery(){    
    $collection = $this->customFactory->create()->getCollection();  
    $collection->getSelect()->columns(    
        array(
        't1' => new \Zend_Db_Expr('SELECT * FROM TABLE_NAME WHERE customer_id IN ( 0, 5 ) ORDER BY feed_id DESC')
        );
    )->group(
        'position'
    );
}