Shortcut to show UserRequest created by me

[Marco]

Hi All,
I'm trying to create shortcut to display all the User Request created by me, but I'm having a problem with the person ID.

The working Query is :

    SELECT UserRequest AS req 
                  JOIN CMDBChangeOp AS op ON op.objkey = req.id
                  JOIN CMDBChange AS change ON op.change = change.id 
                  WHERE op.objclass = 'UserRequest' AND op.finalclass = 'CMDBChangeOpCreate' AND change.userinfo = '<user friendlyname>' 

But the problem is that in iTOP I have only the "current_contact_id" and then it is impossible to be used in the where condition.

I need something like a JOIN on the table Person, but not on the id but on the Friendly name. Something like:

SELECT UserRequest AS req 
          JOIN CMDBChangeOp AS op ON op.objkey = req.id
          JOIN CMDBChange AS change ON op.change = change.id 
          JOIN Person AS pers ON change.userinfo = pers.friendlyname
          WHERE op.objclass = 'UserRequest' AND op.finalclass = 'CMDBChangeOpCreate' AND pers.id = <ID>

But If I understood correctly the JOIN on one field that is not an ID is not possible.
Someone could help me to figure out?

Thanks in advance

Cheers,
Marco

[Vincent @ Combodo]

In iTop 3.0, CMDBChangeOp will contain user_id.
In the meantime, if the ticket creator is something important for you, might be better to add this information within the UserRequest as a field.

[Vincent @ Combodo]

Your current solution is really not efficient in terms of response time in a production database with thousands of Tickets, I have tested :-(

[Marco]

Hi Vdumas,
yes I will add a new field in the UserRequest class. At the moment make more sense.

Cheers,
Marco

[Marco]

Hi Vdumas,
yes I will add a new field in the UserRequest class. At the moment make more sense.

Cheers,
Marco

[Marco]

Hi Vdumas,
maybe I have another question for you and for sure for all the community.
I created a new field "creator_id" in the class , and then a new method , I'm using the attribe "impact" just to test.
The question is, there is a way to use the "itop_contact_id" in this function?

<method id="OnCreation" _delta="define">
          <static>false</static>
          <access>public</access>
          <type>Overload-DBObject</type>
          <code><![CDATA[
                        public function OnCreation()
                      {
                            parent::OnInsert();
                            $sCreator = $this->Get('impact');
                            $this->Set('creator_id', $sCreator);
                      }]]>
          </code>
        </method>

Cheers,
Marco

[Marco]

I don't know if I found the right solution, but this is working:

UserRights::GetContactId();

Thanks
Marco

[Pierre Goiffon]

Hello,
To me this is the right solution : you can see this is used already in Combodo's code, for example in \DBObject::SetCurrentPerson