Scriptcase 8.1.032 - issue with custom mysql sql request

Scriptcase 8 Discussion
, , ,
#1

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?

#3

Did you test it in your mysql workbench?

#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 est est_grid.class.php on line 282

I don’t understand.

Thanks in advance for your support

Best Regards
Mehdi

#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

#6

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

#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

#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.

#9

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

#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

#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

#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.

#13

I opened a bug.thanks ? gain for your support!!

#14

you are very welcome!