Zend_Db_Table, JOIN and mysql expressions in one query?

Question

Im trying to build a complex (well...) query with Zend_Db_Table where I will both need to join the original table with an extra table and get some extra info from the original table with zend_db_expr.

However, things go wrong from the start. What I to is this:

$select = $this->getDbTable()->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
                             ->setIntegrityCheck(false);

$select->from( $this->getDbTable() , array(
    '*' ,
    new Zend_Db_Expr('`end` IS NULL as isnull') ,
    new Zend_Db_Expr('`sold` IN (1,2,3) as issold') ,
) );                                     

Zend_Debug::dump( $select->__toString() );exit;

What results in this:

SELECT `items`.*, `items_2`.*, `end` IS NULL as isnull, `sold` IN (1,2,3) as issold FROM `items`
 INNER JOIN `items` AS `items_2`

What I need it to be though, at this point before I do the join with the other table, is

SELECT `items`.*, `end` IS NULL as isnull, `sold` IN (1,2,3) as issold FROM `items`

I don't need an inner join with itself, I just need to add those two Zend_Db_Expr to the things that should be selected, after which I'd continue building the query with the join and where's etc. like

$select->joinLeft( ... )
->where(...)

Any ideas? Cheers.

Answer 1

You should not redo a ->from() call, which means yu add a new table in the query. Instead you should just use ->where()->columns() calls containing you Zend_Db_expr.

edit: sorry for the mistake.