Magento 2 Query Param Binding – How to Bind Query Parameters

databasemagento2querysql

->having(
        'COUNT(*) ' . '<= 3 '
    )

Hardcoded value 3 need to come From:

This is the query to return most viewed products biggest views number:

SELECT COUNT(report_event.object_id) FROM catalog_product_entity,
report_event WHERE catalog_product_entity.entity_id =
report_event.object_id GROUP BY catalog_product_entity.entity_id ORDER
BY COUNT(report_event.object_id) DESC LIMIT 1

this number that query gives back needs to be multiplied by 

$helper->getPopularProductPriority($storeId)

and the return of these actions should change the hardcoded number.
Whats the best way to achieve this?

EDIT:

$priorityView = 3;
$lowerPriorityView = 2;

->having(
        'COUNT(*) ' . '<= ? ',
        $priorityView . 'AND' . ' COUNT(*) ' . '>= ? ',$lowerPriorityView
    )

Xdebug query shows: 

HAVING (COUNT(*) <= 3.000000 )

OR: 

->having(
        'COUNT(*) ' . '>= ? ',
        $lowerPriorityView . ' AND ' . 'COUNT(*) ' . '<= ? ',$priorityView
    )

returns:

HAVING (COUNT(*) >= '2 AND COUNT(*) <= ? ' )

Why is that?

Best Answer

You can rebuild your code part like that:

->having(
    'COUNT(*) ' . '<= ? ',
    $variableWhereValueWillBeStored
)

Just use second param to provide needed value

To understand deeper, please see original method from the Zend_Db_Select class:

/**
 * Adds a HAVING condition to the query by AND.
 *
 * If a value is passed as the second param, it will be quoted
 * and replaced into the condition wherever a question-mark
 * appears. See {@link where()} for an example
 *
 * @param string $cond The HAVING condition.
 * @param mixed    $value OPTIONAL The value to quote into the condition.
 * @param int      $type  OPTIONAL The type of the given value
 * @return Zend_Db_Select This Zend_Db_Select object.
 */
public function having($cond, $value = null, $type = null)
{
    if ($value !== null) {
        $cond = $this->_adapter->quoteInto($cond, $value, $type);
    }

    if ($this->_parts[self::HAVING]) {
        $this->_parts[self::HAVING][] = self::SQL_AND . " ($cond)";
    } else {
        $this->_parts[self::HAVING][] = "($cond)";
    }

    return $this;
}

As you can see, methods accept three parameters: $cond (string value with quotes), $value (the value to quote into the condition. Also can be an array if you will use couple quotes), $type - type of given value (in your case now, you should not use this param).

So, using your second code part example, you should rebuild it like that:

$priority = 3;
$lowerPriority = 2;

->having(
    'COUNT(*) <= ? AND COUNT(*) >= ? ', [$priority, $lowerPriority]
)
Related Topic