How to handle database access in Zend Framework?

Question

Hi,

To begin: i'm not an expert in Zend Framework and doing something terrible wrong. I'm sure of that. I think there's something wrong with my design patterns.

As an example: I'm building access management with Zend_ACL (Access Control List)

There are three tables in the database: roles resources permissions

the permissions table handles the role-resources relation.

I made a model for each table, it extends Zend_Db_Table_Abstract. So far so good.

Now in the ACL I load the resources, role and permissions on a page request and add it to the ACL. Now the part I'm doing something wrong: The way I do it is call methods from the tablemodels that give me the required data. But when I look at my profiler it takes 117 select queries and takes 0.7 seconds just to load the ACL. No queries for the underlying system yet. This can't be good and I'm sure there is a better way. I just can't find anything about this on google or anywhere.

Is there someone who can tell me if I'm doing something wrong and if I am, what I should to to speed it up? Should I load everything in one query to models and let them handle it? How do I do that, are there any examples?

Thanks in advance!

Answer 1

It's not clear exactly what you're doing, but you shouldn't be grabbing generating so many queries.

Some things to think about:

Do you need to load the full ACL on every request? Perhaps you could structure your data-access layer in such a way that it checks for relevant permissions when a user attempts to perform some action.

If you do need the full ACL, you ought to be able to construct a single query to grab all the relevant data. Perhaps eschewing the Zend_Table stuff here and just executing a raw custom query? Not exactly a best-practice, but if you're loading everything, it's just one query.

More concrete code in your question would definitely produce more concrete answers.