First step for a menu of subcategories and sub-subcategories - the query

[oikram]

Hello all, this is a middle question, could be made on a php part, or on the mysql part... I don't know... still I presume it fits better here:

I'm having the mysterious query:

SELECT DISTINCT t2.nome_cat AS subcategoria, t3.nome_cat AS subsubcategoria
FROM categoria AS t1
LEFT JOIN categoria AS t2 ON t2.parent_id_cat = t1.id_cat
LEFT JOIN categoria AS t3 ON t3.parent_id_cat = t2.id_cat
WHERE t1.id_cat = 1

I'm getting this as return:
subcategoria subsubcategoria
Fitofármacos Herbicidas
Fitofármacos Insecticidas
Fitofármacos Fungicidas
Adubos NULL
Sementes NULL

On the menu, I want that the first level be with subcategories:
like

Fitofármacos
Adubos
Sementes


But as you can see, I'm having 3 times Fitofarmacos from the database
Instead of displaying 3 times, I must displayed only one time.

1)
Should this be handled on the php side of things, or on the query?
As you may notice I've added a distinct clause hoping to solve this. No success.



Thanks in advance,
Newbie,
Márcio

[r937]

SELECT t2.nome_cat AS subcategoria
  FROM categoria AS t1
LEFT OUTER
  JOIN categoria AS t2 
    ON t2.parent_id_cat = t1.id_cat
 WHERE t1.id_cat = 1

[oikram]

Hi,



I have done as mentioned:

SELECT t2.nome_cat AS subcategoria
FROM categoria AS t1
LEFT OUTER
JOIN categoria AS t2 ON t2.parent_id_cat = t1.id_cat
WHERE t1.id_cat =1

But with this, I only get subcategories. But not the sub-subcategories.

The intended result shoud be, instead of:

subcategories - sub-subcategories
subcategory1 - sub-subcategory1
subcategory1 - sub-subcategory2
subcategory1 - sub-subcategory3
subcategory2
subcategory3


Have:

subcategories - sub-subcategories
subcategory1 - sub-subcategory1
subcategory2 - sub-subcategory2
subcategory3 - sub-subcategory3

The intended result was to have no repeating subcategories or sub-subcategories returned.

If this is possible?


Regards,
Márcio

[r937]

in your first post, it certainly looked like all you wanted was the subcategories

now, it seems that you want the subsubcatories as well, bu only one of them

my advice is that if you show only one of them, it is misleading to the user

SELECT t2.nome_cat AS subcategoria
     , MIN(t3.nome_cat) AS subsubcategoria
  FROM categoria AS t1
LEFT OUTER
  JOIN categoria AS t2 
    ON t2.parent_id_cat = t1.id_cat
LEFT OUTER
  JOIN categoria AS t3 
    ON t3.parent_id_cat = t2.id_cat
 WHERE t1.id_cat = 1
GROUP
    BY t2.nome_cat

[oddz]

Select all rows then pass the result as $arrRows to this function and tell me how it works out. You should end up with a structure of nested arrays that replicates the tree.

$arrTree = parse_into_tree(null,$arrRows,'id_cat','parent_id_cat','children');
echo '<pre>',print_r($arrTree),'</pre>';

function parse_into_tree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {
    
    $arrChildren = array();

    for($i=0;$i<count($arrRows);$i++) {
        if($intParentId === $arrRows[$i][$strParentsIdField]) {
            $arrChildren = array_merge($arrChildren,array_splice($arrRows,$i--,1));
        }
    }
    
    $intChildren = count($arrChildren);
    if($intChildren != 0) {
        for($i=0;$i<$intChildren;$i++) {
            $arrChildren[$i][$strNameResolution] = parse_into_tree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);
        }        
    }
    
    return $arrChildren;

} 

Ex.

Array
(
    [0] => Array
        (
            [cats_id] => 1
            [parents_id] => 
            [name] => Tv & Video
            [sort_order] => 1
            [cats] => Array
                (
                    [0] => Array
                        (
                            [cats_id] => 6
                            [parents_id] => 1
                            [name] => Televisions
                            [sort_order] => 0
                            [cats] => Array
                                (
                                )

                        )

                    [1] => Array
                        (
                            [cats_id] => 7
                            [parents_id] => 1
                            [name] => DVD Players
                            [sort_order] => 1
                            [cats] => Array
                                (
                                )

                        )

                )

        )

    [1] => Array
        (
            [cats_id] => 2
            [parents_id] => 
            [name] => Audio
            [sort_order] => 2
            [cats] => Array
                (
                    [0] => Array
                        (
                            [cats_id] => 8
                            [parents_id] => 2
                            [name] => Car Audio
                            [sort_order] => 0
                            [cats] => Array
                                (
                                )

                        )

                    [1] => Array
                        (
                            [cats_id] => 9
                            [parents_id] => 2
                            [name] => Music
                            [sort_order] => 0
                            [cats] => Array
                                (
                                    [0] => Array
                                        (
                                            [cats_id] => 10
                                            [parents_id] => 9
                                            [name] => Compact Discs
                                            [sort_order] => 0
                                            [cats] => Array
                                                (
                                                )

                                        )

                                )

                        )

                )

        )

    [2] => Array
        (
            [cats_id] => 3
            [parents_id] => 
            [name] => Toys & Games
            [sort_order] => 3
            [cats] => Array
                (
                )

        )

    [3] => Array
        (
            [cats_id] => 4
            [parents_id] => 
            [name] => Cameras & Camcorders
            [sort_order] => 4
            [cats] => Array
                (
                )

        )

    [4] => Array
        (
            [cats_id] => 5
            [parents_id] => 
            [name] => Cell Phone & Office
            [sort_order] => 5
            [cats] => Array
                (
                )

        )

)

That is the first step. Once the tree is built its just a matter of recursively parsing it into a menu.

** You will also need to replace null with 0 if a parent of 0 represents the root level.

[oddz]

Here is an example of my test code prior to the previous posting. Ignore ActiveRecord:: getConnection() as it just returns the database connection object. In this case its PDO but it could be anything.

$arrRows = array();
$strSQL = 'SELECT c.cats_id,c.parents_id,c.name,c.sort_order FROM CATS c';
$objPDO = ActiveRecord::getConnection();

if($objPDOSTMT = $objPDO->prepare($strSQL)) {
    if($objPDOSTMT->execute()) {
        while($arrRow = $objPDOSTMT->fetch(PDO::FETCH_ASSOC)) {
            $arrRows[] = $arrRow;
        }    
    }
} 

[oddz]

Here is the full thing:

$arrRows = array();
$strSQL = 'SELECT c.cats_id,c.parents_id,c.name,c.sort_order FROM CATS c';
$objPDO = ActiveRecord::getConnection();

if($objPDOSTMT = $objPDO->prepare($strSQL)) {
    if($objPDOSTMT->execute()) {
        while($arrRow = $objPDOSTMT->fetch(PDO::FETCH_ASSOC)) {
            $arrRows[] = $arrRow;
        }    
    }
}

parse_into_menu(parse_into_tree(null,$arrRows,'cats_id','parents_id','cats'),'cats','name');

function parse_into_tree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {
    
    $arrChildren = array();

    for($i=0;$i<count($arrRows);$i++) {
        if($intParentId === $arrRows[$i][$strParentsIdField]) {
            $arrChildren = array_merge($arrChildren,array_splice($arrRows,$i--,1));
        }
    }
    
    $intChildren = count($arrChildren);
    if($intChildren != 0) {
        for($i=0;$i<$intChildren;$i++) {
            $arrChildren[$i][$strNameResolution] = parse_into_tree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);
        }        
    }
    
    return $arrChildren;

}

function parse_into_menu($arrMenu,$strChildKey,$strNameKey,$intRunner=0) {
    
    $strNL = "\n";
    $intMenu = count($arrMenu);
    
    for($i=0;$i<$intMenu;$i++) {
        if($i==0) { 
            echo str_repeat("\t",$intRunner),'<ul>',$strNL; 
        }
        
        if(!empty($arrMenu[$i][$strChildKey])) {
            echo str_repeat("\t",$intRunner+1),'<li>',$arrMenu[$i][$strNameKey],$strNL;
            parse_into_menu($arrMenu[$i][$strChildKey],$strChildKey,$strNameKey,$intRunner+2);
            echo str_repeat("\t",$intRunner+1),'</li>',$strNL;
        } else {
            echo str_repeat("\t",$intRunner+1),'<li>',$arrMenu[$i][$strNameKey],'</li>',$strNL;
        }
        
        if($i==($intMenu-1)) { 
            echo str_repeat("\t",$intRunner),'</ul>',$strNL; 
        }
    }
    
} 

Output:

<ul>
	<li>Tv & Video
		<ul>
			<li>Televisions</li>
			<li>DVD Players</li>
		</ul>
	</li>
	<li>Audio
		<ul>

			<li>Car Audio</li>
			<li>Music
				<ul>
					<li>Compact Discs
						<ul>
							<li>Alternative</li>
							<li>Rock</li>
							<li>Rap</li>

						</ul>
					</li>
				</ul>
			</li>
		</ul>
	</li>
	<li>Toys & Games</li>
	<li>Cameras & Camcorders</li>

	<li>Cell Phone & Office</li>
</ul>

[oikram]

in your first post, it certainly looked like all you wanted was the subcategories

I'm sorry. You are absolutely right. I forgot something on my previous post. :s

now, it seems that you want the subsubcatories as well, bu only one of them

Sorry. I wanted the subsubcategories as well, but only one of each kind. I want to avoid repetition on subsubcategories as well as in subcategories.

my advice is that if you show only one of them, it is misleading to the user

To oddz consideration as well:
My goal is to have an array of objects with subcategories and sub-subcategories inside. Once I have then, I intend to loop trought the results of this array showing on one page: a list of subcategory links only.
Once the user clicks one of those subcategorie links, on another page, a URI segment will change, and I will get the list of subcategories as well as the list of the correspondent sub-subcategories.

According to my hardly explained intends , shouldn't this be a nice query solution?


thanks a lot,
Márcio






ps - A little more about the intended result:
When the user clicks over one of the subcategories or sub-subcategories, the third segment of the URI will change to a value equal to the id_cat value stored on the database.
Each time the user clicks on this list of links on the left side of the page: I will list (on the right side) all the products where the product cat_id will be = to the URI value on the third segment. (more then a menu, I'm talking about some sort of filtering mechanism).

[oddz]

My goal is to have an array of objects with subcategories and sub-subcategories inside. Once I have then, I intend to loop trought the results of this array showing on one page: a list of subcategory links only.
Once the user clicks one of those subcategorie links, on another page, a URI segment will change, and I will get the list of subcategories as well as the list of the correspondent sub-subcategories.

According to my hardly explained intends , shouldn't this be a nice query solution?

Same concept as posted above. The only difference is that the call to parse_into_tree() first argument will be the primary key of the current item. So if someone is on the electronics page and electronics has a primary key of 309 then you send in 309 as the parent id. You still need to select every row though because with an infinite depth menu there is no way use a where clause. Instead you need to bring in everything and parse it on the application side. make sense?

[oddz]

So to get all the menu for items with a parents_id of 10 the below would work. I had to change the comparison in parse_into_tree() from identity to equality as well.

...

parse_into_menu(parse_into_tree(10,$arrRows,'cats_id','parents_id','cats'),'cats','name');

function parse_into_tree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {
    
    $arrChildren = array();

    for($i=0;$i<count($arrRows);$i++) {
        if($intParentId == $arrRows[$i][$strParentsIdField]) {
            $arrChildren = array_merge($arrChildren,array_splice($arrRows,$i--,1));
        }
    }
    
    $intChildren = count($arrChildren);
    if($intChildren != 0) {
        for($i=0;$i<$intChildren;$i++) {
            $arrChildren[$i][$strNameResolution] = parse_into_tree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);
        }        
    }
    
    return $arrChildren;

}
... 

Output:

<ul>
	<li>Alternative</li>
	<li>Rock</li>
	<li>Rap</li>
</ul>

[oikram]

I'm really trying to keep this simple:

Let's say that, for this project, I don't want unlimited categories. I want no more then a 2 level array to be returned actually.

If this is the case, then a nice builded query will sufice no?


Ahhh!!! :P
(I have a voice on the back of my head telling me
However, we all know the client, they say: "Nooo.... I will never have more then 2 subcategories" and, one year later or even less: "Hi, I cannot add another sub sub sub sub category! What's going on?!!!".
So YES, I should follow oddz solution.


Let me be pertinent, I'm really sorry for this, , If I create another thread, can I have your help on building a multi-level category filter system based on URI segment changes and subcategories id, on a MVC structure, that, by the way, is supported by a framework called CodeIgniter that I have followed like a looser because, with my "newbility",I was unable to build my own framework, but hey I get myself a MVC structure (breeeaad)?

If not, and I perfectly understand it, I will stay with a fixed subcategory solution, if yes. Let's gooo!!!!!



Regards,
Márcio

[oikram]

Same concept as posted above. The only difference is that the call to parse_into_tree() first argument will be the primary key of the current item. So if someone is on the electronics page and electronics has a primary key of 309 then you send in 309 as the parent id. You still need to select every row though because with an infinite depth menu there is no way use a where clause. Instead you need to bring in everything and parse it on the application side. make sense?

I see... so untill today, I was always trying to actually build different arrays, based on the URI, and I was getting different arrays, but the parents never showed up. But, what you are saying is that I should retriev, on every page, of my navigation, always an array containing all the three, then, based on the value of the URI I can then remove the parts that are not needed to show the menu?

What I need to find then, must be the logic behind the removing keys and values of an array, based on the id passed on a specific segment of an uri.

The tree will be on my model, and the "tree cuts" must be a task for the controller.


Was this what you have suggested on the above quoted comment?


Regards,
Márcio