OQL - "Incidents of My Group"

[therm]

Hello,

I am trying hard to get a new menu, that should display all Incidents of the groups the user who has logged in is in.

This is what I ment, but it seems to me that OQL does not have as many features SQL has:
$oNode = new OQLMenuNode('Incident:MyGroupIncidents', 'SELECT Incident WHERE workgroup_id IN ( select lnkTeamToContact.team_id WHERE agent_id = :current_contact_id )', $oShortcutNode->GetIndex(), 4

The Problem with this select is that OQL seems not to know "lnkTeamToContact.team_id" after the select.

Is anyone here who knows a solution for that?

Thanks and regards,
therm

[Romain Quetiez]

Hi Therm,

Yes the OQL is a limited subset of SQL.

An OQL reference guide will be available soon. Meanwhile, let me give you a quick answer:
OQL supports only SELECT statements, and does not implement GROUP BY.
Joins are supported and it will address your issue.

If I'm not wrong the expected query should look like:
SELECT Incident AS i JOIN Team AS t ON i.workgroup_id = t.id JOIN lnkTeamToContact AS lnk ON lnk.team_id = t.id WHERE lnk.contact_id = :current_contact_id

Regards,
Romain

[therm]

Hello Romain,

big thanks to you, it does work!

@others who have the same wish:
You have to add the following to "modules/itop-incident-mgmt-1.0.0/model.itop-incident-mgmt.php":

$oNode = new OQLMenuNode('Incident:MyGroupIncidents', 'SELECT Incident AS i JOIN Team AS t ON i.workgroup_id = t.id JOIN lnkTeamToContact AS lnk ON lnk.team_id = t.id WHERE lnk.contact_id = :current_contact_id', $oShortcutNode->GetIndex(), 4 /* fRank */);
$oNode->SetParameters(array('auto_reload' => 'fast'));

and add a line to your locale file, in my case it is "modules/itop-incident-mgmt-1.0.0/de.dict.itop-incident-mgmt.php"

'Menu:Incident:MyGroupIncidents' => 'Meine Gruppen-Incidents',

Thanks again for this great software!
Regards, therm

[therm]

Hello @all,

I have made a change in order to get the closed tickets out of the list:

$oNode = new OQLMenuNode('Incident:MyGroupIncidents', 'SELECT Incident AS i JOIN Team AS t ON i.workgroup_id = t.id JOIN lnkTeamToContact AS lnk ON lnk.team_id = t.id WHERE lnk.contact_id = :current_contact_id AND i.status IN ("new", "assigned", "escalated_tto", "escalated_ttr", "resolved")', $oShortcutNode->GetIndex(), 4 /* fRank */);

So if there is somebody who wants this feature, please try out the updated line.

Regards,
therm

[Romain Quetiez]

Hi,

I would recommend another syntax: specify "not closed". The query is easier to read, and it will survive to an change such as adding a state to the tickets.

SELECT Incident AS i JOIN Team AS t ON i.workgroup_id = t.id JOIN lnkTeamToContact AS lnk ON lnk.team_id = t.id WHERE lnk.contact_id = :current_contact_id AND i.status NOT IN ("closed")

[therm]

ok, but why are you using the string "("new", "assigned", "escalated_tto", "escalated_ttr", "resolved")'" for your OQLMenuNode('Incident:OpenIncidents'…) ?
Isnt it the same thing here?

[Jonathan Vogt]

what Romain ment is:

When you add another state to a incident (for example reopened) the query with not closed will be correct. The query using "("new", "assigned", "escalated_tto", "escalated_ttr", "resolved")'" will miss those incidents.