• joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Error editing (updating) Account Groups in 2.3 CVS

1. Recursivity is not available in all version of MySQL (3.23). We may, however, skip 3.23 in 2.3 if this is not used anymore according to our polls.
2. It is not adviseable to handle the grouping as strings and numeric as per se.

/Joe

  • LUTi
  • Member
  • Offline
  • From: Slovenia, Notranje Gorice
  • Registered: 01/07/2010
  • Posts: 42

Re: Error editing (updating) Account Groups in 2.3 CVS

About 2 - I believe the current solution (situation) is not consistent.

If levels are not automatic and defined by number of digits (so 0-9 is 1st level, 00-99 is 2nd level and so on), we cannot accept to order as we do now. 13 cannot be between 1 and 2, I hope this is clear. Why ordering as I've suggested is not good? Why groups or subgroups within one level (and under the same parent...) couldn't (or, shouldn't) be grouped according to normal (human understandable) counting (numbering)?

By the way, I've noticed my query is not 100% OK, I have to work on it a bit more. But generally, I think within a (sub)group of "equally weighted" (the same level and parent) entries, sorting has to be done as we count. Or, limit the number of entries within such group to 10 entries (1 digit), to avoid confusion.

  • LUTi
  • Member
  • Offline
  • From: Slovenia, Notranje Gorice
  • Registered: 01/07/2010
  • Posts: 42

Re: Error editing (updating) Account Groups in 2.3 CVS

Here is a query which seems to handle up to 6 levels, as long as COA doesn't become really silly:

SELECT c8.id, c8.name, c8.class_id, c8.parent, c8.inactive FROM (
  SELECT
    IF(c7.parent1 = '-1', c7.id, IF(c7.parent2 = '-1', c7.level5, IF(c7.parent3 = '-1', c7.level4, IF(c7.parent4 = '-1', c7.level3, IF(c7.parent5 = '-1', c7.level2, c7.level1))))) AS group1,
    IF(c7.parent2 = '-1', c7.id, IF(c7.parent3 = '-1', c7.level5, IF(c7.parent4 = '-1', c7.level4, IF(c7.parent5 = '-1', c7.level3, c7.level2)))) AS group2,
    IF(c7.parent3 = '-1', c7.id, IF(c7.parent4 = '-1', c7.level5, IF(c7.parent5 = '-1', c7.level4, c7.level3))) AS group3,
    IF(c7.parent4 = '-1', c7.id, IF(c7.parent5 = '-1', c7.level5, c7.level4)) AS group4,
    IF(c7.parent5 = '-1', c7.id, c7.level5) AS group5,
    c7.* FROM (
      SELECT
        IF(parent1 = '-1', 1, IF(parent2 = '-1', 2, IF(parent3 = '-1', 3, IF(parent4 = '-1', 4, IF(parent5 = '-1', 5, 6))))) AS grp_level,
        IF(c6.parent5 = '-1', c6.level2, c6.parent5) AS level1,
        c6.* FROM (
          SELECT
            IF(c5.parent4 = '-1', '-1', (SELECT IF(parent = '', '-1', parent) FROM 0_chart_types cx4 WHERE cx4.id = c5.parent4)) AS parent5,
            IF(c5.parent4 = '-1', c5.level3, c5.parent4) AS level2,
            c5.* FROM (
              SELECT
                IF(c4.parent3 = '-1', '-1', (SELECT IF(parent = '', '-1', parent) FROM 0_chart_types cx3 WHERE cx3.id = c4.parent3)) AS parent4,
                IF(c4.parent3 = '-1', c4.level4, c4.parent3) AS level3,
                c4.* FROM (
                  SELECT
                    IF(c3.parent2 = '-1', '-1', (SELECT IF(parent = '', '-1', parent) FROM 0_chart_types cx2 WHERE cx2.id = c3.parent2)) AS parent3,
                    IF(c3.parent2 = '-1', c3.level5, c3.parent2) AS level4,
                    c3.* FROM (
                      SELECT
                        IF(c2.parent1 = '-1', '-1', (SELECT IF(parent = '', '-1', parent) FROM 0_chart_types cx1 WHERE cx1.id = c2.parent1)) AS parent2,
                        IF(c2.parent1 = '-1', c2.id, c2.parent1) AS level5,
                        c2.* FROM (
                          SELECT
                            IF(c1.parent = '' OR c1.parent = '-1', '-1', c1.parent) AS parent1,
                            c1.* FROM (
                              SELECT * FROM 0_chart_types c) AS c1) AS c2) AS c3) AS c4) AS c5) AS c6) AS c7) AS c8
ORDER BY CAST(group1 AS UNSIGNED), CAST(group2 AS UNSIGNED), CAST(group3 AS UNSIGNED), CAST(group4 AS UNSIGNED), CAST(group5 AS UNSIGNED), CAST(id AS UNSIGNED), grp_level