Catalog Collections

Product Collection

// Get product collection
$collection = $objectManager->get(
    \Magento\Catalog\Model\ResourceModel\Product\Collection::class
);

// Select specific attributes
$collection->addAttributeToSelect(['name', 'price', 'sku', 'image']);

// Filter by attribute
$collection->addFieldToFilter('status', 1);
$collection->addFieldToFilter('visibility', ['in' => [2, 4]]);

// Sort
$collection->setOrder('price', 'ASC');

// Paginate
$collection->setPageSize(20)->setCurPage(1);

// Load
$collection->load();

// Iterate
foreach ($collection as $product) {
    echo $product->getName();
    echo $product->getPrice();
}

Category Collection

// Get category collection
$collection = $objectManager->get(
    \Magento\Catalog\Model\ResourceModel\Category\Collection::class
);

// Filter active categories
$collection->addFieldToFilter('is_active', 1);

// Filter by level
$collection->addFieldToFilter('level', ['gteq' => 1]);
$collection->addFieldToFilter('level', ['lteq' => 3]);

// Add product count
$collection->addCountToSelect();

// Sort by position
$collection->setOrder('position', 'ASC');

foreach ($collection as $category) {
    echo $category->getName();
    echo $category->getProductCount();
}

Filter Conditions

// Simple filter
$collection->addFieldToFilter('status', 1);

// In filter
$collection->addFieldToFilter('entity_id', ['in' => [1, 2, 3]]);

// Range filter
$collection->addFieldToFilter('price', ['gteq' => 10]);
$collection->addFieldToFilter('price', ['lteq' => 50]);
$collection->addFieldToFilter('price', ['from' => 10, 'to' => 50]);

// Like filter
$collection->addFieldToFilter('name', ['like' => '%T-Shirt%']);

// Null filter
$collection->addFieldToFilter('special_price', ['null' => true]);

// Not null filter
$collection->addFieldToFilter('special_price', ['notnull' => true]);

// OR conditions
$collection->addFieldToFilter([
    ['attribute' => 'status', 'eq' => 1],
    ['attribute' => 'visibility', 'eq' => 4]
]);

Sorting

// Sort by attribute
$collection->setOrder('price', 'ASC');
$collection->setOrder('name', 'DESC');

// Multiple sort
$collection->setOrder('position', 'ASC');
$collection->setOrder('price', 'ASC');

// Sort by raw SQL
$collection->getSelect()->order(new \Zend_Db_Expr('RAND()')); // Random

Pagination

// Set page size
$collection->setPageSize(20);

// Set current page
$collection->setCurPage(1);

// Get total count
$count = $collection->getSize(); // or count($collection)

// Get last page number
$lastPage = $collection->getLastPageNumber();

// Check if page exists
if ($collection->getCurPage() <= $collection->getLastPageNumber()) {
    // Load page
}

Joining Tables

// Join with another table
$collection->getSelect()->join(
    ['additional' => 'additional_table'],
    'additional.entity_id = e.entity_id',
    ['additional_column']
);

// Left join
$collection->getSelect()->joinLeft(
    ['additional' => 'additional_table'],
    'additional.entity_id = e.entity_id',
    ['additional_column']
);

// Join with alias
$collection->getSelect()->join(
    ['inv' => $collection->getResource()->getTable('cataloginventory_stock_item')],
    'inv.product_id = e.entity_id',
    ['qty' => 'inv.qty']
);

Custom WHERE Conditions

// Add raw WHERE
$collection->getSelect()->where('e.entity_id > ?', 100);

// Add WHERE with AND
$collection->getSelect()->where('e.status = ?', 1);
$collection->getSelect()->where('e.price > ?', 10);

// Add WHERE with OR
$collection->getSelect()->where('e.status = ? OR e.visibility = ?', [1, 4]);

// Add HAVING
$collection->getSelect()->having('COUNT(*) > ?', 5);

Custom SELECT Columns

// Add columns
$collection->getSelect()->columns([
    'entity_id',
    'name',
    'price',
    'IF(special_price IS NOT NULL, special_price, price) AS final_price'
]);

// Use expressions
$collection->getSelect()->columns([
    'price_with_tax' => new \Zend_Db_Expr('price * 1.2')
]);

// Aggregate functions
$collection->getSelect()->columns([
    'avg_price' => new \Zend_Db_Expr('AVG(price)')
]);

Query Building

// Get SQL query
$sql = $collection->getSelect()->__toString();

// Debug query
error_log($sql);

// Count query
$countSql = 'SELECT COUNT(*) FROM (' . $sql . ') AS count_table';

Performance Optimization

// 1. Select only needed attributes
$collection->addAttributeToSelect(['name', 'price', 'sku']);
// Avoid: addAttributeToSelect('*')

// 2. Use flat table for listing
$flatCollection = $objectManager->get(
    \Magento\Catalog\Model\ResourceModel\Product\Flat\Collection::class
);

// 3. Limit result set
$collection->setPageSize(20)->setCurPage(1);

// 4. Use indexes
$collection->addAttributeToFilter('status', 1); // Uses index

// 5. Avoid N+1
// BAD: foreach ($collection as $product) { $product->getName(); }
// GOOD: Load attributes in collection

// 6. Clear when done
$collection->clear();

Monitoring Queries

// Enable query logging
$profiler = $objectManager->get(
    \Magento\Framework\Profiler::class
);
$profiler->start('catalog_collection');

$collection->load();

$profiler->stop('catalog_collection');

// Log queries
$logger = $objectManager->get(
    \Psr\Log\LoggerInterface::class
);
$logger->info($collection->getSelect()->__toString());

Common Patterns

// Get products in category with price filter
$collection = $productCollection->create();
$collection->addAttributeToSelect(['name', 'price', 'image'])
    ->addFieldToFilter('status', 1)
    ->addFieldToFilter('visibility', ['in' => [2, 4]])
    ->joinField(
        'price',
        'catalog_product_index_price',
        'price',
        'entity_id=entity_id',
        ['customer_group_id' => 0, 'website_id' => 1]
    )
    ->addFieldToFilter('price', ['gteq' => 10])
    ->addFieldToFilter('price', ['lteq' => 50])
    ->setOrder('price', 'ASC')
    ->setPageSize(20);

// Get product count per category
$category->getProductCount();

// Get categories for product
$product->getCategoryIds();