/**
* returns order by statement for simularity calculations based on given fields and object ids
*
* @param $fields
* @param $objectId
*/
public function buildSimularityOrderBy($fields, $objectId)
{
try {
$fieldString = "";
$maxFieldString = "";
foreach ($fields as $f) {
if (!empty($fieldString)) {
$fieldString .= ",";
$maxFieldString .= ",";
}
$fieldString .= $this->db->quoteIdentifier($f->getField());
$maxFieldString .= "MAX(" . $this->db->quoteIdentifier($f->getField()) . ") as " . $this->db->quoteIdentifier($f->getField());
}
$query = "SELECT " . $fieldString . " FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a WHERE a.o_id = ?;";
OnlineShop_Plugin::getSQLLogger()->log("Query: " . $query, Zend_Log::INFO);
$objectValues = $this->db->fetchRow($query, $objectId);
OnlineShop_Plugin::getSQLLogger()->log("Query done.", Zend_Log::INFO);
$query = "SELECT " . $maxFieldString . " FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a";
OnlineShop_Plugin::getSQLLogger()->log("Query: " . $query, Zend_Log::INFO);
$maxObjectValues = $this->db->fetchRow($query);
OnlineShop_Plugin::getSQLLogger()->log("Query done.", Zend_Log::INFO);
if (!empty($objectValues)) {
$subStatement = array();
foreach ($fields as $f) {
$subStatement[] = "(" . $this->db->quoteIdentifier($f->getField()) . "/" . $maxObjectValues[$f->getField()] . " - " . $objectValues[$f->getField()] / $maxObjectValues[$f->getField()] . ") * " . $f->getWeight();
}
$statement = "ABS(" . implode(" + ", $subStatement) . ")";
OnlineShop_Plugin::getSQLLogger()->log("Similarity Statement: " . $statement, Zend_Log::INFO);
return $statement;
} else {
throw new Exception("Field array for given object id is empty");
}
} catch (Exception $e) {
OnlineShop_Plugin::getSQLLogger()->err($e);
return "";
}
}
