Recursive SQL query to Get All Sub Categories Topic is solved

[sokrates_mx5]

I'm improving the function of JMailAlerts to better suit my needs.

To improve its plugin - JMailAlerts - Kunena - Latest Forum Posts - I want to get all sub categories of the chosen top-category/top-categories. For this I need to alter the SQL query of this plugin.

I've tried to accive it with the following SQL query, but it doesn't only gives the the categories and its sub-categories of ALL Kunena categories, not just the two categories 31 and 36, and its sub-categories.

Code: Select all

SELECT * 
FROM (
	SELECT m.*, c.name AS catname, c.ordering AS catorder, c.parent_id AS catparent, t.message 
	FROM #__kunena_messages AS m 
	INNER JOIN #__kunena_messages_text AS t ON m.id = t.mesid 
	INNER JOIN #__kunena_categories AS c ON c.id = m.catid 
	WHERE ( 
		m.catid=31 OR m.catid IN ( 
			SELECT c2.id 
			FROM #__kunena_categories c1, #__kunena_categories c2 
			WHERE c1.parent_id=31 OR m.catid IN ( 
				SELECT c2.id FROM #__kunena_categories c1, #__kunena_categories c2 
				WHERE c1.parent_id=31
				)
			)
		)
		OR ( 
			m.catid=36 OR m.catid IN ( 
				SELECT c2.id 
				FROM #__kunena_categories c1, #__kunena_categories c2 
				WHERE c1.parent_id=36 OR m.catid IN ( 
					SELECT c2.id 
					FROM #__kunena_categories c1, #__kunena_categories c2 
					WHERE c1.parent_id=36 
				)
			)
		) 
	AND m.moved = 0 AND m.hold = 0 AND 
	FROM_UNIXTIME(m.time) > '2021-04-15074806' 
	ORDER BY m.time 
	DESC LIMIT 6 
	) AS x 
ORDER BY x.catorder ASC, x.catparent ASC, x.catname ASC, x.thread ASC, x.time ASC

The above SQL query is generated by the following code:

Code: Select all

SELECT * FROM (
	SELECT m.*, c.name AS catname, c.ordering AS catorder, c.parent_id AS catparent, t.message 
	FROM #__kunena_messages AS m 
	INNER JOIN #__kunena_messages_text AS t ON m.id = t.mesid 
	INNER JOIN #__kunena_categories AS c ON c.id = m.catid 
	WHERE " . $catCondition . "
		AND m.moved = 0 AND m.hold = 0 AND 
	FROM_UNIXTIME(m.time) > " . $emaildate . " 
	ORDER BY m.time DESC LIMIT " . $no_of_posts . ") AS x 
	ORDER BY x.catorder ASC, x.catparent ASC, x.catname ASC, x.thread ASC, x.time ASC

Where $catCondition comes from:

Code: Select all

	// My own function
	private function subcats($ids) {

		$i = 0;
		foreach($ids as $id) {
		
			if ($i<1) {
				$sqlcat = null;
			}
			else {
				$sqlcat .= " OR ";
			}
//			$sqlcat .= 'm.catid = ' . $id; // Only gets the category chosen
// The following code shows all categories regardless where in the category three which is not wanted !!!
			$sqlcat .= " (
			m.catid=$id OR m.catid IN 
			( 	SELECT c2.id FROM #__kunena_categories c1, #__kunena_categories c2 
				WHERE c1.parent_id=$id OR m.catid IN 
				( 	SELECT c2.id FROM #__kunena_categories c1, #__kunena_categories c2 
					WHERE c1.parent_id=$id 
				)
			)
			)";

			$i++;
		}		
		return $sqlcat;
	}


		// Category filter
		if ($catid)
		{
			$ids = explode(',', $catid);
			$ids = ArrayHelper::toInteger($ids);
			$catCondition = $this->subcats($ids); // My Function to show sub-categories
		}

The database in use is mariaDB and I've tried to use the following example, but it throws an SQL-syntax error:

Below is an example of a Recursive CTE:

WITH RECURSIVE ancestors AS
( SELECT * FROM folks
WHERE name="Alex"
UNION
SELECT f.*
FROM folks AS f, ancestors AS a
WHERE f.id = a.father OR f.id = a.mother )
SELECT * FROM ancestors;

https://mariadb.com/kb/en/with/

By including the following code:

Code: Select all

	// My own function
	private function subcats($ids) {

		$i = 0;
		foreach($ids as $id) {
		
			if ($i<1) {
				$sqlcat = null;
			}
			else {
				$sqlcat .= " OR ";
			}
//			$sqlcat .= 'm.catid = ' . $id;

			$sqlcat .= " (
			m.catid=$id OR m.catid WITH RECURSIVE ancestors AS 
			( 	SELECT * FROM #__kunena_categories c
				WHERE c.parent_id=$id
				UNION
				SELECT c.*
				FROM #__kunena_categories AS c, ancestors AS a
				WHERE c.id = a.parent_id=$id )
			SELECT * FROM ancestors
			)";

			$i++;
		}		
		return $sqlcat;
	}

I also found the following solution for this kind of issue:

WITH RCTE AS
(
SELECT * , Id AS TopLevelParent
FROM dbo.Category c

UNION ALL

SELECT c.* , r.TopLevelParent
FROM dbo.Category c
INNER JOIN RCTE r ON c.ParentId = r.Id
)
SELECT
r.TopLevelParent AS ParentID
, r.Id AS ChildID
FROM RCTE r
ORDER BY ParentID;

https://stackoverflow.com/questions/455 ... categories

That I tried to incorporate in the same way in my code, but it throws an SQL-syntax error too.

Code: Select all

	// My own function
	private function subcats($ids) {

		$i = 0;
		foreach($ids as $id) {
		
			if ($i<1) {
				$sqlcat = null;
			}
			else {
				$sqlcat .= " OR ";
			}
//			$sqlcat .= 'm.catid = ' . $id;

			$sqlcat .= " (
			m.catid=$id OR m.catid WITH RCTE AS 
			( 
				SELECT * , Id AS TopLevelParent
				FROM dbo.#__kunena_categories c
			
				UNION ALL
			
				SELECT c.* , r.TopLevelParent
				FROM dbo.#__kunena_categories c
				INNER JOIN RCTE r ON c.ParentId = r.Id
			)
			SELECT 
  				r.TopLevelParent AS ParentID
				, r.Id AS ChildID 
			FROM RCTE r
			ORDER BY ParentID 
			)";

			$i++;
		}		
		return $sqlcat;
	}

Maybe I go wrong when I incorporate $sqlcat in the loop that I originally created to be able to list every single category that has been selected.

Any suggestions?

[sokrates_mx5]

Maybe I go wrong when I incorporate $sqlcat in the loop that I originally created to be able to list every single category that has been selected.

The $sqlcat has to be in the loop, since it's got the function to go through one or several of the categories being chosen.

I found the following that should help to do the job:

Code: Select all

To (recursively) query all sub-categories for one parent, the following query can be used:

with recursive cat_tree as (
   select id,
          name,
          parent_category
   from category
   where name = 'Database Software'  -- this defines the start of the recursion
   union all
   select child.id,
          child.name,
          child.parent_category
   from category as child
     join cat_tree as parent on parent.id = child.parent_category -- the self join to the CTE builds up the recursion
)
select *
from cat_tree;

https://www.sql-workbench.eu/comparison ... eries.html

I've altered it in the following way to be used for the #__kunena_categories table containing these fields: id, parent_id, etc. and I've "hard coded" it with id 31 that is one of my top categories:

Code: Select all

WITH RECURSIVE cat_tree AS (
   SELECT id,
          parent_id
   FROM #__kunena_categories
   WHERE id = 31
   UNION ALL
   SELECT child.id,
          child.parent_id
   FROM #__kunena_categories AS child
     JOIN cat_tree AS parent ON parent.id = child.parent_id
)
SELECT *
FROM cat_tree;

Now I get the chosen top category and its sub categories listed

[sokrates_mx5]

Now it works!

I just had to loop through the ids in the WHERE.

Code: Select all

	private function cats_subcats($ids) {

		$i = 0;
		foreach($ids as $id) {
		
			if ($i<1) {
				$sqlcat = null;
			}
			else {
				$sqlcat .= " OR ";
			}
			$sqlcat .= 'id = ' . $id;

			$i++;
		}		
		return $sqlcat;
	}
	
		// Category filter
		if ($catid)
		{
			$ids = explode(',', $catid);
			$ids = ArrayHelper::toInteger($ids);
			$catCondition = $this->cats_subcats($ids); // My Function to show sub-categories
		}
		
		$qry = "
		WITH RECURSIVE cat_tree AS (
   			SELECT id, parent_id
   			FROM #__kunena_categories
   			WHERE " . $catCondition . "
   			UNION ALL
   			SELECT child.id, child.parent_id
   			FROM #__kunena_categories AS child
     		JOIN cat_tree AS parent ON parent.id = child.parent_id
		)

		SELECT * 
		FROM (
			SELECT m.*, c.name AS catname, c.ordering AS catorder, c.parent_id AS catparent, t.message 
			FROM #__kunena_messages AS m 
			INNER JOIN #__kunena_messages_text AS t ON m.id = t.mesid 
			INNER JOIN #__kunena_categories AS c ON c.id = m.catid 
			WHERE ( 
				m.catid IN ( 
					SELECT id 
					FROM cat_tree 
				)
			)
			AND m.moved = 0 AND m.hold = 0 AND FROM_UNIXTIME(m.time) > " . $emaildate . " 
			ORDER BY m.time DESC LIMIT " . $no_of_posts . "
		) AS x 
		ORDER BY x.catorder ASC, x.catparent ASC, x.catname ASC, x.thread ASC, x.time ASC";