Select ignores where clause using Zend_Db_Select

Question

$table = new Zend_Db_Table(array('name'=>'rules'));

    $select = $table->select();
    $select->setTable($table); 
    $select->setIntegrityCheck(false);

    $select = $select
    ->from(array('ru'=>'rules'),array('ru.*'))
    ->join(array('ro'=>'roles'),'ro.id=ru.role_id',array('role_id'=>'ro.id'))
    ->join(array('g'=>'groups'),'ro.group_id=g.id',array('group_id'=>'g.id'))
    ->join(array('ug'=>'user_groups'),"ug.group_id=g.id",array('user_group_id'=>'ug.id'))
    ->where("ug.user_id={$userId}")
    ->where("ru.resource='{$resource}'")
    ->where("ru.privilege='{$privilege}'");
    echo "select: ".$select->__toString();

$row = $table->fetchAll();

I have the preceding code,but when I try fetchAll() it returns all rows in the table, ignoring the where clause, when I use fetchRow() it returns the first row it finds, ignoring the where clause, I printed the SQL statement and run it separately and it executes correctly any clue ?

score 2 · Answer 1 · answered Apr 28 '11 at 13:19

This is how you would create a db select object correctly

$db = Zend_Db::factory( ...options... );
$select = new Zend_Db_Select($db);

Or you use the database adapter's select() method

$db = Zend_Db::factory( ...options... );
$select = $db->select();

And you can add clauses

  // Build this query:
  //   SELECT *
  //   FROM "table1"
  //   JOIN "table2"
  //   ON "table1".column1 = "table2".column1
  //   WHERE column2 = 'foo'

  $select = $db->select()
               ->from('table1')
               ->joinUsing('table2', 'column1')
               ->where('column2 = ?', 'foo');

Have a look at the Zend_Db Reference Guide for more information

score 0 · Answer 2 · answered Apr 29 '11 at 13:01

This should work. Just tested it.

    $table = new Zend_Db_Table('rules');
    $select = $table->getAdapter()->select();

    $select->from(array('ru' => 'rules'), array('ru.*'))
           ->join(array('ro'=>'roles'), 'ro.id = ru.role_id', array('role_id'=>'ro.id'))
           ->join(array('g'=>'groups'), 'ro.group_id = g.id', array('group_id'=>'g.id'))
           ->join(array('ug'=>'user_groups'),"ug.group_id=g.id",array('user_group_id'=>'ug.id'))
           ->where('ug.user_id = ?', $userId)
           ->where('ru.resource = ?', $resource)
           ->where("ru.privilege = ?", $privilege);

    echo (string)$select;

The sql produced from my code is the same as I wanted it, and it runs on phpMyAdmin perfectly, but the problem is that when it executes here in the code, it doesn't care about where clauses, it just select everything in the table — Omar, May 02 '11 at 08:40

regilero · Accepted Answer · 2011-05-02T10:28:45.657

@ArtWorkAD is right in a certain way. But in your case you're not just using a Zend_Db_Select. You tried to extend a Zend_Db_Select obtained from a Zend_Db_Table (well, you should try to handle a Singleton pattern with Zend_Db_Table but this is another problem). Your current problem (if we except the fact you are certainly reading documentation too fast) is that this line was correct:

 $select->setIntegrityCheck(false);

It make your 'select-from-a-zend-db-table' not anymore restricted to the Active Record Mode, and available for extra joins.

But just after that you make a:

$select = new Zend_Db_Select($table);

This is the complete creation of a new object, that you put into your variable. Nothing is kept from previous variable value. You could add a $select=null; just before it would be the same. So this is just canceling the 3 previous lines.

In quite the same confusion mode this line:

$select->setTable($table);

Is not necessary as you're already taking the select from a Zend_Db_Table so the table is already there.

EDIT And your last and bigger error is:

$table->fetchAll()

You do not use your built $select but your $table, so effectively everything done in your $select is ignored :-) . Fecthing from the $select shoudl give you better results

The $select = new Zend_Db_Select($table); was added when I was just trying the select object. I had removed it. — Omar, May 02 '11 at 08:47
your are right also about the setTable method. I had also removed the it, there is nothing changed but it's still not fixed. — Omar, May 02 '11 at 08:57
I've edited the answer with your last error, using $table and not $select — regilero, May 02 '11 at 10:29
Thanks alot, its solved now. I passed the $select to the fetchAll() method — Omar, May 09 '11 at 09:49

Select ignores where clause using Zend_Db_Select

3 Answers3