Database
Database copied to clipboard
hoaproject
Alias in FROM clause.
Hello,
I'm trying to use a Hoa\Database\Query\Select object in an INNER JOIN clause.
My goal is to get the following query:
SELECT *
FROM Foo AS F
INNER JOIN (
SELECT *
FROM Bar
) AS B
ON F.Foo_ID = B.Bar_ForeignKey
But I wasn't able to alias correctly my two sources.
- For the
Footable I can make an alias without the keywordASlikefrom('Foo F'). - The second alias is not well placed due to the
ONclause (it's work well without).
There are my test code and the output.
$req = new \Hoa\Database\Query\Select();
$req->from('Foo')
->_as('F')
->innerJoin(
(new \Hoa\Database\Query\Select())
->from('Bar')
)
->on('F.Foo_ID = B.Bar_ForeignKey')
->_as('B');
SELECT *
FROM Foo
INNER JOIN (
SELECT *
FROM Bar
)
ON F.Foo_ID = B.Bar_ForeignKey
AS B
PS: Moreover the fact to don't alias an Hoa\Database\Query\Select object in an INNER JOIN clause throw a SQL error : #1248 - Every derived table must have its own alias.
Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.
First idea for the second point: Create Join::_as
public function _as ( $alias ) {
$this->_from[key($this->_from)] = current($this->_from) .
' AS ' . $alias;
return $this;
}
It should be used like:
$req = new \Hoa\Database\Query\Select();
$req->from('Foo')
->_as('F')
->innerJoin(
(new \Hoa\Database\Query\Select())
->from('Bar')
)
->_as('B')
->on('F.Foo_ID = B.Bar_ForeignKey');
And for the first point: Change SelectCore::_join to something like this:
protected function _join ( $type, $source ) {
if(empty($this->_from))
return $this;
end($this->_from);
$key = key($this->_from);
$value = array_pop($this->_from);
if(!is_int($key))
$value .= ' AS ' . $key;
if($source instanceof self)
$source = '(' . $source . ')';
$this->_from[] = $value . ' ' . $type . ' ' . $source;
return new Join($this, $this->_from);
}
The changes are:
- We store the last
fromelement and remove it from$this->_from. - If
keyis an alias, we write it directly before theJOINclause. - Finally we add it to
$this->_fromlike an non aliased from.
Note: SelectCore::_as must not be called after this operation...
Finnaly i look now :p For your second point about _as('B')
I think its more logical to write
->innerJoin(
(new \Hoa\Database\Query\Select())
->from('Bar')
->_as('B')
)
So the AS f is not well placed :) when we have jonction, i look this after
No, B is an alias for the data returned by the sub query.
Look my examples below:
->innerJoin(
(new \Hoa\Database\Query\Select())
->from('Bar')
->_as('B')
)
Will produce: (it works)
SELECT *
FROM Foo
INNER JOIN (
SELECT *
FROM Bar AS B
);
And
->innerJoin(
(new \Hoa\Database\Query\Select())
->from('Bar')
)->_as('B')
Will produce: (What we have to do)
SELECT *
FROM Foo
INNER JOIN (
SELECT *
FROM Bar
) AS B;
We could also imagine a more complex request like:
$req = new \Hoa\Database\Query\Select();
$req->from('Foo')
->_as('F') // Basic table alias.
->innerJoin(
(new \Hoa\Database\Query\Select())
->from('Bar')
->_as('Z') // Table alias inside sub query.
->where('Z.Bar_ID = ?')
)
->_as('B') // Sub query alias.
->on('F.Foo_ID = B.Bar_ForeignKey');
Pong, sorry for the answer extremly late :s
So there is a bug, what did you think about :
$req = new \Hoa\Database\Query\Select();
$req->from('Foo', 'aaa')
->select('A as B', 'C', 'D')
->innerJoin(
(new \Hoa\Database\Query\Select())->from('Bar'),
'B'
)
->on('F.Foo_ID = B.Bar_ForeignKey');
Will generate
SELECT A as B, C, D FROM Foo AS aaa INNER JOIN (SELECT * FROM Bar) AS B ON F.Foo_ID = B.Bar_ForeignKey
Camael
Assuming aaa is equals to F.
Your solution is clear but it have the disadvantage of introduce a BC by changing SelectCore::from definition.
The BC is it a problem ?
Database are not released yet, so I don't consider it as real issue. Thoughts Hywan?
So to prevent the BC, we can hardcoded the alias in ->from('Foo AS F', 'Bar AS B');
I think the _as() instruction are deprecated, cause _as and form are not sync
@camael24 But what about: https://github.com/hoaproject/Database/issues/12#issuecomment-51784264. This is a good solution that does not introduce any BC isn't it?
:+1: for https://github.com/hoaproject/Database/issues/12#issuecomment-51784264
