Welcome Guest, Not a member yet? Register   Sign In
Forced to run DB Query from View (Bad practice?)
#12

[eluser]Wuushu[/eluser]
[quote author="oddman" date="1211904891"]Bingo! It is a little bit of a performance hit, having to loop twice - but its worth it for code maintenance and scalability.

In order to do it as one query, you'd have all categories in one table, then have a field called category_id, so for example, the table structure would look like something like:

CREATE TABLE categories (
id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
category_id INT(11) UNSIGNED,
name VARCHAR(255)).etc.

Then when pulling the categories, in the model you might have a function that looks like this:

Code:
public function get_categories($parent_id = null)
{
    $tree = array();
    // useful for getting selected categories of a parent
    if (!is_null($parent_id)) {
        $query = $this->db->get_where('categories', array('category_id' => $parent_id));
    }
    else {
        $query = $this->db->get('categories');
    }
    
    // loop  through the results and create the tree
    foreach ($query->result_array() as $row) {
        $tree[$row['category_id']][] = $row; // appending a new category item to the parent array (this will be demonstrated later)
    }
    
    return $tree;
}

Now, we have a list of all categories as part of a tree, which we can call on at any point. So, once you've assigned the data you need to the view, all you need to do is this:

Code:
foreach ($cat_data[0] as $cat) { // i'm assuming here that top-level parents have a category_id of 0. This could very well simply be null or -1, or whatever best suits your application
    echo '<h2>'.$cat['name'].'</h2>';
    if (isset($cat_data[$cat['id']])) { // checking to see if we have children, if we do - loop through those and grab them!
        foreach ($cat_data[$cat['id']] as $child_cat) {
            echo '<h3>'.$child_cat['name'].'</h3>';
            echo '<p>'.$child_cat['body'].'</p>';
        }
    }
}

Now, you might be saying "wow, that's a lot of looping". Maybe so - but it's only one query. As a result, it will be a 100x faster than doing a subquery each time you find a category. One query is possible with the table layout you're proposed, however it's a bit trickier and means a lot more data is being retrieved.

Hope that helps Smile

PS: one more thing - that view code is best done as a recursive function. It can also support an unlimited number of sub categories and levels.[/quote]




What if $child_cat has a child? Does the view foreach-function take into account and print those out as well?


Messages In This Thread
Forced to run DB Query from View (Bad practice?) - by El Forum - 05-26-2008, 09:10 AM
Forced to run DB Query from View (Bad practice?) - by El Forum - 05-26-2008, 09:16 AM
Forced to run DB Query from View (Bad practice?) - by El Forum - 05-26-2008, 10:45 AM
Forced to run DB Query from View (Bad practice?) - by El Forum - 05-26-2008, 12:30 PM
Forced to run DB Query from View (Bad practice?) - by El Forum - 05-26-2008, 01:39 PM
Forced to run DB Query from View (Bad practice?) - by El Forum - 05-26-2008, 04:31 PM
Forced to run DB Query from View (Bad practice?) - by El Forum - 05-26-2008, 05:23 PM
Forced to run DB Query from View (Bad practice?) - by El Forum - 05-26-2008, 05:30 PM
Forced to run DB Query from View (Bad practice?) - by El Forum - 05-26-2008, 06:24 PM
Forced to run DB Query from View (Bad practice?) - by El Forum - 05-27-2008, 04:04 AM
Forced to run DB Query from View (Bad practice?) - by El Forum - 05-27-2008, 05:14 AM
Forced to run DB Query from View (Bad practice?) - by El Forum - 05-27-2008, 06:42 AM
Forced to run DB Query from View (Bad practice?) - by El Forum - 05-27-2008, 07:39 AM
Forced to run DB Query from View (Bad practice?) - by El Forum - 07-28-2008, 10:37 PM
Forced to run DB Query from View (Bad practice?) - by El Forum - 07-28-2008, 10:53 PM
Forced to run DB Query from View (Bad practice?) - by El Forum - 07-29-2008, 07:10 AM
Forced to run DB Query from View (Bad practice?) - by El Forum - 07-29-2008, 05:11 PM
Forced to run DB Query from View (Bad practice?) - by El Forum - 07-29-2008, 06:17 PM
Forced to run DB Query from View (Bad practice?) - by El Forum - 07-29-2008, 07:37 PM
Forced to run DB Query from View (Bad practice?) - by El Forum - 07-29-2008, 08:55 PM
Forced to run DB Query from View (Bad practice?) - by El Forum - 07-29-2008, 10:38 PM



Theme © iAndrew 2016 - Forum software by © MyBB