Fetching categories and items with Zend Framework

Question

Currently my code below works fine but it's a bit of overkill. In my controller I fetch the categories that have links and all the links in my database. In my view I loop through all the categories and then when I want to add the links under the category I loop through all the links in my database instead I should only loop through the links that are assigned to the current category but I don't know how to do this with Zend Framework. Can anybody send me into right direction. Thank's for your time.

Controller:

    public function indexAction()
    {
        $this->view->title = App_Translate::translate('links_title');
        $this->view->headTitle($this->view->title, 'PREPEND');

        $linkCat = Doctrine_Query::create()
                    ->distinct()
                    ->from('LinkCategory lc')
                    ->innerJoin('lc.Link l WITH lc.id = l.link_category_id')
                    ->orderBy('lc.id')
                    ->execute();

        $links = Doctrine_Query::create()
                    ->from('Link')
                    ->execute();

        $this->view->linkCat = $linkCat;
        $this->view->links = $links;
    }
}

View:

   <?php if(!empty($this->linkCat)): ?>
        <ul>
            <?php foreach($this->linkCat as $linkCat): ?>
            <li><h2><?php echo $this->escape($linkCat['title']); ?></h2>
                <?php if(!empty($this->links)): ?>
                <ul>
                    <?php foreach($this->links as $link): ?>
                        <?php if($link['link_category_id'] == $linkCat['id']): ?>
                            <li><a href="<?php echo $this->escape($link['url']); ?>"><?php echo $this->escape($link['title']); ?></a></li>
                        <?php endif; ?>
                    <?php endforeach; ?>
                </ul>
                <?php endif; ?>
            </li>
            <?php endforeach; ?>
        </ul>
    <?php else: ?>
        <p>No links added</p>
    <?php endif; ?>

Answer 1

Instead of doing those two queries, couldn't you go with only one ?

I see you have an Inner Join in your first query, so I suppose this could be possible ; I suppose you have some rule that says "One link is in one and only one category", which is a One-To-Many Relation ; maybe the Dealing with Relations part of the manual could help you.

What I would do might be :

• remove the distinct from the first query, to get all links + for each one, its category
  • this is the list you want, isn't it ?
  • also, order by category and link, so it's easier to display (links being already ordered by category)
• remove the second query

I suppose something like this would do :

$linkCat = Doctrine_Query::create()
    ->from('LinkCategory lc')
    ->innerJoin('lc.Link l WITH lc.id = l.link_category_id')
    ->orderBy('lc.name, l.name')
    ->execute();

(not tested, though : might need a bit more tunning)

Once you have that data, pass it to the views, where you'll do your loop -- remember lonks are already sorted by category :

• display the name of the first category ; store it in a variable
• loop on the links
  • if the category of the current link is not the same as the one of the previous link (store in the variable), then it means it's the end of a category, and the beginning of a new one
  • continue
• when you reach the end of the links, it's also the end of the last category

This should work, I'd say (of course, you still have to code -- but the idea is here)

As a sidenote : you are using Doctrine classes, writting DQL, and all that in your Controller -- This is quite not the place : all this should go in your Model Classes, not in a Controller.

Have fun !

Answer 2

Your question is not really related to Zend Framework. You are fetching data using Doctrine, not ZF.

In your case, I think you should be able to loop over the links in the specific category using foreach($linkCat->Link as $link), seeing how you use innerJoin to load the relation.