[midPoint] <not> clause breaking xml query (midpoint 3.6)
Pavol Mederly
mederly at evolveum.com
Tue Oct 3 08:52:17 CEST 2017
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 developer
evolveum.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
> e-mail: petr.gasparik at ami.cz <mailto: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 <http://www.ami.cz/>
>
>
>
> 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
> <mailto: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 developer
> evolveum.com <http://evolveum.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 <mailto: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(AbstractNullnessCheckNode.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 list
>> midPoint at lists.evolveum.com <mailto:midPoint at lists.evolveum.com>
>> http://lists.evolveum.com/mailman/listinfo/midpoint
>> <http://lists.evolveum.com/mailman/listinfo/midpoint>
>
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com <mailto:midPoint at lists.evolveum.com>
> http://lists.evolveum.com/mailman/listinfo/midpoint
> <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/3c30e947/attachment.htm>
More information about the midPoint
mailing list