Doctrine query for forum index page

I want to create a small forum on symfony 2.3. The tables are Categories,Subcategories,Topics and Posts, the entities are Category,Subcategory, Topic and Post. The tables are all linked together via one to many relationships in the obvious hierarchy.

For the Index page I want to get an array with all the data I need so i can use forstatements in the view. The query I do is

$this->getEntityManager()
->createQueryBuilder()
->select('c', 'sc', 't', 'u', 'p')
->from('MyForumBundle:Category', 'c')
->leftJoin('m.subcategories', 's')
->leftJoin('s.topics', 't')
->leftJoin('s.user', 'u')
->leftJoin('t.posts', 'p')
->OrderBy('m.id', 'ASC')
->addOrderBy('p.id', 'DESC')
->getQuery()
->getArrayResult();

I guess this works fine if my forum doesn't have a lot of data, but if it gets more full, the execute time is going to be really long. And the second problem is that i don't know how to query for the number of posts in a topic and for the last post in a subcategory.

Is my way of going about this bad practice? How can I do the query with last post and number of posts without destroying my array? Am I doing something wrong?

Solution

The way you've been doing it works alright for smaller domains, but I think you're finding that it doesn't scale. The problem is that you're passing a large amount of data in the Response back to the user. And as your domain grows, the problem will only get worse.

If you want a true scalable application, you'll need to limit the amount of data that you access at one time. Your controller generally doesn't need access to an entire set of entities. Sure, it might need summarized data, like totals and counts... And it might need a smaller subset of data, like the most recent 10 posts from a particular category. But you're not going to need to read or update an entire set of entities.

Here are a few things that might help:

Counting

If you're just looking to get a count of a result set, then instead of:

count(
    $queryBuilder
        ->select('entity')
        ->getQuery()
        ->getResult()
);

Try:

$queryBuilder
    ->select('count(entity.id)')
    ->getQuery
    ->getSingleScalarResult();

(Note that, while both of these give the same answer, one returns the entire data set, and one returns a single number)

Subsets

Instead of returning an entire result set, you can paginate it like this:

$queryBuilder
    ->setFirstResult($pageNumber * $numPerPage)
    ->setMaxResults($numPerPage)

Last Results

Along those lines, you can get the most recent results by ordering and limiting:

$queryBuilder
    ->orderBy("entity.timestamp", "DESC")
    ->setMaxResults(10)