Alias in FROM clause.

[Metalaka]

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 Foo table I can make an alias without the keyword AS like from('Foo F').
• The second alias is not well placed due to the ON clause (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.

[Metalaka]

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');

[Metalaka]

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 from element and remove it from $this->_from.
• If key is an alias, we write it directly before the JOIN clause.
• Finally we add it to $this->_from like an non aliased from.

Note: SelectCore::_as must not be called after this operation...

[Hywan]

Hello :-),

Maybe @camael24 could help you on this issue?

[thehawk970]

Hello
I will look this afternoon :)

[thehawk970]

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

[Metalaka]

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');

[Metalaka]

/ping

[thehawk970]

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

[thehawk970]

update my previous comment

[Metalaka]

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 ?

[thehawk970]

Database are not released yet, so I don't consider it as real issue. Thoughts Hywan?

[Hywan]

Well, the first comments brough a solution, why introducing something new?

[thehawk970]

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