Join with zend_db_table_abstract classes in Zend Framework

Question

I have a module with two models that extend Zend_Db_Table_Abstract:

class Departments_Model_Subpages extends Zend_Db_Table_Abstract
{
    protected $_name = 'bktg_departments_subpages';
    protected $_primary = 'id';
}

class Departments_Model_Departments extends Zend_Db_Table_Abstract
{
    protected $_name = 'bktg_departments';
    protected $_primary = 'id';
}

Reading through the documentation, what I want to do is something like this (obviously, wrong):

$depTable = new Departments_Model_Departments();
$subTable = new Departments_Model_Subpages();

$depQuery = $depTable->select()->setIntegrityCheck(false)
->from($depTable, array('id', 'title'))
->join($subTable, array('COUNT(id) as `count`'))
->where('bktg_departments.id = bktg_departments_subpages.parent_id');

What am I missing? I just can't wrap my head around Zend joins, and I'm thinking I'm going to end up writing straight sql.

Thank you for having a look.

Edit

Here's what I'm working with now:

$depQuery = $depTable->select()->setIntegrityCheck(false)
->from($depTable, array('id', 'title'))
->joinLeft($subTable, 'bktg_departments.id = bktg_departments_subpages.parent_id',       array('COUNT(id) as count'))
->order('id DESC')
->group('id');

The sql that works for me:

SELECT bktg_departments.id, bktg_departments.title, count(bktg_departments_subpages.id) FROM bktg_departments left join bktg_departments_subpages on bktg_departments.id = bktg_departments_subpages.parent_id group by bktg_departments.id

And I am having issues reproducing that in Zend.

score 0 · Answer 1 · answered Jul 02 '12 at 19:15

0

There seems to be a problem with you join(). The second parameter should be the ON clause of the join and you won't need the where() anymore:

$depQuery = $depTable->select()->setIntegrityCheck(false)
->from($depTable, array('id', 'title'))
->join($subTable, 'bktg_departments.id = bktg_departments_subpages.parent_id', array('COUNT(id) as `count`'));

answered Jul 02 '12 at 19:15

Joel Lord

2,175
1
18
25

So I had my parameters mixed up? I really appreciate the help. Because I can pass the models in as objects, is there anyway to use those objects as the ON clause or does it always need to be typed out (for example, if for some reason I rename the table). – dbergunder Jul 03 '12 at 01:59
Though this isn't working as expected. I want to count the returned id's of the subTable that match parent_id to the subTable id. – dbergunder Jul 03 '12 at 02:13

score 0 · Accepted Answer · answered Jul 05 '12 at 13:57

As far as I can tell, passing an object into the table parameter doesn't give you the expected results, and always generates an error. Even a method such as $table->getName() generates errors so I was forced to type out the name manually. I don't know if I was expecting more out of the select object then it's capable of or this is a bug in 1.11. Either way, I hope this helps someone with their frustrations.

The following code works:

$depQuery = $depTable->select()->setIntegrityCheck(false)
->from($depTable, array('id', 'title'))
->joinLeft('bktg_departments_subpages', 'bktg_departments.id = bktg_departments_subpages.parent_id', array('count' => 'COUNT(bktg_departments_subpages.id)'))
->group('bktg_departments.id')
->order('bktg_departments.id DESC');

Join with zend_db_table_abstract classes in Zend Framework

2 Answers2