---
title: DbQuery Builder — API fluida para queries SQL
section: database
slug: dbquery
description: "Referencia completa del DbQuery Builder de PrestaShop: select, from, join, where, orderBy, limit, group, having y metodo build()."
keywords: prestashop DbQuery builder query SQL select from join where orderBy limit group
last_updated: 2024-12-01
source_url: "https://ayudaprestashop.es/database/dbquery"
---

# DbQuery Builder — API fluida para queries SQL

> Referencia completa del DbQuery Builder de PrestaShop: select, from, join, where, orderBy, limit, group, having y metodo build().

DbQuery es un constructor de queries SQL de tipo fluent que PrestaShop proporciona como alternativa segura a la concatenacion de strings. Siempre genera el SQL a traves de `build()` y se ejecuta con `Db::getInstance()`.

## Metodos del DbQuery Builder

| Metodo | SQL generado | Descripcion |
| --- | --- | --- |
| select('p.id_product, pl.name') | SELECT p.id_product, pl.name | Campos a seleccionar |
| from('product', 'p') | FROM ps_product p | Tabla principal (con prefijo automatico) |
| join('JOIN ps_category c...') | JOIN ... | JOIN literal (con prefijo manual) |
| leftJoin('table', 'alias', 'on') | LEFT JOIN ps_table alias ON ... | LEFT JOIN con prefijo automatico |
| innerJoin('table', 'alias', 'on') | INNER JOIN ps_table alias ON ... | INNER JOIN |
| where('p.active = 1') | WHERE p.active = 1 | Condicion (se combinan con AND) |
| having('cnt > 5') | HAVING cnt > 5 | Condicion HAVING |
| orderBy('p.date_add DESC') | ORDER BY p.date_add DESC | Ordenacion |
| groupBy('p.id_category') | GROUP BY p.id_category | Agrupacion |
| limit(20, 0) | LIMIT 20 OFFSET 0 | Paginacion (limit, offset) |
| build() | SQL string | Genera el SQL final como string |

## Queries con JOIN

*Queries complejas con multiples JOINs*

```php
<?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);
```

## Filtros WHERE y HAVING

*Construccion dinamica de condiciones WHERE*

```php
<?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);
```

## Paginacion y ordenacion

*Paginacion con DbQuery*

```php
<?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);
```

## Subqueries

*Subqueries con DbQuery*

```php
<?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);
```

## Ejecutar la query

*Metodos de Db:: para ejecutar queries*

```php
<?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);
}
```


---

*Fuente: [https://ayudaprestashop.es/database/dbquery](https://ayudaprestashop.es/database/dbquery). Version Markdown generada automaticamente para consumo por LLMs.*