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

[eluser]oddman[/eluser]
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.
#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?
#13

[eluser]Pascal Kriete[/eluser]
The current logic doesn't, but I believe that's what the PS was for.

If all you have are categories and entries with no further nesting, then all you have to do is join the two tables. Truly one query.
Code:
$this->db->join('entries', 'entries.FK_category_id = 'categories.category_id', 'left');
#14

[eluser]dbelanger[/eluser]
[quote author="Wuushu" date="1211910123"][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?[/quote]

I'm also interested in seeing how you can go deeper with this code such as the case of $child_cat having a child. Any ideas?
#15

[eluser]oddman[/eluser]
That's what a recursive function would be used for - and it would have limitless depth.
#16

[eluser]drewbee[/eluser]
Argh.. I wouldn't recomend a recursive function that makes database queries. Think about it. For every child you are making a new call to the database. Iwould hate to have to manage that.

Take a look at this:
http://www.sitepoint.com/article/hierarc...a-database

It is the 'preorder tree transversal sitepoint', and you can get all the information you want in 1 query. (OR two queries if you need to identify the current category location); needless to say, this method is the best for performance.
#17

[eluser]oddman[/eluser]
The recursive function wouldn't be making multiple DB queries - it would simply be traversing the array of items, and generating the output. Did you read the full post beforehand? Tongue
#18

[eluser]drewbee[/eluser]
Yes, I did. Thanks for asking Smile
"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."

Recursive or not, a new query is being made for each 'child'.
#19

[eluser]oddman[/eluser]
ah fair enough - thought you were referring to the solution I suggested Smile
#20

[eluser]drewbee[/eluser]
Nope -- Now that I think about it I bet that category method would make a great library for CI... any takers? Maybe it will be my next little project. hehe.




Theme © iAndrew 2016 - Forum software by © MyBB