Skip to content

Instantly share code, notes, and snippets.

@Xerkus
Forked from metalinspired/test.php
Created May 24, 2017 07:38
Show Gist options
  • Save Xerkus/254e98cc8ecea15a460767e3a83912be to your computer and use it in GitHub Desktop.
Save Xerkus/254e98cc8ecea15a460767e3a83912be to your computer and use it in GitHub Desktop.
<?php
$subSelect = new Select();
$subSelect
->from(['head_parent' => $this->config->getTable()])
->columns([])
->join(
['parent' => $this->config->getTable()],
new Expression(
'?.? >= ?.? AND ?.? < ?.?',
[
['parent' => Expression::TYPE_IDENTIFIER],
[$this->config->getLeftColumn() => Expression::TYPE_IDENTIFIER],
['head_parent' => Expression::TYPE_IDENTIFIER],
[$this->config->getLeftColumn() => Expression::TYPE_IDENTIFIER],
['parent' => Expression::TYPE_IDENTIFIER],
[$this->config->getRightColumn() => Expression::TYPE_IDENTIFIER],
['head_parent' => Expression::TYPE_IDENTIFIER],
[$this->config->getRightColumn() => Expression::TYPE_IDENTIFIER]
]
),
[]
)
->join(
['child' => $this->config->getTable()],
new Expression(
'?.? BETWEEN ?.? AND ?.?',
[
['child' => Expression::TYPE_IDENTIFIER],
[$this->config->getLeftColumn() => Expression::TYPE_IDENTIFIER],
['parent' => Expression::TYPE_IDENTIFIER],
[$this->config->getLeftColumn() => Expression::TYPE_IDENTIFIER],
['parent' => Expression::TYPE_IDENTIFIER],
[$this->config->getRightColumn() => Expression::TYPE_IDENTIFIER]
]
),
[
'id' => new Expression(
'?.?',
[
['child' => Expression::TYPE_IDENTIFIER],
[$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
]
),
'depth' => new Expression(
'(CASE WHEN ?.? = :childDepthId THEN 0 ELSE COUNT(*) END)',
[
['child' => Expression::TYPE_IDENTIFIER],
[$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
]
)
]
)
->group(
new Expression(
'?.?',
[
['child' => Expression::TYPE_IDENTIFIER],
[$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
]
)
);
$subSelect->having
->greaterThanOrEqualTo(new Expression('COUNT(*)'), 1);
if ($this->depthLimit) {
$subSelect->having
->lessThanOrEqualTo(new Expression('COUNT(*)'), $this->depthLimit);
}
$subSelect->where
->equalTo(
new Expression(
'?.?',
[
['head_parent' => Expression::TYPE_IDENTIFIER],
[$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
]
),
new Expression(':id')
)
->greaterThan(
new Expression(
'?.?',
[
['parent' => Expression::TYPE_IDENTIFIER],
[$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
]
),
$this->config->getRootNodeId()
);
if ($this->includeSearchingNode) {
$subSelect
->where
->or
->equalTo(
new Expression(
'?.?',
[
['child' => Expression::TYPE_IDENTIFIER],
[$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
]
),
new Expression(':searchNodeId'));
}
$select = new Select();
$select
->from(['q' => $subSelect])
->columns(['depth'])
->join(
['t' => $this->config->getTable()],
new Expression(
'?.? = ?.?',
[
['t' => Expression::TYPE_IDENTIFIER],
[$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER],
['q' => Expression::TYPE_IDENTIFIER],
[$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
]
),
$this->columns
)
->order(new Expression(
'?.? ASC',
[
['t' => Expression::TYPE_IDENTIFIER],
[$this->config->getLeftColumn() => Expression::TYPE_IDENTIFIER]
]
)
);
$statement = $this->sql->prepareStatementForSqlObject($select);
$parameters = [
':id' => $id,
':childDepthId' => $id
];
if ($this->includeSearchingNode) {
$parameters[':searchNodeId'] = $id;
}
$result = $this->statement->execute($parameters);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment