Skip to content

Instantly share code, notes, and snippets.

@drew7721

drew7721/collection.php

Created July 18, 2017 19:28
Show Gist options
  • Save drew7721/a899d7f2449010c30e35ed7458e0e557 to your computer and use it in GitHub Desktop.
Save drew7721/a899d7f2449010c30e35ed7458e0e557 to your computer and use it in GitHub Desktop.
Download ZIP
Magento 2 ProductRepository Example
Raw
collection.php
<?php
private function getProductInfoByAriSkus($ariSkus)
{
$productInfo = [];
if (!empty($ariSkus)) {
/** @var Collection $productCollection*/
$productCollection = $this->product
->getCollection()
->addAttributeToSelect([
'entity_id',
'shiptime',
'price',
'sku',
'manufacturer_part_number',
])
->addFieldToFilter(
[
[
'attribute' => 'manufacturer_part_number',
[
'in' => $ariSkus
]
]
]
);
$productCollection->getItems();
foreach ($productCollection as $product) {
/** do something */
}
}
return array_filter($productInfo);
}
Raw
queries.sql
-- Let's compare Collection vs Repository
## Collection
SELECT `e`.*, `at_manufacturer_part_number`.`value` AS `manufacturer_part_number` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_entity_varchar` AS `at_manufacturer_part_number` ON (`at_manufacturer_part_number`.`entity_id` = `e`.`entity_id`) AND (`at_manufacturer_part_number`.`attribute_id` = '532') AND (`at_manufacturer_part_number`.`store_id` = 0) WHERE ((((at_manufacturer_part_number.value = 'manufacturer_part_number') OR (at_manufacturer_part_number.value IN('11061-0291', '130BD0610', '13271-0795', '13280-0305', '14001-0159', '14013-1051', '14014-0019', '14014-0029', '14014-0030', '27010-1313', '670D2018', '92005-1238', '92022-304', '92043-1506', '92043-1567', '92046-037', '92049-1474', '92049-1520', '92055-091', '92062-0019', '92062-1056', '92066-0003', '92066-1012', '92066-1110', '92066-1161', '92153-0381', '92153-1639', '92153-1640', '92172-0090')))));
;
## Repository
-- executed in ProductRepository->getList() by $collection->load();
SELECT `e`.*, IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS `status`, IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) AS `visibility`, `at_manufacturer_part_number`.`value` AS `manufacturer_part_number`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '80') AND `at_status_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '80') AND (`at_status`.`store_id` = 1)
INNER JOIN `catalog_product_entity_int` AS `at_visibility_default` ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '85') AND `at_visibility_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_int` AS `at_visibility` ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '85') AND (`at_visibility`.`store_id` = 1)
INNER JOIN `catalog_product_entity_varchar` AS `at_manufacturer_part_number` ON (`at_manufacturer_part_number`.`entity_id` = `e`.`entity_id`) AND (`at_manufacturer_part_number`.`attribute_id` = '532') AND (`at_manufacturer_part_number`.`store_id` = 0)
LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 WHERE ((at_manufacturer_part_number.value IN('11061-0291', '130BD0610', '13271-0795', '13280-0305', '14001-0159', '14013-1051', '14014-0019', '14014-0029', '14014-0030', '27010-1313', '670D2018', '92005-1238', '92022-304', '92043-1506', '92043-1567', '92046-037', '92049-1474', '92049-1520', '92055-091', '92062-0019', '92062-1056', '92066-0003', '92066-1012', '92066-1110', '92066-1161', '92153-0381', '92153-1639', '92153-1640', '92172-0090'))) LIMIT 29
;
-- executed in ProductRepository->getList() by $searchResult->setTotalCount($collection->getSize()); before the return.
SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '80') AND `at_status_default`.`store_id` = 0
INNER JOIN `catalog_product_entity_int` AS `at_visibility_default` ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '85') AND `at_visibility_default`.`store_id` = 0
INNER JOIN `catalog_product_entity_varchar` AS `at_manufacturer_part_number` ON (`at_manufacturer_part_number`.`entity_id` = `e`.`entity_id`) AND (`at_manufacturer_part_number`.`attribute_id` = '532') AND (`at_manufacturer_part_number`.`store_id` = 0)
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 WHERE ((at_manufacturer_part_number.value IN('11061-0291', '130BD0610', '13271-0795', '13280-0305', '14001-0159', '14013-1051', '14014-0019', '14014-0029', '14014-0030', '27010-1313', '670D2018', '92005-1238', '92022-304', '92043-1506', '92043-1567', '92046-037', '92049-1474', '92049-1520', '92055-091', '92062-0019', '92062-1056', '92066-0003', '92066-1012', '92066-1110', '92066-1161', '92153-0381', '92153-1639', '92153-1640', '92172-0090')))
;
Raw
repository.php
<?php
private function getProductInfoByAriSkus($ariSkus)
{
$productInfo = [];
if (!empty($ariSkus)) {
$this->searchCriteriaBuilder->addFilter('manufacturer_part_number', $ariSkus, 'in');
/** @var ProductInterface[] $products */
$products = $this->productRepository->getList($this->searchCriteriaBuilder->create())->getItems();
foreach ($products as $product) {
/** do something **/
}
}
return array_filter($productInfo);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment