<?php
namespace App\Repository;
use Doctrine\ORM\EntityRepository;
/**
* Repository
*
*/
class BaseRepository extends EntityRepository
{
public function findFiltered(array $filters = array(), $order = array(), $limit = null, $offset = 0, $options = array())
{
// Grab the querybuilder
$queryBuilder = $this->buildQuery($filters);
// Order
if(count($order))
{
foreach($order AS $k => $v)
$queryBuilder->addOrderBy($k, $v);
}
// Limit
if($limit)
$queryBuilder->setMaxResults($limit);
// Offset
if($offset)
$queryBuilder->setFirstResult($offset);
// Get the query
$query = $queryBuilder->getQuery();
// Execute and return
return $query->getResult();
}
public function findOneFiltered(array $filters = array(), $order = array())
{
// Grab the querybuilder
$queryBuilder = $this->buildQuery($filters);
$queryBuilder->setMaxResults(1);
// Order
if(count($order))
{
foreach($order AS $k => $v)
$queryBuilder->addOrderBy($k, $v);
}
// Get the query
$query = $queryBuilder->getQuery();
// Execute and return
return $query->getOneOrNullResult();
}
public function findFilteredQueryBuilder(array $filters = array(), $order = array())
{
// Grab the querybuilder
$queryBuilder = $this->buildQuery($filters);
// Order
if(count($order))
{
foreach($order AS $k => $v)
$queryBuilder->addOrderBy($k, $v);
}
// Get the query
return $queryBuilder;
}
public function findFilteredQuery(array $filters = array(), $order = array())
{
return $this->findFilteredQueryBuilder($filters, $order)->getQuery();
}
public function buildDefaultQueryBuilder($queryBuilder, $alias, $filters)
{
// Get the class meta data
$metaData = $this->getEntityManager()->getClassMetadata($this->getClassName());
// Extract the field mappings
$fieldMappings = $metaData->fieldMappings;
// Extract the association mappings
$associationMappings = $metaData->associationMappings;
// Loop the parent classes
foreach($metaData->parentClasses AS $someClass)
{
// Get the class meta data
$classMetaData = $this->getEntityManager()->getClassMetadata($someClass);
// Merge the field mappings
foreach($classMetaData->fieldMappings AS $k => $v)
{
// Add it in if not already got it
if(!array_key_exists($k, $fieldMappings))
$fieldMappings[$k] = $v;
}
// Merge the association mappings
foreach($classMetaData->associationMappings AS $k => $v)
{
// Add it in if not already got it
if(!array_key_exists($k, $associationMappings))
$associationMappings[$k] = $v;
}
}
// Loop the field mappings
foreach($fieldMappings AS $key => $someMappedField)
{
// Got this in our filter?
if(array_key_exists($key, $filters))
{
// Grab the filter
$theFilter = $filters[$key];
// Basic filter match?
if(!is_array($theFilter))
{
// Is it null?
if(is_null($theFilter))
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' IS NULL');
}
else
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' = :' . $key)
->setParameter($key, $theFilter);
}
}
// Complex filter match
else
{
// Extract the bits
list($type, $data) = $theFilter;
// Deal with in, not_in
if(in_array($type, array("in", "not_in")))
{
// What type
if($type == "in")
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' IN(:' . $key . ')')
->setParameter($key, $data);
}
elseif($type == "not_in")
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' NOT IN(:' . $key . ')')
->setParameter($key, $data);
}
}
// Deal with nulls
elseif(in_array($type, array("is_null", "not_null")))
{
// What type
if($type == "is_null" && is_bool($data))
{
if($data)
$queryBuilder->andWhere($this->alias . '.' . $key . ' IS NULL');
else
$queryBuilder->andWhere($this->alias . '.' . $key . ' IS NOT NULL');
}
elseif($type == "not_null" && is_bool($data))
{
if($data)
$queryBuilder->andWhere($this->alias . '.' . $key . ' IS NOT NULL');
else
$queryBuilder->andWhere($this->alias . '.' . $key . ' IS NULL');
}
else
throw new \Exception("Invalid query paramater 'null' value(s)");
}
// Deal with numeric gt, gte, lt, lte
elseif(in_array($someMappedField['type'], array("integer", "decimal")) && in_array($type, array("lt", "lte", "gt", "gte")))
{
// Make sure data is numeric
if(!is_numeric($data))
throw new \Exception("Cannot use non-numeric data for '" . $type . "' query");
// Less than
if($type == "lt")
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' < :' . $key)
->setParameter($key, $data);
}
// Less than or equal
elseif($type == "lte")
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' <= :' . $key)
->setParameter($key, $data);
}
// Greater than
elseif($type == "gt")
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' > :' . $key)
->setParameter($key, $data);
}
// Greater than or equal
elseif($type == "gte")
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' >= :' . $key)
->setParameter($key, $data);
}
}
// Deal with datetimes gt, gte, lt, lte
elseif(in_array($someMappedField['type'], array("datetime")) && in_array($type, array("lt", "lte", "gt", "gte")))
{
// Make sure data is a datetime
if(!$data instanceof \DateTime)
throw new \Exception("Data must be a DateTime instance for '" . $type . "' query");
// Less than
if($type == "lt")
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' < :' . $key)
->setParameter($key, $data->format('Y-m-d H:i:s'));
}
// Less than or equal
elseif($type == "lte")
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' <= :' . $key)
->setParameter($key, $data->format('Y-m-d H:i:s'));
}
// Greater than
elseif($type == "gt")
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' > :' . $key)
->setParameter($key, $data->format('Y-m-d H:i:s'));
}
// Greater than or equal
elseif($type == "gte")
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' >= :' . $key)
->setParameter($key, $data->format('Y-m-d H:i:s'));
}
}
else
{
throw new \Exception("Invalid query paramater of type '" . $type . "' on field '" . $key . "'. Available options: in, not_in, lt, lte, gt, gte, is_null, not_null");
}
}
}
}
// Loop the association mappings
foreach($associationMappings AS $key => $someMappedAssociation)
{
// Got this in our filter?
if(array_key_exists($key, $filters))
{
// Grab the filter
$theFilter = $filters[$key];
// Basic filter match?
if(!is_array($theFilter))
{
// Is it null?
if(is_null($theFilter))
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' IS NULL');
}
else
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' = :' . $key)
->setParameter($key, $theFilter);
}
}
// Complex filter match
else
{
// Extract the bits
list($type, $data) = $theFilter;
// Deal with in, not_in
if(in_array($type, array("in", "not_in")))
{
// What type
if($type == "in")
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' IN(:' . $key . ')')
->setParameter($key, $data);
}
elseif($type == "not_in")
{
$queryBuilder->andWhere($this->alias . '.' . $key . ' NOT IN(:' . $key . ')')
->setParameter($key, $data);
}
}
// Deal with nulls
elseif(in_array($type, array("is_null", "not_null")))
{
// What type
if($type == "is_null" && is_bool($data))
{
if($data)
$queryBuilder->andWhere($this->alias . '.' . $key . ' IS NULL');
else
$queryBuilder->andWhere($this->alias . '.' . $key . ' IS NOT NULL');
}
elseif($type == "not_null" && is_bool($data))
{
if($data)
$queryBuilder->andWhere($this->alias . '.' . $key . ' IS NOT NULL');
else
$queryBuilder->andWhere($this->alias . '.' . $key . ' IS NULL');
}
else
throw new \Exception("Invalid query paramater 'null' value(s)");
}
// Deal with has all
elseif(in_array($type, array("has_all")))
{
// Make sure we got some
if(count($data))
{
// Loop them
foreach($data AS $itemKey => $someItem)
{
$queryBuilder->andWhere(':' . $key . $itemKey . ' MEMBER OF ' . $this->alias . '.' . $key)
->setParameter($key . $itemKey, $someItem);
}
}
}
else
{
throw new \Exception("Invalid query paramater of type '" . $type . "' on field '" . $key . "'. Available options: in, not_in, lt, lte, gt, gte, is_null, not_null, has_all");
}
}
}
}
return $queryBuilder;
}
}