SQL Query magento 2 to get all products in a certain category

[michelle_van ro]

How do I get all the products with a certain category id (Category Id being 129) using the below query - 

SELECT e.entity_id AS 'id',
v1.value AS 'name',
e.sku,
d1.value AS 'price',
t1.value AS 'short_description',
t2.value AS 'description',
v2.value AS 'image',
v3.value AS 'thumbnail',
mg.value AS 'media_gallery',
cids.category_ids AS 'category_ids',
cids.category_names AS 'category_names'
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id
AND v1.store_id = 0 AND v1.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name'
AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t1 ON e.entity_id = t1.entity_id
AND t1.store_id = 0 AND t1.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'short_description'
AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t2 ON e.entity_id = t2.entity_id
AND t2.store_id = 0 AND t2.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'description'
AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v2 ON e.entity_id = v2.entity_id
AND v2.store_id = 0 AND v2.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'image'
AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v3 ON e.entity_id = v3.entity_id
AND v3.store_id = 0 AND v3.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'thumbnail'
AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id
AND d1.store_id = 0 AND d1.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price'
AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))
LEFT JOIN
(SELECT m1.entity_id, GROUP_CONCAT(m2.value) AS value FROM catalog_product_entity_media_gallery_value_to_entity m1
INNER JOIN catalog_product_entity_media_gallery m2 ON m2.value_id = m1.value_id
AND m2.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'media_gallery'
AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))
GROUP BY m1.entity_id) mg ON e.entity_id = mg.entity_id
LEFT JOIN
(SELECT product_id, GROUP_CONCAT(c.category_id SEPARATOR ',') AS category_ids, GROUP_CONCAT(cv.value SEPARATOR ',') AS category_names
FROM catalog_category_product c
INNER JOIN catalog_category_entity_varchar cv ON c.category_id = cv.entity_id
AND cv.store_id = 0
AND cv.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name'
AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_category'))
GROUP BY product_id) cids ON e.entity_id = cids.product_id;

[mallikajai5922]

First I suggest you to use magento standard .
1. Create Products.php block- 

<?php
namespace VendorName\ModuleName\Block;
class Products extends \Magento\Framework\View\Element\Template
{    
  
     /**
     * @var \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory
     */
    protected $_productCollectionFactory;
  
    public function __construct(
        \Magento\Backend\Block\Template\Context $context,        
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory
    )
    {    
        $this->_productCollectionFactory = $productCollectionFactory;
        parent::__construct($context);
    }
    
    
    public function getProductCollectionByCategories($ids)
    {
        $collection = $this->_productCollectionFactory->create();
        $collection->addAttributeToSelect('*');
        $collection->addCategoriesFilter(['in' => ids]);
        return $collection;
    }
}

2. Now you have product collection in block . Insert the below code in VendorName\ModuleName/view/frontend/templates/list.phtml

$ids = [1,2,3];
$categoryProducts = $block->getProductCollectionByCategories($ids);
foreach ($categoryProducts as $product) {
    echo $product->getName() . ' - ' . $product->getProductUrl() . '<br />';
}

I hope this will help you . Click kudos and Accept it as a solution.

[hankjamjam53d1]

---

@mallikajai5922 wrote:

First I suggest you to use magento standard .
1. Create Products.php block- 

<?php
namespace VendorName\ModuleName\Block;
class Products extends \Magento\Framework\View\Element\Template
{    
  
     /**
     * @var \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory
     */
    protected $_productCollectionFactory;
  
    public function __construct(
        \Magento\Backend\Block\Template\Context $context,        
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory
    )
    {    
        $this->_productCollectionFactory = $productCollectionFactory;
        parent::__construct($context);
    }
    
    
    public function getProductCollectionByCategories($ids)
    {
        $collection = $this->_productCollectionFactory->create();
        $collection->addAttributeToSelect('*');
        $collection->addCategoriesFilter(['in' => ids]);
        return $collection;
    }
}

2. Now you have product collection in block . Insert the below code in VendorName\ModuleName/view/frontend/templates/list.phtml

$ids = [1,2,3];
$categoryProducts = $block->getProductCollectionByCategories($ids);
foreach ($categoryProducts as $product) {
    echo $product->getName() . ' - ' . $product->getProductUrl() . '<br />';
}

I hope this will help you . Click kudos and Accept it as a solution.

---

Oh! okay. that make sense

[artempav1684c5]

I first asked this on stack but the provided answer didn't work. I saw that the question wasn't getting much headway so I dropped the discussion and just tried to hack it out. I suck at hacking it out. So I've come back home to reddit for help.

I using a code snippet to render the categories that a product is listed under on the product page.

<ul>
<?php $categories = $_product->getCategoryIds(); ?>
<?php foreach($categories as $k => $_category_id): ?>
<?php $_category = Mage::getModel('catalog/category')->load($_category_id) ?>
<?php if($_category->getIsActive()): ?>
<li><a href="<?php echo $_category->getUrl() ?>"><?php echo $_category->getName() ?></a></li>
<?php endif; ?>
<?php endforeach; ?>
</ul>  

However, I'm realizing that the list can get a bit unruly and not really necessary. How can I adapt this code to only show the child categories the product belongs to. I might even need to take it a step further and only show the LAST child categories the product belongs to. Any help?

Also, is there a way to widgetize this? I've got this code in the media.phtml file but I don't like managing actual files and would rather work with this from admin via cms static blocks.

Again, I suck at hacking it out on magento.

sky light pay card