PrestaShop SQL: the joins you need for useful catalogue reports

Most useful PrestaShop reports are not stored in one table.

The challenge is not writing a long query. It is understanding which entity owns each piece of data and joining it without creating duplicates.

1. Start with the row grain

Before writing SQL, define what one output row represents.

ONE ROW PER PRODUCT

or:

ONE ROW PER COMBINATION

If you do not define the grain first, every additional join can multiply rows.

2. Product + language

SELECT
    p.id_product,
    p.reference,
    pl.name
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1;

The id_lang and id_shop conditions are part of the dataset specification, not cosmetic filters.

3. Add stock

For simple products:

LEFT JOIN ps_stock_available sa
    ON sa.id_product = p.id_product
    AND sa.id_product_attribute = 0

For variants, the row grain must switch to combinations and join with id_product_attribute.

4. Add the default category

A product can belong to multiple categories. If you only want its default category, use the default-category relationship rather than joining every association.

LEFT JOIN ps_category_lang cl
    ON cl.id_category = p.id_category_default
    AND cl.id_lang = 1
    AND cl.id_shop = 1

5. Manufacturer

LEFT JOIN ps_manufacturer m
    ON m.id_manufacturer = p.id_manufacturer

Now your report can include brand/manufacturer without a second export.

6. Count category associations without multiplying rows

Use aggregation:

SELECT
    p.id_product,
    p.reference,
    COUNT(DISTINCT cp.id_category) AS category_count
FROM ps_product p
LEFT JOIN ps_category_product cp
    ON cp.id_product = p.id_product
GROUP BY p.id_product, p.reference;

7. Images are one-to-many too

Joining ps_image directly can produce one row per image. Decide whether your report needs:

• cover image only,
• image count,
• every image as a separate row.

8. A practical product report

SELECT
    p.id_product,
    p.reference,
    pl.name,
    m.name AS manufacturer,
    cl.name AS default_category,
    sa.quantity,
    p.price,
    p.active
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
LEFT JOIN ps_manufacturer m
    ON m.id_manufacturer = p.id_manufacturer
LEFT JOIN ps_category_lang cl
    ON cl.id_category = p.id_category_default
    AND cl.id_lang = 1
    AND cl.id_shop = 1
LEFT JOIN ps_stock_available sa
    ON sa.id_product = p.id_product
    AND sa.id_product_attribute = 0
ORDER BY p.id_product DESC;

9. Detect accidental row multiplication

Compare:

COUNT(*)

with:

COUNT(DISTINCT p.id_product)

If they diverge unexpectedly, one of your joins changed the grain.

10. Build reports in layers

BASE ENTITY
    ↓
LANGUAGE
    ↓
SHOP CONTEXT
    ↓
ONE-TO-ONE DATA
    ↓
ONE-TO-MANY DATA
    ↓
AGGREGATE
    ↓
VERIFY ROW COUNT

Good SQL reporting is dataset design. Once the row grain and context are explicit, the query becomes easier to reason about and safer to reuse.

Where to go from here

Start with the saved SQL Manager checks when you need a focused audit, then promote only proven queries into reports. For pricing datasets, include the context rules from the Specific Prices guide rather than treating product.price as the final answer.