Skip to content

Instantly share code, notes, and snippets.

@randm-ch

randm-ch/DqlStatement.php Secret

Last active Aug 29, 2015
Embed
What would you like to do?
<?php
namespace MyCompany\MyApplication\Domain\Model;
use TYPO3\Flow\Annotations as Flow;
use Doctrine\ORM\Mapping as ORM;
use MyCompany\MyApplication\Utility\DataClassReflection;
/**
* @Flow\Entity
*/
class DqlStatement {
/**
* @var string
* @ORM\Column(type="text")
*/
protected $dql;
/**
* @var string
* @ORM\Column(type="text")
*/
protected $selectStatement;
/**
* @var string
* @Flow\Validate(type="NotEmpty")
* @ORM\Column(type="text")
*/
protected $fromStatement;
/**
* @var string
* @ORM\Column(type="text")
*/
protected $joinStatement;
/**
* @var string
* @ORM\Column(type="text")
*/
protected $whereStatement;
/**
* @var string
* @ORM\Column(type="text")
* @ORM\Column(nullable=true)
*/
protected $orderStatement;
/**
* @Flow\Inject
* @var \TYPO3\Flow\Reflection\ReflectionService
*/
protected $reflectionService;
/**
* @var array
* @Flow\Transient
*/
static protected $joins = array(
'normal' => 'JOIN',
'left' => 'LEFT JOIN',
'inner' => 'INNER JOIN',
'outer' => 'LEFT OUTER JOIN'
);
/**
* @var array
* @Flow\Transient
*/
static protected $operations = array(
'less' => '<',
'lesseq' => '<=',
'more' => '>',
'moreeq' => '>=',
'lessmore' => '<>',
'eq' => '=',
'neq' => '!=',
'like' => 'LIKE'
);
/**
* @var array
* @Flow\Transient
*/
static protected $conjunctions = array(
'and' => 'AND',
'or' => 'OR',
'paropen' => '(',
'parclose' => ')'
);
/**
* @var array
* @Flow\Transient
*/
static protected $functions = array(
'null' => 'IS NULL',
'notnull' => 'IS NOT NULL',
'now' => 'CURRENT_TIMESTAMP()'
);
/**
* @return string
*/
public function getDql() {
return $this->dql;
}
/**
* @param string $dql
* @return void
*/
public function setDql($dql) {
$this->dql = $dql;
}
/**
* @return string
*/
public function getSelectStatement() {
return $this->selectStatement;
}
/**
* @param string $selectStatement
* @return void
*/
public function setSelectStatement($selectStatement) {
$this->selectStatement = html_entity_decode($selectStatement);
}
/**
* @return string
*/
public function getFromStatement() {
return $this->fromStatement;
}
/**
* @param string $fromStatement
* @return void
*/
public function setFromStatement($fromStatement) {
$this->fromStatement = html_entity_decode($fromStatement);
}
/**
* @return string
*/
public function getJoinStatement() {
return $this->joinStatement;
}
/**
* @param string $joinStatement
* @return void
*/
public function setJoinStatement($joinStatement) {
$this->joinStatement = html_entity_decode($joinStatement);
}
/**
* @return string
*/
public function getWhereStatement() {
return $this->whereStatement;
}
/**
* @param string $whereStatement
* @return void
*/
public function setWhereStatement($whereStatement) {
$this->whereStatement = html_entity_decode($whereStatement);
}
/**
* @return string
*/
public function getOrderStatement() {
return $this->orderStatement;
}
/**
* @param string $orderStatement
* @return void
*/
public function setOrderStatement($orderStatement) {
$this->orderStatement = html_entity_decode($orderStatement);
}
/**
* Puts together all the parts into one single DQL statement
*
* @param array<\MyCompany\MyApplication\Utility\TagsinputTag> $tags
* @return void
* @throws \TYPO3\Flow\Security\Exception\AccessDeniedException
*/
public function generate($tags = array()) {
$fromStatements = (strlen(trim($this->fromStatement)) > 0) ? explode(',', $this->fromStatement) : array();
$joinStatements = (strlen(trim($this->joinStatement)) > 0) ? explode(',', $this->joinStatement) : array();
$selectStatements = (strlen(trim($this->selectStatement)) > 0) ? explode(',', $this->selectStatement) : array();
$whereStatements = (strlen(trim($this->whereStatement)) > 0) ? explode(',', $this->whereStatement) : array();
$orderStatements = (strlen(trim($this->orderStatement)) > 0) ? explode(',', $this->orderStatement) : array();
$tagCounter = 0;
/** @var \MyCompany\MyApplication\Utility\TagsinputTag $tag */
foreach($tags as $tag) {
if($tag->isDataTag()) {
// SELECT product.attribute FROM MyCompany\MyApplication\Domain\Model\Product product WHERE product.attribute = '...' ORDER BY product.price ASC
// -> SELECT data1.value AS attribute FROM MyCompany\MyApplication\Domain\Model\Product product JOIN product.data data1 WHERE ( data1.identifier = 'attribute' AND d.value = '...' ) ORDER BY data1.value ASC
list($sourcePropertyName, $dataIdentifier) = explode('.', $tag->getValue());
$dataJoinPropertyName = 'data'.$tagCounter++;
if(in_array($tag->getValue(), array_merge($selectStatements, $whereStatements, $orderStatements))) {
$joinStatements[] = 'LEFT OUTER JOIN';
$joinStatements[] = $sourcePropertyName.'.data '.$dataJoinPropertyName.' WITH '.$dataJoinPropertyName.'.identifier = \''.$dataIdentifier.'\'';
}
if(in_array($tag->getValue(), $selectStatements)) {
foreach($selectStatements as $i => $selectStatement) {
if($tag->getValue() === $selectStatement) {
$selectStatements[$i] = $dataJoinPropertyName.'.value AS '.$dataIdentifier;
}
}
}
if(in_array($tag->getValue(), $whereStatements)) {
foreach($whereStatements as $i => $whereStatement) {
if($tag->getValue() === $whereStatement) {
$dataValueField = $dataJoinPropertyName.'.value';
$whereStatements[$i] = $dataValueField;
}
}
}
if(in_array($tag->getValue(), $orderStatements)) {
foreach($orderStatements as $i => $orderStatement) {
if($tag->getValue().' ASC' === $orderStatement) {
$orderStatements[$i] = $dataJoinPropertyName.'.value ASC';
}
if($tag->getValue().' DESC' === $orderStatement) {
$orderStatements[$i] = $dataJoinPropertyName.'.value DESC';
}
}
}
}
else {
if(in_array($tag->getValue(), $selectStatements)) {
foreach($selectStatements as $i => $selectStatement) {
if($tag->getValue() === $selectStatement) {
$selectStatements[$i] = $selectStatement.' AS '.$tag->getPropertyValue().$tagCounter++;;
}
}
}
}
}
$fromStatement = (count($fromStatements) > 0) ? implode(',', $fromStatements) : '';
$joinStatement = (count($joinStatements) > 0) ? implode(',', $joinStatements) : '';
$selectStatement = (count($selectStatements) > 0) ? implode(',', $selectStatements) : '';
$whereStatement = (count($whereStatements) > 0) ? implode(',', $whereStatements) : '';
$orderStatement = (count($orderStatements) > 0) ? implode(',', $orderStatements) : '';
$this->dql = preg_replace('/[ ]+/', ' ', implode(' ', array(
$this->generateSelectStatement($selectStatement),
$this->generateFromStatements($fromStatement, $joinStatement),
$this->generateWhereStatement($whereStatement),
$this->generateOrderStatement($orderStatement)
)));
}
/**
* @param string $statement
* @return string
*/
protected function generateSelectStatement($statement = NULL) {
if(!is_string($statement) || strlen($statement) <= 0) $statement = $this->selectStatement;
$statement = (strlen($statement) > 0) ? str_replace(',', ', ', $statement) : DataClassReflection::getPropertyNameFromClassName($this->fromStatement);
return 'SELECT '.$statement;
}
/**
* @param string $statement
* @return string
*/
protected function generateFromStatements($statement = NULL, $joinStatement = NULL) {
if(!is_string($statement) || strlen($statement) <= 0) $statement = $this->fromStatement;
if(strlen($statement) <= 0) return '';
$fromStatementParts = array();
foreach(explode(',', $statement) as $fromClassName) {
$fromPropertyName = DataClassReflection::getPropertyNameFromClassName($fromClassName);
$currentJoinChain = array($fromPropertyName);
if(!is_string($joinStatement) || strlen($joinStatement) <= 0) $joinStatement = $this->joinStatement;
$joinStatementParts = array();
if(strlen($joinStatement) > 0) {
$joinStatements = explode(',', $joinStatement);
foreach($joinStatements as $i => $joinStatementPart) {
if(strpos($joinStatementPart, '.') === FALSE) {
// is a join operator (like 'LEFT JOIN')
$lookAhead = $joinStatements[$i+1];
list($source, $target) = explode('.', trim($lookAhead));
if(in_array($source, $currentJoinChain)) {
$joinStatementParts[] = $joinStatementPart;
if(preg_match('/^[\w\d]+ [\w\d]+( WITH .+)?$/i', $target)) {
// join statement was already prepared
$joinStatementParts[] = $lookAhead;
}
else {
$joinStatementParts[] = $source.'.'.$target.' '.$target;
$currentJoinChain[] = $target;
}
}
}
}
}
$fromStatementParts[] = $fromClassName.' '.$fromPropertyName.' '.implode(' ', $joinStatementParts);
}
return 'FROM '.implode(', ', $fromStatementParts);
}
/**
* @param string $statement
* @return string
*/
protected function generateWhereStatement($statement = NULL) {
if(!is_string($statement) || strlen($statement) <= 0) $statement = $this->whereStatement;
if(strlen($statement) <= 0) return '';
return 'WHERE '.str_replace(',', ' ', $statement);
}
/**
* @param string $statement
* @return string
*/
protected function generateOrderStatement($statement = NULL) {
if(!is_string($statement) || strlen($statement) <= 0) $statement = $this->orderStatement;
if(strlen($statement) <= 0) return '';
return 'ORDER BY '.str_replace(',', ', ', $statement);
}
/**
* @param string $propertyName
* @return boolean
*/
static public function isProperty($propertyName) {
return !in_array($propertyName, array_merge(self::getOperations(), self::getConjunctions(), self::getFunctions(), self::getJoins()));
}
/**
* @return array
*/
static public function getJoins() {
return self::$joins;
}
/**
* @return array
*/
static public function getOperations() {
return self::$operations;
}
/**
* @return array
*/
static public function getConjunctions() {
return self::$conjunctions;
}
/**
* @return array
*/
static public function getFunctions() {
return self::$functions;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment