Announcement

Collapse
No announcement yet.

Scriptcase 8.1.032 - issue with custom mysql sql request

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Scriptcase 8.1.032 - issue with custom mysql sql request

    Hello,

    I don't understand why this sql request generate a malfonction when I activate the refined search function inside my grid.

    Sql Request :
    SELECT t.Id as ID,
    t.name as Titre,
    t.status as Statut,
    t.priority as Priorité,
    t.slas_id as SLAs,
    GROUP_CONCAT(DISTINCT concat(us.realname,' ',us.firstname) SEPARATOR ',') AS "Bénéficiaire FIDAL",
    GROUP_CONCAT(DISTINCT concat(uv.realname,' ',uv.firstname) SEPARATOR ',') AS Technicien,
    t.itilcategories_id as catégorie,
    t.requesttypes_id as Source,
    t.due_date as "Date d'échéance + Progression",
    t.takeintoaccount_delay_stat as "Délai de prise en compte",
    gg.name as "Groupe de techniciens",
    t.users_id_recipient as Rédacteur,
    GROUP_CONCAT(DISTINCT concat(uz.realname,' ',uz.firstname) SEPARATOR ',') AS "Demandeur FIDAL",
    coalesce(s.satisfaction, '0') as Satisfaction,
    s.comment as Commentaires,
    t.locations_id as Lieu,
    count(w.tickets_id_1) as "Nombre de ticket dupliqués",
    t.solve_delay_stat "Délai de résolution",
    t.close_delay_stat "Délai de clôture",
    t.waiting_duration "Délai en attente",
    t.type as Type,
    t.solvedate as "Date de résolution",
    t.Date as "Date d'ouverture",
    y.date as "Date Escalade Groupe SVP"
    FROM glpi_tickets t
    LEFT JOIN glpi_groups_tickets g
    ON t.ID = g.tickets_id
    LEFT JOIN glpi_tickets_users u
    ON t.ID = u.tickets_id and u.type = 1
    LEFT JOIN glpi_tickets_users v
    ON t.ID = v.tickets_id and v.type = 2
    LEFT JOIN glpi_tickets_users z
    ON t.ID = z.tickets_id and z.type = 3
    LEFT JOIN glpi_users us
    ON u.users_id = us.id
    LEFT JOIN glpi_users uv
    ON v.users_id = uv.id
    LEFT JOIN glpi_users uz
    ON z.users_id = uz.id
    LEFT JOIN glpi_groups gg
    ON g.groups_id = gg.id
    LEFT JOIN glpi_ticketsatisfactions s
    On t.Id = s.tickets_id
    LEFT JOIN glpi_tickets_tickets w
    On t.Id = w.tickets_id_1
    LEFT JOIN glpi_tickettasks y
    On t.Id = y.tickets_id and y.is_private = 1 and y.state = 0 and y.content REGEXP "Escalade" AND y.content REGEXP "SVP|Administration" and users_id_tech = 0
    WHERE t.is_deleted = '0'
    Group by t.Id

    Best Regards

    Mehdi


  • #2
    Did the log error prompt any message?

    Comment


    • #3
      Did you test it in your mysql workbench?

      Comment


      • #4
        Hello,
        The sql request is ok with scriptcase sql builder and mysql workbench.

        When I generated the grid, I have the following message error :

        Parse error: syntax error, unexpected '',us.firstname) SEPARATOR '' (T_CONSTANT_ENCAPSED_STRING), expecting ']' in C:\Users\mrhaiem\wwwroot\scriptcase\app\FidalITSM\ test\test_grid.class.php on line 282

        I don't understand.

        Thanks in advance for your support

        Best Regards
        Mehdi

        Comment


        • #5
          Take a look to these columns aliases:
          • "Demandeur FIDAL"
          • "Bénéficiaire FIDAL"
          • "Délai de prise en compte"
          • "Nombre de ticket dupliqués"
          MySql support them without problems because its engine is prepared for this kind of alias. However, when you expects to work with programming language as PHP, or a tool that used PHP like ScriptCase you will have a bad time, why? because ScriptCase need to parse the query in an "application layer" before run the query in "database layer". This step before sometimes get in problems when find symbols like ", ' (quotes) inside the query. Consider redefine your column's aliases by a name that could be more easy to understand to ScriptCase.

          There are not conventions on how to set an alias in sql context, but I advise strongly to follow some rules:

          1. Avoid to use " (quotes) in queries inside of ScriptCase to set a column alias

          NOT GOOD: select name as "cool name" from table where job = "xyz";
          GOOD: select name from table where job = "xyz";


          2. Use camel case standard to set alias:

          NOT GOOD: select name as "Nombre de ticket dupliqués" from table where job = "xyz";
          GOOD: select name as nombre_de_ticked_dupliques from table where job = "xyz";

          3. Do not use symbol like +, á, é, spaces, etc. These bad guys could bring troubles when trying to access them from php context.

          ScriptCase is powerful to parse SQL syntax, but sometimes you need bring it a query as clear as you can for a very safe work and easy to maintain your project






          Comment


          • #6
            Hello Manfred,
            I will follow up your recommendation and keep you informed.
            Thanks a lot
            Mehdi

            Comment


            • #7
              Hello,
              After to have follow up your recommendation, the situation change :

              when I setup refine search on the field gg.name, I have the following error :

              Erreur d'accès à la base de données :
              Unknown column 'gh.name' in 'field list'
              select gh.name, COUNT(*) from (SELECT t.Id, t.status, t.priority, t.slas_id, t.itilcategories_id, t.requesttypes_id, t.due_date, t.takeintoaccount_delay_stat, gh.name, t.users_id_recipient, coalesce(s.satisfaction, '0'), s.comment, t.locations_id, count(w.tickets_id_1), t.solve_delay_stat, t.close_delay_stat, t.waiting_duration, t.type, t.solvedate, t.Date, y.date as Date_Escalade_SVP FROM glpi_tickets t LEFT JOIN glpi_groups_tickets g ON t.ID = g.tickets_id LEFT JOIN glpi_tickets_users u ON t.ID = u.tickets_id and u.type = 1 LEFT JOIN glpi_tickets_users v ON t.ID = v.tickets_id and v.type = 2 LEFT JOIN glpi_tickets_users z ON t.ID = z.tickets_id and z.type = 3 LEFT JOIN glpi_users us ON u.users_id = us.id LEFT JOIN glpi_users uv ON v.users_id = uv.id LEFT JOIN glpi_users uz ON z.users_id = uz.id LEFT JOIN glpi_groups gh ON g.groups_id = gh.id LEFT JOIN glpi_ticketsatisfactions s On t.Id = s.tickets_id LEFT JOIN glpi_tickets_tickets w On t.Id = w.tickets_id_1 LEFT JOIN glpi_tickettasks y On t.Id = y.tickets_id and y.is_private = 1 and y.state = 0 and y.content REGEXP 'Escalade' AND y.content REGEXP 'SVP|Administration' and users_id_tech = 0 WHERE t.is_deleted = '0' Group by t.Id) nm_sel_esp where t.is_deleted = '0' GROUP BY gh.name order by gh.name ASC

              thanks in adavnce for your support

              Regards
              Mehdi

              Comment


              • #8
                That error is pretty normal now, looks like you are selecting a column that does not exist in the table. Verify if column name exists in glpi_groups table.

                Comment


                • #9
                  Hello Manfred,
                  you are right.However this sql request is generated by scriptcase when I setup the refine search.
                  Best Regards
                  Mehdi

                  Comment


                  • #10
                    Hello, sometimes you need to fix yourself these little things, take a review of your query and look which columns do you need and which ones do not.. Finally compile your query again in ScriptCase to load changes

                    Comment


                    • #11
                      Hello,

                      Thnaks for you reply.

                      There is an issue with SC on the generating sql for the refine search.(see the changes in bold)

                      Right Sql statement :

                      select nm_sel_esp.name, COUNT(*) from (SELECT t.Id, t.status, t.priority, t.slas_id, t.itilcategories_id, t.requesttypes_id, t.due_date, t.takeintoaccount_delay_stat, gh.name, t.users_id_recipient, coalesce(s.satisfaction, '0'), s.comment, t.locations_id, count(w.tickets_id_1), t.solve_delay_stat, t.close_delay_stat, t.waiting_duration, t.type, t.solvedate, t.Date, y.date as Date_Escalade_SVP FROM glpi_tickets t LEFT JOIN glpi_groups_tickets g ON t.ID = g.tickets_id LEFT JOIN glpi_tickets_users u ON t.ID = u.tickets_id and u.type = 1 LEFT JOIN glpi_tickets_users v ON t.ID = v.tickets_id and v.type = 2 LEFT JOIN glpi_tickets_users z ON t.ID = z.tickets_id and z.type = 3 LEFT JOIN glpi_users us ON u.users_id = us.id LEFT JOIN glpi_users uv ON v.users_id = uv.id LEFT JOIN glpi_users uz ON z.users_id = uz.id LEFT JOIN glpi_groups gh ON g.groups_id = gh.id LEFT JOIN glpi_ticketsatisfactions s On t.Id = s.tickets_id LEFT JOIN glpi_tickets_tickets w On t.Id = w.tickets_id_1 LEFT JOIN glpi_tickettasks y On t.Id = y.tickets_id and y.is_private = 1 and y.state = 0 and y.content REGEXP 'Escalade' AND y.content REGEXP 'SVP|Administration' and users_id_tech = 0 WHERE t.is_deleted = '0' Group by t.Id) nm_sel_esp GROUP BY nm_sel_esp.name order by nm_sel_esp.name ASC

                      wrong sql statement :

                      select gh.name, COUNT(*) from (SELECT t.Id, t.status, t.priority, t.slas_id, t.itilcategories_id, t.requesttypes_id, t.due_date, t.takeintoaccount_delay_stat, gh.name, t.users_id_recipient, coalesce(s.satisfaction, '0'), s.comment, t.locations_id, count(w.tickets_id_1), t.solve_delay_stat, t.close_delay_stat, t.waiting_duration, t.type, t.solvedate, t.Date, y.date as Date_Escalade_SVP FROM glpi_tickets t LEFT JOIN glpi_groups_tickets g ON t.ID = g.tickets_id LEFT JOIN glpi_tickets_users u ON t.ID = u.tickets_id and u.type = 1 LEFT JOIN glpi_tickets_users v ON t.ID = v.tickets_id and v.type = 2 LEFT JOIN glpi_tickets_users z ON t.ID = z.tickets_id and z.type = 3 LEFT JOIN glpi_users us ON u.users_id = us.id LEFT JOIN glpi_users uv ON v.users_id = uv.id LEFT JOIN glpi_users uz ON z.users_id = uz.id LEFT JOIN glpi_groups gh ON g.groups_id = gh.id LEFT JOIN glpi_ticketsatisfactions s On t.Id = s.tickets_id LEFT JOIN glpi_tickets_tickets w On t.Id = w.tickets_id_1 LEFT JOIN glpi_tickettasks y On t.Id = y.tickets_id and y.is_private = 1 and y.state = 0 and y.content REGEXP 'Escalade' AND y.content REGEXP 'SVP|Administration' and users_id_tech = 0 WHERE t.is_deleted = '0' Group by t.Id) nm_sel_esp where t.is_deleted = '0' GROUP BY gh.name order by gh.name ASC

                      Best Regards

                      Mehdi

                      Comment


                      • #12
                        Well.. with scriptcase I have nothing to do, at least I tried to fix your query. Wait for someone with more experience with this tool.

                        Comment


                        • #13
                          I opened a bug.thanks à gain for your support!!

                          Comment


                          • #14
                            you are very welcome!

                            Comment

                            Working...
                            X