Skip to content
This repository has been archived by the owner on Sep 20, 2021. It is now read-only.

Alias in FROM clause. #12

Open
Metalaka opened this issue Aug 10, 2014 · 19 comments
Open

Alias in FROM clause. #12

Metalaka opened this issue Aug 10, 2014 · 19 comments

Comments

@Metalaka
Copy link
Member

Metalaka commented Aug 10, 2014

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
Copy link
Member Author

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
Copy link
Member Author

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
Copy link
Member

Hywan commented Aug 12, 2014

Hello :-),

Maybe @camael24 could help you on this issue?

@thehawk970
Copy link

Hello
I will look this afternoon :)

@thehawk970
Copy link

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
Copy link
Member Author

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
Copy link
Member Author

/ping

@thehawk970
Copy link

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
Copy link

update my previous comment

@Metalaka
Copy link
Member Author

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
Copy link

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

@Hywan
Copy link
Member

Hywan commented Jan 7, 2015

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

@thehawk970
Copy link

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

@Hywan
Copy link
Member

Hywan commented Jan 7, 2015

@camael24 But what about: #12 (comment). This is a good solution that does not introduce any BC isn't it?

@Jir4
Copy link

Jir4 commented Feb 1, 2016

👍 for #12 (comment)

@thehawk970
Copy link

👍

@Jir4
Copy link

Jir4 commented Jun 14, 2016

ping ?

@Hywan
Copy link
Member

Hywan commented Jun 17, 2016

What is the status @Jir4?

@vonglasow
Copy link
Member

any news about this issue ? ping @Metalaka @Jir4 @Hywan @camael24

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Development

No branches or pull requests

5 participants