Understanding SQL Aliases

Question

I understand the concept of SQL aliases, but in the following doctrine query language example, the alias is at the front of the table names, as well as following behind them. Could someone help explain whats happening in this query? I want to try to get a better understanding of whats happening before i attempt to alter it.

public function getByDomain($domain)
  {
    return $this->createQuery('d')->select('d.*, c.*, p.*, cl.*')
            ->innerJoin('d.Table1 c')
            ->innerJoin('c.Table2 p')->innerJoin('c.Table3 cl')
            ->where('d.name=?',$domain)->fetchOne();
  }

Answer 1

What is happening is that you are calling $this->createQuery() from inside a method that resides in a class that extends Doctrine_Table. The createQuery() method takes one parameter $alias, and returns a Doctrine_Query object with the 'from' automatically added in (that is why there is no ->from() call in the statement).

The full code probably looks like this:

class DomainTable extends Doctrine_Table
{
  public function getByDomain($domain)
  {
    return $this->createQuery('d')->select('d.*, c.*, p.*, cl.*')
            ->innerJoin('d.Table1 c')
            ->innerJoin('c.Table2 p')->innerJoin('c.Table3 cl')
            ->where('d.name=?',$domain)->fetchOne();
  }
}

In Doctrine, the alias can be used in front of the other model names you want to perform a join on. Doctrine will automatically determine the foreign keys if you have the proper relations defined in your schema files.

So this code is selecting all columns from Domain, Table1, Table2 and Table3 where the Domain.name column matches $domain, and only returns 1 result (LIMIT 1).