Optimised solution to store order of rows in a database

I have a data model in Doctrine/symfony. I have a 'Course' which has many 'Lesson's. For each lesson I need to calculate the order (by date) that the lesson appears. For example, the Course 'Java for beginners' might have 10 lessons during October, I need to retrieve the order of these lessons so that the first one is called 'Java for beginners 1' etc.

Currently I have a getTitle() method in my Lesson model that queries the database to establish the number. This works fine. However, when there are 400 lessons on screen (this is a typical use case) it results in 400+ queries.

I have read about Doctrine behaviours and as I understand it, I could add a behaviour for each time a lesson is added, edited or deleted I can recalculate all the sequence numbers - storing them in the database. However, I cannot get this to work efficiently.

Is there a more efficient method than the ones I have mentioned?

Cheers,

Matt

Doctrine_Query::create()->
        from('Lesson l')->
        leftJoin('l.Course c')->
        leftJoin('l.Teacher t')->
        leftJoin('l.Students sl')->
        andWhere('l.date BETWEEN ? AND ?', array(date('Y-m-d', $start_date), date('Y-m-d', $end_date)))->
        orderBy('l.date, l.time');

The above code returns all my lesson information (apart from the lesson number).

Doctrine_Query::create()
                ->select('COUNT(l.id) as count')
                ->from('Lesson l')
                ->lef开发者_StackOverflow中文版tJoin('l.Course c')
                ->where('c.id = ?', $this->course_id)
                ->andWhere('TIMESTAMP(l.date, l.time) < ?', $this->date . ' ' . $this->time)
                ->orderBy('l.date, l.time');
    $lessons = $q->fetchOne();
    return $lessons->count + 1;

And this code is in the Lesson model as a function. It calculates the sequence number of a given lesson and returns it as an integer. This is the method that gets called 400+ times. I have tried adding this as a subquery to the first query, but with no success.

Behaviour Code

public function postInsert(Doctrine_Event $event) {
    $invoker = $event->getInvoker();
    $table = Doctrine::getTable('Lesson');

    // Course query
    $cq = Doctrine::getTable('Lesson')->createQuery();
    $cq->select('COUNT(l.id) as count')
            ->from('Lesson l')
            ->leftJoin('l.Course c')
            ->where('c.id = ?', $invoker->Course->id)
            ->andWhere('TIMESTAMP(l.date, l.time) < ?', $invoker->date . ' ' . $invoker->time)
            ->orderBy('l.date, l.time');
    $lessons = $cq->fetchOne();

    $q = $table->createQuery();

    $q->update()->
            set('sequence_number', $lessons->count + 1)->
            where('id = ?', $invoker->id)->
            execute();
}

The obvious problem here is that it only updates the invoked lesson. If one lesson updates its sequence, all lessons should update their sequence numbers. However, the above code causes a memory problem when I try to populate the database from my fixtures (about 5000 lessons, it fails at ~1500).

---

Edit::

Youre running out of memory because youre eating tons of memory with all those objects. You should try batching in groups of 1000 (since youre failing at ~1500).

Another thing you could do is not put the listener/behavior on the model directly but instead make a postTransactionCommit listener. In this listener you can check to see if any records in the lesson table were part of the commit and then update them all with the proper sequence.

---

Edit:

So as per our comment... lets go the other direction:

Doctrine_Core::getTable('Course')->createQuery('c')
  ->leftJoin('l.Lesson')
  ->orderBy('l.date_column')
  ->execute();

---

Well normally to avoid the extra queries you would perform a join wihtin the query so that you dont have the extra expense. For example:

Doctrine_Core::getTable('Lesson')->createQuery('l')
  ->leftJoin('l.Course')
  ->orderBy('l.date_column')
  ->execute();

This way the Course and Lesson info will come down in one query and the Course will be hydrated along with the Lesson.

This of course does nothing for your sequence number. That is a different issue really, and i would think that the best thing to do would be to use a behavior for that as you yourself suggested. If you want to post your behavior code and a description of the problem(s) im sure we could help you with it