🔍 DbQuery Builder — API fluida para queries SQL

<?php

// ── Query compleja con multiples joins ──
$query = (new DbQuery())
    ->select('p.id_product, pl.name, p.price, cl.name AS category_name,
              m.name AS manufacturer_name, p.quantity')
    ->from('product', 'p')
    ->leftJoin(
        'product_lang', 'pl',
        'p.id_product = pl.id_product AND pl.id_lang = ' . (int) $idLang
        . ' AND pl.id_shop = ' . (int) $idShop
    )
    ->leftJoin(
        'category_lang', 'cl',
        'p.id_category_default = cl.id_category AND cl.id_lang = ' . (int) $idLang
    )
    ->leftJoin(
        'manufacturer', 'm',
        'p.id_manufacturer = m.id_manufacturer'
    )
    ->innerJoin(
        'stock_available', 'sa',
        'p.id_product = sa.id_product AND sa.id_product_attribute = 0'
    )
    ->where('p.active = 1')
    ->where('p.visibility IN (\'both\', \'catalog\')')
    ->orderBy('p.date_add DESC')
    ->limit(20, ($page - 1) * 20); // Paginacion

$products = Db::getInstance()->executeS($query);

<?php

$query = (new DbQuery())
    ->select('p.id_product, pl.name, p.price')
    ->from('product', 'p')
    ->leftJoin('product_lang', 'pl',
        'p.id_product = pl.id_product AND pl.id_lang = ' . (int) $idLang
    );

// ── Condiciones dinamicas ──
if ($activeOnly) {
    $query->where('p.active = 1');
}

if ($minPrice > 0) {
    $query->where('p.price >= ' . (float) $minPrice);
}

if ($maxPrice > 0) {
    $query->where('p.price <= ' . (float) $maxPrice);
}

if ($searchTerm) {
    $term = pSQL($searchTerm);
    $query->where("pl.name LIKE '%" . $term . "%'");
}

if ($idCategory) {
    $query->where('p.id_category_default = ' . (int) $idCategory);
}

// ── HAVING para campos calculados ──
$query
    ->select('COUNT(o.id_order) AS total_orders')
    ->leftJoin('orders', 'o', 'p.id_product = o.id_product')
    ->groupBy('p.id_product')
    ->having('total_orders > 5');

$results = Db::getInstance()->executeS($query);

<?php

$page     = max(1, (int) Tools::getValue('page', 1));
$perPage  = 20;
$offset   = ($page - 1) * $perPage;

// ── Query paginada ──
$query = (new DbQuery())
    ->select('p.id_product, pl.name, p.price')
    ->from('product', 'p')
    ->leftJoin('product_lang', 'pl',
        'p.id_product = pl.id_product AND pl.id_lang = ' . (int) $idLang
    )
    ->where('p.active = 1')
    ->orderBy('p.date_add DESC')
    ->limit($perPage, $offset);

$products = Db::getInstance()->executeS($query);

// ── Contar el total sin LIMIT ──
$countQuery = (new DbQuery())
    ->select('COUNT(p.id_product) AS total')
    ->from('product', 'p')
    ->where('p.active = 1');

$total = (int) Db::getInstance()->getValue($countQuery);
$totalPages = ceil($total / $perPage);

<?php

// ── Subquery en WHERE con IN ──
$categoryIds = [1, 3, 7, 12];
$query = (new DbQuery())
    ->select('*')
    ->from('product', 'p')
    ->where('p.id_category_default IN (' . implode(',', array_map('intval', $categoryIds)) . ')');

// ── Subquery como tabla ──
$subQuery = (new DbQuery())
    ->select('id_order, MAX(date_add) AS last_date')
    ->from('order_history')
    ->groupBy('id_order');

$mainQuery = (new DbQuery())
    ->select('o.id_order, o.reference, oh_last.last_date')
    ->from('orders', 'o')
    ->join('INNER JOIN (' . $subQuery->build() . ') oh_last ON o.id_order = oh_last.id_order')
    ->where('o.valid = 1');

$results = Db::getInstance()->executeS($mainQuery);

<?php

$db = Db::getInstance();

// ── Multiples filas → array de arrays asociativos ──
$rows = $db->executeS($query);  // Devuelve array o false
$rows = $db->executeS($query) ?: [];  // Devuelve array vacio si no hay resultados

// ── Una sola fila → array asociativo ──
$row = $db->getRow($query);
if ($row) {
    echo $row['name'];
}

// ── Un solo valor ──
$count = (int) $db->getValue($query);
$name  = (string) $db->getValue($query);

// ── Obtener el SQL generado para debug ──
$sql = $query->build();
echo $sql;

// ── Debug en modo dev ──
if (_PS_MODE_DEV_) {
    PrestaShopLogger::addLog('SQL: ' . $query->build(), 1);
}