Convert a static query to dynamic one, with a having clause

Question

i am writing a patch for ubercart 3, for a attribute ignore module i am building. For this module, i need to be able to query alter the query for the uc_product_adjustments_form.

I need to convert the static mysql query to a dynamic one.

This is what the static query looks like:

$result = db_query("SELECT pa.nid, pa.aid, pa.ordering, pa.display, a.name, a.ordering, ao.aid, COUNT(po.oid) 
    FROM {uc_product_attributes} pa 
    LEFT JOIN {uc_attributes} a ON pa.aid = a.aid 
    LEFT JOIN {uc_attribute_options} ao ON a.aid = ao.aid 
    LEFT JOIN {uc_product_options} po ON ao.oid = po.oid AND po.nid = :po_nid 
    WHERE pa.nid = :pa_nid 
    AND pa.display <> :pa_display 
    GROUP BY ao.aid, pa.aid, pa.display, a.name, pa.ordering, a.ordering, pa.nid 
    HAVING count(po.oid) > 0 
    ORDER BY pa.ordering, a.ordering", array(':po_nid' => $nid, ':pa_nid' => $nid, ':pa_display' => 3));

And this is what i got so far:

$query = db_select('uc_product_attributes', 'pa');
  $query->leftJoin('uc_attributes', 'a', 'pa.aid = a.aid');
  $query->leftJoin('uc_attribute_options', 'ao', 'a.aid = ao.aid');
  $query->leftJoin('uc_product_options', 'po', 'ao.oid = po.oid AND po.nid = :po_nid', array(':po_nid' => $nid));
  $query->fields('pa', array('nid', 'aid', 'ordering', 'display'));
  $query->fields('a', array('name', 'ordering', 'aid'));
  $query->fields('ao', array('aid'));
  $query->condition('pa.nid', $nid);
  $query->condition('pa.nid', $nid);
  $query->groupBy('ao.aid');
  $query->groupBy('pa.aid');
  $query->groupBy('pa.display');
  $query->groupBy('a.name');
  $query->groupBy('pa.ordering');
  $query->groupBy('a.ordering');
  $query->groupBy('pa.nid');
  $result = $query->execute();

So everything works, except for the HAVING clause, which i don't understand to be honest.

Many thanks!

-- Tom

Answer 1

If I understand you right you just need to use the having() member function of the SelectQuery:

$query->having('count(po.oid) > 0');

FYI you have this line twice:

$query->condition('pa.nid', $nid);

I don't think it'll make a difference to the query result but it's probably best to remove the duplicate line.