Zendcasts Forum

Hello everyone!

I have been trying to postpone this but the day has come:  I can't figure out a way in which I could do some complex querying with Zend_db, that I was able to do with simple SQL writing.

The issue rises when I need to select from more then one table in a relational db, like, for example, to pull all the records that belong to a category in a nested set database model.

Here's an example:

we have 2 tables: projects ( id, name, category_id ) and categories (id, name, left, right).
For those who don't remember about nested set model , please read here:
http://dev.mysql.com/tech-resources/art … -data.html

Say that we have the following category structure: 

ALL
--Web Development
   -- PHP
   -- ASP
--Graphic Design
  -- Image Creation
  -- HTML slicing

and the task is to pull all the projects that belong to category "Graphic Design", which will mean that we need also those pertaining to "Image Creation" and "HTML slicing" as well..

Here's how I would've done it in a classic PHP/MySQL development script:

SELECT p.id
FROM
   projects p, categories cNode, categories cParent
WHERE
   p.category_id=cNode.id
   AND cNode.left BETWEEN cParent.left AND cParent.right
   AND cParent.name='Graphic Design'

I sure hope it's possible to do this using Zend_db and waiting for you guys to give me a clue.

Thanks,
Slavic