MVC Object Oriented Techniques - How to minimise queries and maintain flexibility?

Question

Hello there

I am using an objected oriented MVC framework in PHP (Kohana) and have kind of mashed together a few techniques to get stuff done. Problem is I am not sure how to keep things clean without calling lots and lots of queries per page.

To illustrate my example, I'll imagine I am designing a stack overflow like site:

I have model class for a question (question_model) as well as one for question finder (question_finder_model).

question_model mainly just contains variables to store the question data, arrays of answer objects and some factory methods. Something like:

class question_model {
    public $question_id,$question_title,$question_body,$answers = array();    
}

The question finder containers an array of question_model objects as well as an array of question ids. The array of ids is populated by find methods in the class and used by other methods. Something like:

class question_finder_model {
    private $question_ids = array();
    public $questions = array() ;   //

    function public find_questions () {
        // executes some SQL to find a list of projects
        // Create a new question_model object for each question and store in $questions
        // for each of these questions store the id in $questions_ids
    }
    function public get_answer_info () {
       // using all the question ids stored in $question_ids:
       // find information about the answers
     }
}

So I use this method for all my models, for example my user model will contain an array of questions objects.

The problem is that it is getting quite hard to handle, for example my question contains many answers and each answer can contain many comments and so on. How can I populate all these objects without having several queries. I mean the easy way would be to just iterate through my array of question objects and call a function stored within the question class which gets the answers info for that object. But then I would be calling 10's or 100's of queries per page.

I apologise if this is all abit hazy as the problem is quite difficult to articulate. Any help is appreciate because perhpas my entire pattern is flawed.

Answer 1

It doesn't really have that much to do with PHP than SQL.

To get a list of answers for a question, including comments, you could fetch all answers by question id. Then, collect all answer ids into an array, and fetch all comments which have an answer id in that array.

That's three queries. You'd just need to work out which comment belongs to which answer in code - which can be made faster if you sort the comments by answer id, and so on.

You can also cache things with memcached, to files or other, which should speed things up quite nicely.

Of course building the objects from such structures can be trickier than just per-query, but that's something you'll just need to do.

Answer 2

Jani Hartikainen has summed it up pretty well, I'd like to mention something else.

When you have learned OOP, everything starts to look like an object.

While it is true that everything can be declared as a class and seen as an object, you don't have to. In your example, it is probably easier to be seen as a mass data manipulation rather than dealing with individual question/answer/comment objects.

Once you look at it from that angle, you will probably come up with solutions like Jani's yourself.

Answer 3

What you are expressing here is one of the most common preoblems with naive ORMappers. Generating several queries.

You can read precise description of your problem here: ORMs Done right: (DBA Gripe #3: hidden expensive actions)

Basically the problem is that you need to several queries for something like this:

foreach ($questions in $site) {
 foreach ($question as $questions) {
    foreach ($answer in $question){
       foreach ($comment in $answer) {
         echo "$site->title, $question->title, $answer->title, $comment->title";
       }
    }
 }

}

It gets really slow, really fast.

What you need to do is to get all the info in one query with the proper Joins. And then populate your objects.

Look for the suggested implementation in the some article: Class::ReluctantORM — mandatory prefetching

Finally don't get hang-up trying to get every case right. The Relational model and Object model “impedance mismatch” isn't a solved problem. So don't try to solve it perfectly yourself. After all Object-Relational Mapping is the Vietnam of Computer Science