Skip to content

Instantly share code, notes, and snippets.

@unclecheese
Last active June 12, 2019 03:46
Show Gist options
  • Save unclecheese/7399a4afa370ffa810a88caf68adf6ac to your computer and use it in GitHub Desktop.
Save unclecheese/7399a4afa370ffa810a88caf68adf6ac to your computer and use it in GitHub Desktop.
<?php
namespace SilverStripe\Snapshots;
use SilverStripe\Core\Injector\Injectable;
use SilverStripe\ORM\Connect\Query;
use SilverStripe\ORM\Limitable;
use SilverStripe\ORM\Queries\SQLSelect;
use SilverStripe\ORM\Map;
use ArrayIterator;
use Exception;
use SilverStripe\ORM\Sortable;
use InvalidArgumentException;
/**
* There isn't great support for UNION queries in the ORM, so this is a patchwork fix.
* The main thing it needs to provide is pagination for the UNION query in the snapshot
* admin. Unfortunately, that means it has to implement everything in SS_List, as well.
* Many of the methods are just stubs that error out, while others are actually implemented
* in a good-enough way for an internal API.
*
* This API should be considered unstable, and exists only to serve the snapshot-admin needs.
*
* @internal
*/
class UnionList implements Limitable, Sortable
{
use Injectable;
/**
* @var SQLSelect[]
*/
protected $queries = [];
/**
* @var Query
*/
protected $result;
/**
* @var array
*/
protected $limit = [];
/**
* @var UnionSelect
*/
protected $select;
/**
* UnionList constructor.
* @param SQLSelect[] ...$queries
*/
public function __construct(...$queries)
{
$this->select = new UnionSelect($queries);
}
/**
* @param int $limit
* @param int $offset
* @return Limitable|void
*/
public function limit($limit, $offset = 0)
{
$this->select->setLimit($limit, $offset);
}
public function sort()
{
$count = func_num_args();
if ($count == 0) {
return $this;
}
if ($count > 2) {
throw new InvalidArgumentException('This method takes zero, one or two arguments');
}
if ($count == 2) {
$col = null;
$dir = null;
list($col, $dir) = func_get_args();
if (!in_array(strtolower($dir), ['desc', 'asc'])) {
user_error('Second argument to sort must be either ASC or DESC');
}
$sort = [$col => $dir];
} else {
$sort = func_get_arg(0);
}
$this->select->setOrderBy($sort);
}
public function canSortBy($by)
{
return $this->select->canSortBy($by);
}
public function reverse()
{
$this->select->reverseOrderBy();
}
/**
* @return ArrayIterator
*/
public function getIterator()
{
return new ArrayIterator($this->toArray());
}
/**
* @return mixed|Query
*/
public function last()
{
return $this->select->lastRow()->execute();
}
/**
* @param string $colName
* @return array
*/
public function column($colName = "ID")
{
$fieldExpression = $this->select->expressionForField($colName);
$this->select->setSelect(array());
$this->select->selectField($fieldExpression, $colName);
return $this->select->execute()->column($colName);
}
/**
* @return int
*/
public function count()
{
return $this->select->count();
}
/**
* @return mixed|SQLSelect
*/
public function first()
{
return $this->select->firstRow()->execute();
}
/**
* @param string $keyfield
* @param string $titlefield
* @return Map
*/
public function map($keyfield = 'ID', $titlefield = 'Title')
{
return new Map($this, $keyfield, $titlefield);
}
/**
* @param callable $callback
* @return Limitable|void
*/
public function each($callback)
{
foreach ($this as $row) {
$callback($row);
}
}
/**
* @param string $key
* @param mixed $value
* @return array|null
*/
public function find($key, $value)
{
foreach ($this as $row) {
if (isset($row[$key]) && $row[$key] == $value) {
return $row;
}
}
return null;
}
/**
* @return array
*/
public function toNestedArray()
{
return $this->toArray();
}
/**
* @return array
*/
public function toArray()
{
$result = $this->select->execute();
$rows = [];
foreach ($result as $row) {
$rows[] = $row;
}
return $rows;
}
/**
* @param mixed $offset
* @throws Exception
*/
public function offsetExists($offset)
{
throw new Exception(sprintf('The %s method is not supported for %s', __FUNCTION__, __CLASS__));
}
/**
* @param mixed $offset
* @return mixed|void
* @throws Exception
*/
public function offsetGet($offset)
{
throw new Exception(sprintf('The %s method is not supported for %s', __FUNCTION__, __CLASS__));
}
/**
* @param mixed $offset
* @throws Exception
*/
public function offsetUnset($offset)
{
throw new Exception(sprintf('The %s method is not supported for %s', __FUNCTION__, __CLASS__));
}
/**
* @param mixed $offset
* @param mixed $value
* @throws Exception
*/
public function offsetSet($offset, $value)
{
throw new Exception(sprintf('The %s method is not supported for %s', __FUNCTION__, __CLASS__));
}
/**
* @param mixed $item
* @throws Exception
*/
public function remove($item)
{
throw new Exception(sprintf('The %s method is not supported for %s', __FUNCTION__, __CLASS__));
}
/**
* @param mixed $item
* @throws Exception
*/
public function add($item)
{
throw new Exception(sprintf('The %s method is not supported for %s', __FUNCTION__, __CLASS__));
}
}
<?php
namespace SilverStripe\Snapshots;
use SilverStripe\ORM\Queries\SQLSelect;
use InvalidArgumentException;
use BadMethodCallException;
class UnionSelect extends SQLSelect
{
/**
* @var SQLSelect[]
*/
protected $queries;
/**
* UnionSelect constructor.
* @param array $queries
* @param array $where
* @param array $orderby
* @param array $groupby
* @param array $having
* @param array $limit
*/
public function __construct(
$queries = [],
$where = [],
$orderby = [],
$groupby = [],
$having = [],
$limit = []
) {
$this->queries = $queries;
if (sizeof($this->queries) < 2) {
throw new InvalidArgumentException('Must provide at least two UNION selects');
}
foreach ($this->queries as $query) {
if (!$query instanceof SQLSelect) {
throw new InvalidArgumentException(sprintf(
'%s only accepts %s instances in its constructor',
__CLASS__,
SQLSelect::class
));
}
}
parent::__construct('*', [], $where, $orderby, $groupby, $having, $limit);
}
public function execute()
{
/* @var SQLSelect $query */
foreach ($this->queries as $query) {
if (!empty($query->getOrderBy())) {
user_error('Union selects cannot contain queries with their own ORDER BY clauses', E_USER_ERROR);
}
if (!empty($query->getLimit())) {
user_error('Union selects cannot contain queries with their own LIMIT clauses', E_USER_ERROR);
}
}
return parent::execute(); // TODO: Change the autogenerated stub
}
public function sql(&$allParams = array())
{
if (!$allParams) {
$allParams = [];
}
$unions = [];
/* @var SQLSelect $query */
foreach ($this->queries as $query) {
$unions[] = $query->sql($params);
$allParams = array_merge($allParams, $params);
}
$unionSQL = '' . implode(' UNION ALL ', $unions) . '';
$this->from = ['blah' => sprintf('(%s', $unionSQL)];
$this->setSelect([
'"ID"',
]);
return parent::sql($allParams);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment