/**
* Returns a list of records with the given values
*
* @history
* 2013.09.30:
* (AT) Initial release
* 2013.10.25:
* (AT) Improve thrown exceptions
* 2013.11.25:
* (AT) Add support for _limit and _offset query parameters
* (AT) Set default limit to 10,000 rows
* 2014.02.27:
* (AT) Fix bad logic when setting limit and offset on OCI
* 2014.03.04:
* (AT) Split function into a query-generating function and a query
* execution part to make it easier to extend query functionality
* 2014.03.05:
* (AT) Handle queryUnique flag
* 2014.03.06:
* (AT) Rename array keys when using the OCI driver
* 2014.03.18:
* (AT) Make sure the method is still persistent
*
* @version 2014.03.18
* @author (AT) Alberto Trevino, Brigham Young Univ. <*****@*****.**>
*
* @param array $parameters
* List of query parameters
* @param string $class_name
* Use array to return list as an array, or class name to return objects
* @param array $ctorargs
* Constructor arguments if returning objects
* @return array Record list
* @throws \Cougar\Exceptions\Exception;
* @throws \Cougar\Exceptions\AccessDeniedException;
*/
public function query(array $parameters = array(), $class_name = "array", array $ctorargs = array())
{
# Make sure we are still persistent
if (!$this->__persistent) {
throw new Exception("Querying is no longer allowed on this model");
}
# See if querying is allowed
if (!$this->__allowQuery) {
throw new AccessDeniedException("This model does not support querying");
}
# Set the view (if it hasn't been changed)
if ($this->__currentView == "__default__" && $this->__queryView !== "__default__") {
$this->__setView($this->__queryView);
}
# Extract the columns and aliases for the columns we can query
$query_aliases = array_intersect($this->__alias, $this->__queryProperties);
$columns = array();
$key_map = array();
foreach ($this->__queryProperties as $property) {
if ($this->__visible[$property]) {
if ($this->__exportAlias[$property] == $this->__columnMap[$property]) {
$columns[$property] = $this->__columnMap[$property];
$key_map[$this->__columnMap[$property]] = $this->__columnMap[$property];
} else {
$columns[$property] = $this->__columnMap[$property] . " AS " . $this->__exportAlias[$property];
$key_map[$this->__exportAlias[$property]] = $this->__exportAlias[$property];
}
}
}
# Recursively iterate through the query parameters
$this->iterateQueryParameters($parameters, $query_aliases, $columns, $key_map);
# Prepare the array that will hold the parameter values
$values = array();
# Set the default limit to 10,000 rows
$limit = 10000;
$offset = 0;
$used_parameters = array();
# Prepare the query and execute the statement
if ($this->__queryUnique) {
$query = "SELECT DISTINCT ";
} else {
$query = "SELECT ";
}
$query .= implode(", ", $columns) . " FROM " . $this->__table . " " . implode(" ", $this->__joins);
$where_clause = QueryParameter::toSql($parameters, $this->__columnMap, $query_aliases, $this->__caseInsensitive, $values, $used_parameters, $limit, $offset);
if ($where_clause) {
$query .= " WHERE " . $where_clause;
}
# Set the limit and offset
$limit = (int) $limit;
$offset = (int) $offset;
if ($this->__pdo->getAttribute(PDO::ATTR_DRIVER_NAME) == "oci") {
if ($where_clause) {
$query .= " AND ";
} else {
$query .= " WHERE ";
}
$query .= "ROWNUM > " . $offset . " AND ROWNUM <= " . ($offset + $limit);
} else {
$query .= " LIMIT " . $limit . " OFFSET " . $offset;
}
# Execute the query
$results = $this->executeQuery($query, $values, $class_name, $ctorargs);
# Oracle will turn all column names as uppercase. This will rename them
# if we are returning an array and are using OCI
if ($class_name == "array" && count($results) > 0 && $this->__pdo->getAttribute(PDO::ATTR_DRIVER_NAME) == "oci") {
# Change the keys in the key map to uppercase
$key_map = array_change_key_case($key_map, CASE_UPPER);
# Rename the keys
$results = Arrays::renameKeys($results, $key_map);
}
# Return the result
return $results;
}
/**
* @covers \Cougar\UtilQueryParameter::__construct
* @covers \Cougar\Util\QueryParameter::toSql
*/
public function testToSqlCompoundWithLimitAndOffsetNoReferences()
{
$query = array(new QueryParameter(array(new QueryParameter("property1", "value1"), new QueryParameter("property2", "value2", "<", "AND", true))), new QueryParameter(array(new QueryParameter("property3", "value3", "**", "OR"), new QueryParameter("property4", "value4", ">=")), null, "=", "OR"), new QueryParameter("_limit", "50"), new QueryParameter("_offset", "100"));
$column_map = array("property1" => "Column1", "property2" => "Column2", "property3" => "Column3", "property4" => "Column4");
$aliases = array("property1" => "property1", "property2" => "property2", "property3" => "property3", "property4" => "property4");
$values = array();
$this->assertEquals("(Column1 = :property1 AND " . "(Column2 < :property2 OR Column2 IS NULL)) OR " . "(Column3 LIKE :property3 AND " . "Column4 >= :property4)", QueryParameter::toSql($query, $column_map, $aliases, false, $values));
$this->assertCount(4, $values);
$this->assertArrayHasKey("property1", $values);
$this->assertArrayHasKey("property2", $values);
$this->assertArrayHasKey("property3", $values);
$this->assertArrayHasKey("property4", $values);
$this->assertEquals("value1", $values["property1"]);
$this->assertEquals("value2", $values["property2"]);
$this->assertEquals("%value3%", $values["property3"]);
$this->assertEquals("value4", $values["property4"]);
}
