[midPoint] <not> clause breaking xml query (midpoint 3.6)

Petr Gašparík - AMI Praha a.s. petr.gasparik at ami.cz
Tue Oct 3 09:09:49 CEST 2017


Pavoľ, thank you for deep explanation! I think "OR" was change somewhere
around redefined exclusions.

Unfortunately, I do not know of any customer with such a need, so far. When
that will change in future, I will let you know.

Petr

--

s pozdravem

Petr Gašparík
solution architect

gsm: [+420] 603 523 860
e-mail: petr.gasparik at ami.cz


AMI Praha a.s.
Pláničkova 11
162 00 Praha 6
tel.: [+420] 274 783 239
web: www.ami.cz


[image: AMI Praha a.s.]

Textem tohoto e-mailu podepisující neslibuje uzavřít ani neuzavírá za
společnost AMI Praha a.s.
jakoukoliv smlouvu. Každá smlouva, pokud bude uzavřena, musí mít výhradně
písemnou formu.


2017-10-03 8:52 GMT+02:00 Pavol Mederly <mederly at evolveum.com>:

> Hello Petr,
>
> I originally thought the following would work; and it would naturally
> translate into "IN" clause.
>
> <query>
>   <filter>
>     <equal>
>       <path>employeeType</path>
>       <value>A</value>
>       <value>B</value>
>       <value>C</value>
>     </equal>
>   </filter>
> </query>
>
> But unfortunately it does not, at least not now. Maybe it worked some time
> in the past. It is necessary to use <or> for such comparison.
>
> See also note #2 below this table
> <https://wiki.evolveum.com/display/midPoint/Updated+Query+API#UpdatedQueryAPI-Valuefilters>.
> It would be quite straightforward to implement.
> ------------------------------
>
> But for negative conditions on multivalued items the problem is a bit
> deeper: currently we do a join of base entity with all the entities we need
> to query (representing e.g. multivalued items) and then formulate the
> conditions in the "where" section. Using this approach it is not possible
> to specify "give me users that do *not* have a particular value of
> organizationalUnit".
> When driving, just a while before, I thought a bit about this. Maybe it
> would not require embedded queries.
>
> This is a standard translation of
>
> <query>
>   <filter>
>     <equal>
>       <path>employeeType</path>
>       <value>A</value>
>     </equal>
>   </filter>
> </query>
>
> (I chose employeeType over organizationalUnit just because it's a plain
> string, so HQL is a bit simpler.)
> select
>   u.oid,
>   u.fullObject,
>   ...
> from
>   RUser u
>     left join u.employeeType e
> where
>   e = 'A'
>
> But it could be written also like this:
>
> select
>   u.oid,
>   u.fullObject,
>   ...
> from
>   RUser u
>     left join u.employeeType e on e = 'A'
> where
>   e is not null
>
> And this condition could be negated quite easily:
>
> select
>   u.oid,
>   u.fullObject,
>   ...
> from
>   RUser u
>     left join u.employeeType e on e = 'A'
> where
>   e is null
>
> So this would find all users that do not have an employeeType of 'A'!
>
> We just would need to move the condition from "where ..." to "join ... on
> ...". This requires some more thinking about possible consequences, but it
> would be perhaps doable.
>
> Do you know of any customer that would like to invest a couple of man-days
> (probably not that many days!) into the query interpreter? :-)
>
> Pavol Mederly
> Software developerevolveum.com
>
> On 03.10.2017 8:18, Petr Gašparík - AMI Praha a.s. wrote:
>
> Pavoľ,
> is there a way how to use "IN"/"NOT IN" in a filter?
>
> --
>
> s pozdravem
>
> Petr Gašparík
> solution architect
>
> gsm: [+420] 603 523 860 <603%20523%20860>
> e-mail: petr.gasparik at ami.cz
>
>
> AMI Praha a.s.
> Pláničkova 11
> 162 00 Praha 6
> tel.: [+420] 274 783 239 <274%20783%20239>
> web: www.ami.cz
>
>
> [image: AMI Praha a.s.]
>
> Textem tohoto e-mailu podepisující neslibuje uzavřít ani neuzavírá za
> společnost AMI Praha a.s.
> jakoukoliv smlouvu. Každá smlouva, pokud bude uzavřena, musí mít výhradně
> písemnou formu.
>
>
> 2017-10-03 7:38 GMT+02:00 Pavol Mederly <mederly at evolveum.com>:
>
>> Hello,
>>
>> you're right. The <not> clause doesn't work well (or maybe doesn't work
>> at all) with multivalued items. The reason is that the translation of such
>> queries to HQL/SQL is not so straightforward. Some kind of embedded query
>> or something like that would be needed. You could create a JIRA for this if
>> you want, but I am not sure when we'll have some time to implement that.
>>
>> And yes, I know that the query interpreter (i.e. the code that translates
>> midPoint queries to HQL queries) could be more polite and explain errors in
>> a better way... again, not enough time for that. :-)
>>
>> Best regards,
>>
>> Pavol Mederly
>> Software developerevolveum.com
>>
>> On 02.10.2017 23:17, Alcides Carlos de Moraes Neto wrote:
>>
>> So, this only happens with multivalued fields. Single valued fields work
>> just fine. Is this as intended?
>>
>> 2017-09-28 18:51 GMT-03:00 Alcides Carlos de Moraes Neto <
>> alcides.neto at gmail.com>:
>>
>>> Hello all,
>>>
>>> I'm trying to make a simple search query. I want to find all users with
>>> an assignment, that don't have XYZ value for organizational unit.
>>>
>>> I can do this just fine:
>>> <query>
>>>     <filter>
>>>         <and>
>>>             <ref>
>>>                 <path>assignment/targetRef</path>
>>>                 <value oid="7d66fc64-1540-44da-8f3d-c565bc917e12"/>
>>>             </ref>
>>>             <equal>
>>>                 <path>organizationalUnit</path>
>>>                 <value>XYZ</value>
>>>             </equal>
>>>         </and>
>>>     </filter>
>>> </query>
>>>
>>> This will fetch users with the assignment and the value
>>> organizationalUnit XYZ
>>>
>>> If I add the <not>...
>>> <query>
>>>     <filter>
>>>         <and>
>>>             <ref>
>>>                 <path>assignment/targetRef</path>
>>>                 <value oid="7d66fc64-1540-44da-8f3d-c565bc917e12"/>
>>>             </ref>
>>>             <not>
>>>               <equal>
>>>                   <path>organizationalUnit</path>
>>>                   <value>NQPPPS</value>
>>>               </equal>
>>>             </not>
>>>         </and>
>>>     </filter>
>>> </query>
>>>
>>>
>>> This results in a hibernate error:
>>> org.hibernate.HibernateException: SqlNode's text did not reference
>>> expected number of columns at org.hibernate.hql.internal.ast
>>> .tree.AbstractNullnessCheckNode.extractMutationTexts(Abstrac
>>> tNullnessCheckNode.java:151)
>>> ...
>>> at com.evolveum.midpoint.repo.sql.query2.hqm.RootHibernateQuery
>>> .getAsHqlQuery(RootHibernateQuery.java:96)
>>>
>>> Am I doing something wrong? Is it a bug? I'm using midpoint 3.6
>>> Is there any other way to get what I want from a query?
>>>
>>>
>>
>>
>> _______________________________________________
>> midPoint mailing listmidPoint at lists.evolveum.comhttp://lists.evolveum.com/mailman/listinfo/midpoint
>>
>>
>>
>> _______________________________________________
>> midPoint mailing list
>> midPoint at lists.evolveum.com
>> http://lists.evolveum.com/mailman/listinfo/midpoint
>>
>>
>
>
> _______________________________________________
> midPoint mailing listmidPoint at lists.evolveum.comhttp://lists.evolveum.com/mailman/listinfo/midpoint
>
>
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> http://lists.evolveum.com/mailman/listinfo/midpoint
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20171003/5fa99e7b/attachment.htm>


More information about the midPoint mailing list