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 ?

  • you mean in magento grid? or something else.. specify more details Oct 22, 2021 at 8:08

3 Answers 3


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


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


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);

Magento 2 : Create mysql query Join

class Collection extends AbstractCollection
    protected function _construct(
        $this->_init(SellerOperationTeamList::class , SellerOperationTeam::class);
    protected function _initSelect()
            ['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']
            ['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');


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.