Magento 2 Query to get SKU, Product Details ,more information

Question

I need a query to fetch the following data by passing the product sku.

catalog_product_entity as sku, catalog_product_entity_varchar as productName, catalog_product_entity_text as description

following are the tables

SQL Query

Answer 1

You don't need to write any query for product collection in magento please use below code for get products data in .phtml or any custom module,

Using Factory Method

<?php
namespace Test\Module\Block;

class Product extends \Magento\Framework\View\Element\Template
{

  protected $_productloader;  


  public function __construct(
        \Magento\Framework\View\Element\Template\Context $context,
        \Magento\Catalog\Model\ProductFactory $_productloader

    ) {


        $this->_productloader = $_productloader;
        parent::__construct($context);
    }
    public function getLoadProduct($id)
    {
        return $this->_productloader->create()->load($id);
    }

}

Add following code in your .phtml file if you use Factory Method

$product=$this->getLoadProduct(20);
echo $product->getName();

In Magento 2.1

<?php
    namespace Test\Module\Block;
    use Magento\Catalog\Api\ProductRepositoryInterface;
    class Product extends \Magento\Framework\View\Element\Template
     {
          protected $productRepository; 
          protected $_storeManager; 

          public function __construct(
            \Magento\Framework\App\Action\Context $context,
            ProductRepositoryInterface $productRepository
          ) {
              parent::__construct($context);

              $this->productRepository = $productRepository;
          }
          public function getProduct($productId)
          {
              return $product = $this->productRepository->getById($productId);
          }
      }

Answer 2

You don't need to write any query for product collection in magento. you can load product collection in external script as below :

<?php
use Magento\Framework\App\Bootstrap;

require __DIR__ . '/app/bootstrap.php';
$params =  $_SERVER;
$bootstrap = Bootstrap::create(BP, $params);

$obj = $bootstrap->getObjectManager();

$state = $obj->get('Magento\Framework\App\State');
$state->setAreaCode('frontend');

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();

$productCollection = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\CollectionFactory');

$collection = $productCollection->create()
            ->addAttributeToSelect('*')
            ->load();

foreach ($collection as $product){
     echo 'Name  =  '.$product->getName().'<br>';
     echo 'Sku  =  '.$product->getSku().'<br><br>';
}  

?>

Answer 3

You don't need to write sql query please use model to get product collection find the code below,

 <?php
$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
/** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $productCollection */
$productCollection = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\Collection');
/** Apply filters here */
$collection = $productCollection->addAttributeToSelect('*')
        ->load();
    foreach ($collection as $product){
         echo 'Name  =  '.$product->getName().'<br>';
         echo 'Name  =  '.$product->getSku().'<br>';
         echo 'Name  =  '.$product->getDescription().'<br>';

    }  

Answer 4

How to get product by id and SKU in Magento 2

$_helper = $this->helper('Magento\Catalog\Helper\Output'); 
$_product = $block->getProduct();
echo 'Product Id = '.$_product->getId().'<br>';
echo 'Product Name = '.$_product->getName().'<br>';
echo 'Product Sku = '.$_product->getSku().'<br>';

Answer 5

SELECT cpe.sku, cpev.value, cpet.value
FROM catalog_product_entity cpe
         JOIN catalog_product_entity_varchar cpev ON cpe.entity_id = cpev.entity_id
         JOIN catalog_product_entity_text cpet ON cpe.entity_id = cpet.entity_id
WHERE cpev.attribute_id = (SELECT attribute_id
                           FROM eav_attribute
                           WHERE attribute_code LIKE 'name' AND entity_type_id = 4)
  AND cpet.attribute_id = (SELECT attribute_id
                           FROM eav_attribute
                           WHERE attribute_code LIKE 'description' AND entity_type_id = 4)
  AND cpe.sku LIKE 'WH01';

where WH01 SKU for example.

Answer 6

Best and easy way to fetch sku's based on the category id from sql.To help anyone looking for an answer that works with Magento 2. x

SELECT product.entity_id as product_id, product.sku FROM catalog_product_entity as product
    INNER JOIN catalog_category_product as cat_prod on product.entity_id = cat_prod.product_id
    WHERE cat_prod.category_id=YOUR Category ID;