[midPoint] <not> clause breaking xml query (midpoint 3.6)
Alcides Carlos de Moraes Neto
alcides.neto at gmail.com
Tue Oct 3 22:24:10 CEST 2017
Thank you for the explanations.
I was able to work around the issue by using an extension field, single
valued.
2017-10-03 4:09 GMT-03:00 Petr Gašparík - AMI Praha a.s. <
petr.gasparik at ami.cz>:
> 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 <+420%20603%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 <+420%20274%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 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
>>
>>
>
> _______________________________________________
> 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/807c5ef6/attachment.htm>
More information about the midPoint
mailing list