Magento 2 : Create mysql query

Question

Is there any way we can create following query

SELECT `e`.sku,`ccp`.category_id,`ccp`.product_id,`ccp`.position
FROM `catalog_product_entity` AS `e` 
INNER JOIN `catalog_category_product` AS `ccp`
 ON e.row_id = ccp.product_id where sku IN ('P22756')

In Magento form ?

Answer 1

Depend where you are; for example, this is from an installer

$setup->getConnection()->query($yourRequestSql);

Globally, what you need to get is the getConnection() method which will allows you to perform anything you want and not just raw Sql; which i Don't recommand.

You can also inject using DI : Magento\Framework\App\ResourceConnection

Answer 2

Generally writing raw sql is a bad idea and you would be better off using the built in methods Magento gives you.

This is an example using a product resource model to build your query:

/** @var \Magento\Catalog\Model\ResourceModel\Product $resource */
/** @var \Magento\Framework\DB\Adapter\Pdo\Mysql $adapter */
$adapter = $resource->getConnection();
$select = $adapter->select()
    ->from(['e' => $resource->getTable('catalog_product_entity')], [])
    ->join(['ccp' => $resource->getTable('catalog_category_product')],
        'e.entity_id = ccp.product_id', [])
    ->where($adapter->prepareSqlCondition('e.sku', ['in' => ['P22756']]))
    ->columns(['e.sku', 'ccp.category_id', 'ccp.product_id', 'ccp.position']);

// If you want to see the raw query generated you can add this
// $sql = $select->assemble();

$results = $adapter->fetchAll($select);

Answer 3

Magento 2 : Create mysql query Join

class Collection extends AbstractCollection
{
    protected function _construct(
    )
    {
        parent::_construct();
        $this->_init(SellerOperationTeamList::class , SellerOperationTeam::class);
    }
    protected function _initSelect()
    {
        parent::_initSelect();
        $this->getSelect()->joinLeft(
            ['customer_address_entity' => $this->getTable('customer_address_entity')],
            '`main_table`.address_id = `customer_address_entity`.entity_id',['city','region','postcode' ,'street' , 'region_id','country_id']
        )->join(
            ['seller_operation_team_role' => $this->getTable('seller_operation_team_role')],
            '`main_table`.seller_operation_team_role_id = `seller_operation_team_role`.id',['name']
        );
        $this->addFilterToMap('name', 'main_table.seller_operation_team_role_id');
    }

}