Forced to run DB Query from View (Bad practice?)

[eluser]ROFLwtfBBQ?[/eluser]
Ok a design pattern question here. I'm using active record to fetch all the data from a table: 'categories'. Under each category, i want to list sub-categories (all of them in the same view). I figured the best way to do this was to add another Query inside the View in the 'categories' for each loop, because the sub-categories listing has to know the specific category_id in order to know what category it should be listed under. I'd rather do all of the queries in the model or controller, but i can't send data (for example category_id) back to the controller? So is there a better way to solve this than the way im currently doing it. Any ideas? My current solution kind of screams bad practice, since i want to seperate my php code from the view as much as possible.

[eluser]Pascal Kriete[/eluser]
It does not really scream bad practice, as long as the view only reads and does not write to the db. However, you could do a sql join to get the category and sub-categories with one query.

[eluser]Developer13[/eluser]
Personally I'd just use a recursive query in the model to get each category and each related subcategory.

[eluser]bradym[/eluser]
1 - Get the id of the current category
2 - Get the children of the current category

I always have a parent field in category tables, so it would be a simple query:

Code:

$this->db->query("SELECT name FROM category WHERE parent = $category_id");

You could easily combine both steps into one function in your model.

[eluser]Sean Murphy[/eluser]
This really should be done with ONE query, like inparo said.

[eluser]oddman[/eluser]
Any sort of application logic should NOT be in ANY view. Retrieving data to be stored in a variable is application logic, retrieved from the model. Inparo and Sean Murphy are right - this should all be done with one query, and the way you could do that would be to grab all categories for a particular parent, then create a tree with the various links, so that each parent_id has it's own array. Then you'd just loop through the entire tree, and for each element within a parent, see if it also has children (by doing something like if ($categories[$parent_id]).etc.

[eluser]Rick Jolly[/eluser]
[quote author="oddman" date="1211859092"]Any sort of application logic should NOT be in ANY view. Retrieving data to be stored in a variable is application logic, retrieved from the model.[/quote]
One definition of MVC supports the views retrieving data from models. Actually, it's a good way of creating a more modular application and reducing redundant code.

[eluser]oddman[/eluser]
[quote author="Rick Jolly" date="1211862183"][quote author="oddman" date="1211859092"]Any sort of application logic should NOT be in ANY view. Retrieving data to be stored in a variable is application logic, retrieved from the model.[/quote]
One definition of MVC supports the views retrieving data from models. Actually, it's a good way of creating a more modular application and reducing redundant code.[/quote]

For sake of argument - isn't this in a way defeating the purpose of MVC? In effect, you're now using application logic in a view?

[eluser]gtech[/eluser]
quick lets have another 'accessing a model in a view argument'

[url="http://ellislab.com/forums/viewthread/77719/"]http://ellislab.com/forums/viewthread/77719/[/url]

[eluser]ROFLwtfBBQ?[/eluser]
I'm not sure how I would solve this with "one query" as people are suggesting. However, I moved the query into the model now, but I'm not entirly convinced about the benefits of it. It seems like alot of extra code, and a waste of performance having to first sort the results into the data array in the model, then iterate through the array in the view. But i guess that's an extra neccesary step to keep the business and presentation logic apart.

In the model:

Code:

$query = $this->db->get('categories');
foreach ($query->result() as $category)
{
     $anotherQuery = $this->db->get_where('subcategory', array('category_id' => $category->id));
     foreach ($anotherQuery->result() as $subcategory)
     {
         $data['categories'][$category->title][$subcategory->id]['title'] = $subcategory->title;
         $data['categories'][$category->title][$subcategory->id]['body'] = $subcategory->body;
     }
}

And in the view

Code:

<?php foreach ($categories as $category => $sub):?>
    <h2>&lt;?php echo $category;?&gt;</h2>
    &lt;?php foreach ($sub as $subcategories => $subcategory):?&gt;
        <h3>&lt;?php echo $subcategory['title']?&gt;</h3>
        <p>&lt;?php echo $subcategory['body']?&gt;</p
    &lt;?php endforeach;?&gt;    
&lt;?php endforeach;?&gt;