select columns invalidQueryExeption

Question

I have two tables : product with id, name, price and brand fields and brand with id, name, url fields. I would like to select both of fields from this two tables. I am unable to define columns and defines aliases.

ProductTable

...
public function fetchAll()
{

    $select = new Select;
    $select->from($this->table);
    $select->join('brand', 'product.brand = brand.id', array('name', 'url'));
    $select->columns(array('product.id', 'name', 'price'));

    $statement = $this->adapter->createStatement();
    $select->prepareStatement($this->adapter, $statement);

    $resultSet = new ResultSet();
    $resultSet->initialize($statement->execute());
    return $resultSet;
   }

Message : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'product.product.id' in 'field list'

What is the good practice for the request : select p.id as id, p.name as name, p.price, b.id as brandid, b.name as brandname...

After a few tries I found this solution :

public function fetchAll()
{

    $select = new Select;
    $select->from($this->table);
    $select->join(array('b' => 'brand'), 'product.brand = b.id', array('brandid' => 'id', 'brandname' => 'name', 'url'));
    $select->columns(array('id', 'name', 'price'));

    $statement = $this->adapter->createStatement();
    $select->prepareStatement($this->adapter, $statement);

    $resultSet = new ResultSet();
    $resultSet->initialize($statement->execute());
    return $resultSet;
}

I found how to put a alias on join tables but what about the base table product ?

Fouad Fodail · Answer 1 · 2013-09-23T13:46:01.800

You can specify an alias for your base table as follow:

$select->from( array('p' => $this->table));

If you would like to add columns at the same time you can do it like that :

$select->from( array('p' => $this->table), array('id', 'name', 'price'));
// $select->columns(array('id', 'name', 'price')); // no need for that now

To add aliases for some columns you can use :

$select->from( array('p' => $this->table), array('id' => 'pid', 'name' => 'pname', 'price'));

Doc : http://framework.zend.com/manual/1.12/en/zend.db.select.html#zend.db.select.building.from

Edit :

Your code would be :

public function fetchAll()
{
    $select = new select();
    $select->from(array('p' => $this->table), array('id','name', 'price', 'brand'));
    $select->join(array('b' => 'brand'), 'p.brand = b.id', array('name', 'url'));

    // var_dump($select->__toString()); // Use this to print the SQL query corresponding to your select object

    $statement = $this->adapter->createStatement();
    $select->prepareStatement($this->adapter, $statement);

    $resultSet = new ResultSet();
    $resultSet->initialize($statement->execute());
    return $resultSet;
}

The first solution work very well. Result is : SELECT "p".*, "b"."id" AS "brandid", "b"."name" AS "brandname", "b"."url" AS "url" FROM "product" AS "p" INNER JOIN "brand" AS "b" ON "p"."brand" = "b"."id". But with the second solution like that $select->from(array('p' => $this->table), array('id', 'name')); the result is the same although I did not add the field 'price'. — bsg78, Sep 23 '13 at 09:49

select columns invalidQueryExeption

1 Answers1