Doctrine 1.2: get products where associated simple products have stock

Question

I am struggling on how to generate the correct query with Doctrine 1.2 to retrieve my products where the associated simple_products have a total stock greater than 1 (at least one of them has a stock of 1 or more)

My schema.yml:

Product:
  columns:
    id:
Simple_product:
  columns:
    id:
    quantity:   { type: integer }
    product_id: { type: integer(11), notnull: true }
  relations:
    product_id: { class: Product, local: product_id, foreign: id, foreignAlias: Simple_products }

Example query I tried:

$qProducts = Doctrine_Query::create()
  ->select('p.*, SUM(s.quantity)')
  ->from('Product p')
  ->innerJoin('p.Simple_products s')
  ->where('p.category_id = ?', $_SESSION['eshop'],
  ->andWhere('p.brand = ?', $_SESSION['eshop'],
  ->andWhere('p.is_active = 1')
  ->having('SUM(s.quantity) > 0');

This query doesn't return expected results, it returns an empty collection even though I know the database contains products with associated simple products with stock.

What am I doing wrong?

EDIT:

$qProducts->getSqlQuery() returns:

SELECT p.id AS p__id, p.sku AS p__sku, p.name AS p__name, p.gender AS p__gender, p.description AS p__description, p.custom_price AS p__custom_price, p.base_price AS p__base_price, p.sales_price AS p__sales_price, p.eshop_price AS p__eshop_price, p.brand AS p__brand, p.product_url AS p__product_url, p.image_url AS p__image_url, p.is_active AS p__is_active, p.category_id AS p__category_id, SUM(s.quantity) AS s__0 FROM product p INNER JOIN simple_product s ON p.id = s.product_id WHERE (p.category_id = ? AND p.brand = ? AND p.is_active = 1) HAVING SUM(s.quantity) > 0

You do have a quantity field in your simple_product table right ? (your schema.yml excerpt doesn't show it. — rtome, Dec 28 '14 at 13:50
Also, if you do **echo $qProducts->getSqlQuery();** what do you get ? If you get a reasonable query what does it display when run via the mysql command-line client ? (or phpMyAdmin or adminer ...) Does it return something or just 0 rows ? — rtome, Dec 28 '14 at 13:50

score 1 · Accepted Answer · answered Jan 02 '15 at 13:16

The DQL query I was looking for:

$qNbProducts = Doctrine_Query::create()
  ->select('p.id')
  ->addSelect('SUM(s.quantity) as sum_stock')
  ->from('Product p')
  ->innerJoin('p.Category c')
  ->leftJoin('p.Simple_products s')
  ->where('c.root_id = ?', $categoryId)
  ->andWhere('p.brand = ?', $_SESSION['eshop'])
  ->andWhere('p.is_active = 1')
  ->groupBy('p.id')
  ->having('sum_stock > 0');

Doctrine 1.2: get products where associated simple products have stock

1 Answers1