b2ap3 icon joomlaWe have been looking at refining a finder plugin in Joomla which is accessing a nested MySQL table.  Nothing complex about that one would think.

However the desire was to expand out the different sub record titles into a single ‘title’ comprised of the individual sub record titles linked together offered a few ‘opportunities’ for want of a different word!

To explain further.  We have a classic nested table structure as follows:


CREATE TABLE IF NOT EXISTS `#__it_projects` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'The system generated unique identifier for the project.',
  `parent_id` int(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Link to parent project id.',
  `title` varchar(255) NOT NULL COMMENT 'The unique name of the project.',
  `description` varchar(4000) DEFAULT NULL COMMENT 'A full description of the project.', 
  `lft` INT(11) NOT NULL DEFAULT '0' COMMENT 'Nested table left',
  `rgt` int(11) NOT NULL DEFAULT '0' COMMENT 'Nested table right',
  `level` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Nested table level.'

There are other fields but they are irrelevant for our description.

Thus for a sub project we desired to  see something like:  Project Level 1 – Level 2 – Level 3 …  etc.

We quickly came up with a simple query to obtain what we wanted which I list below:

select A.id, group_concat(title SEPARATOR ' - ') AS title from
(SELECT B.id, C.title FROM `d6r3h_it_projects` AS B, `d6r3h_it_projects` AS C
WHERE (B.lft BETWEEN C.lft AND C.rgt)
AND C.level != 0
ORDER BY B.lft, C.lft) AS A
GROUP by id;

Now the problem was to refine it and insert it into the finder plugin code. 

The refinement of the query involved changing the order by clauses such that when the GROUP_CONCAT statement would combine the fields in the correct order. We also ended up adding a couple of extra fields in the inner select clause so that we could see what was happening and to resolve all possible ordering concerns.

Then we turned to using this in our Joomla finder code, and where we started to hit problems.  The Joomla database library doesn’t like using sub-queries in the join.  Yes we could completely structure the code query construct but given that the rest of the query was quite complex with various CASE statements, multiple LEFT JOINS etc. this was decided to be overkill.  We tried other simple changes but abandoned these as being too complex.

We decided to look at what we could do in the database.  Possibly a function/procedure, but ended up look at VIEWS.  Now we find that our version of MySQL doesn’t like sub-queries in a view.  Seeing as the code might end up running on various MySQL database versions we ended up with two views.  The second calling the first.

The views we ended up with look as follows:

CREATE view #__it_project_view1 AS
SELECT B.id, B.parent_id AS pid, C.level, C.title FROM `#__it_projects` AS B, `d6r3h_it_projects` AS C
WHERE (B.lft BETWEEN C.lft AND C.rgt)
AND C.level != 0
ORDER BY B.lft, C.lft;

[We do not really need the parent_id field at all but it may come in handy if we have to look at it another time.]

create view #__it_project_view2 AS
select id, group_concat(title ORDER BY level ASC SEPARATOR ' - ') AS title FROM #__it_project_view1
group by id;

With these defined we could now change the finder code to add a simple one line LEFT JOIN clause and a simple select.

$sql->join('LEFT', '#__it_project_view2` AS w ON w.id = a.related_project_id');

It installed and worked perfectly, but will probably need more modification for other databases such as Oracle, SQL*Server etc.  An interesting way to pass away a few hours but such is the bane of developers everywhere.