<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<p>Hello Petr,</p>
<p>I originally thought the following would work; and it would
naturally translate into "IN" clause. </p>
<p><tt><query></tt><tt><br>
</tt><tt> <filter></tt><tt><br>
</tt><tt> <equal></tt><tt><br>
</tt><tt> <path>employeeType</path></tt><tt><br>
</tt><tt> <value>A</value></tt><tt><br>
</tt><tt> <value>B</value></tt><tt><br>
</tt><tt> <value>C</value></tt><tt><br>
</tt><tt> </equal></tt><tt><br>
</tt><tt> </filter></tt><tt><br>
</tt><tt></query></tt></p>
<p>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.</p>
<p>See also note #2 below <a moz-do-not-send="true"
href="https://wiki.evolveum.com/display/midPoint/Updated+Query+API#UpdatedQueryAPI-Valuefilters">this
table</a>. It would be quite straightforward to implement.<br>
</p>
<hr size="2" width="100%">
<p>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
<b>not</b> have a particular value of organizationalUnit". <br>
</p>
When driving, just a while before, I thought a bit about this. Maybe
it would not require embedded queries.<br>
<p>This is a standard translation of</p>
<p><tt><query></tt><tt><br>
</tt><tt> <filter></tt><tt><br>
</tt><tt> <equal></tt><tt><br>
</tt><tt> <path>employeeType</path></tt><tt><br>
</tt><tt> <value>A</value></tt><tt><br>
</tt><tt> </equal></tt><tt><br>
</tt><tt> </filter></tt><tt><br>
</tt><tt></query></tt></p>
<p>(I chose employeeType over organizationalUnit just because it's a
plain string, so HQL is a bit simpler.)<br>
</p>
<tt>select</tt><tt><br>
</tt><tt> u.oid,</tt><tt><br>
</tt><tt> u.fullObject,</tt><tt><br>
...<br>
</tt><tt>from</tt><tt><br>
</tt><tt> RUser u</tt><tt><br>
</tt><tt> left join u.employeeType e</tt><tt><br>
</tt><tt>where</tt><tt><br>
</tt><tt> e = 'A'</tt><tt><br>
</tt><br>
But it could be written also like this:<br>
<br>
<tt>select</tt><tt><br>
</tt><tt> u.oid,</tt><tt><br>
</tt><tt> u.fullObject,</tt><tt><br>
</tt><tt> ...</tt><tt><br>
</tt><tt>from</tt><tt><br>
</tt><tt> RUser u</tt><tt><br>
</tt><tt> left join u.employeeType e on e = 'A'</tt><tt><br>
</tt><tt>where</tt><tt><br>
</tt><tt> e is not null</tt><tt><br>
</tt><br>
And this condition could be negated quite easily:<br>
<br>
<tt>select</tt><tt><br>
</tt><tt> u.oid,</tt><tt><br>
</tt><tt> u.fullObject,</tt><tt><br>
</tt><tt> ...</tt><tt><br>
</tt><tt>from</tt><tt><br>
</tt><tt> RUser u</tt><tt><br>
</tt><tt> left join u.employeeType e on e = 'A'</tt><tt><br>
</tt><tt>where</tt><tt><br>
</tt><tt> e is null<br>
</tt><br>
So this would find all users that do not have an employeeType of
'A'!<br>
<br>
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.<br>
<br>
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?
:-)<br>
<pre class="moz-signature" cols="72">Pavol Mederly
Software developer
evolveum.com
</pre>
<div class="moz-cite-prefix">On 03.10.2017 8:18, Petr Gašparík - AMI
Praha a.s. wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CABAspd3-TVz+4gQmVLf=cU8p8Q_50MM5HZKuTmTbiKUC9FNerA@mail.gmail.com">
<div dir="ltr">Pavoľ,
<div>is there a way how to use "IN"/"NOT IN" in a filter?</div>
</div>
<div class="gmail_extra"><br clear="all">
<div>
<div class="gmail_signature" data-smartmail="gmail_signature">
<div dir="ltr">
<div>
<div dir="ltr">
<p><span
style="font-family:Arial,sans-serif;font-size:10pt">--</span></p>
<p><span
style="font-family:Arial,sans-serif;font-size:10pt">s
pozdravem</span></p>
<table
style="font-family:Verdana,Arial,Helvetica,sans-serif;border-collapse:collapse;padding:0px;margin:0px;border-width:0px!important;border-style:solid!important;width:482px!important">
<tbody>
<tr style="padding:0px;margin:0px;border:0px solid
gray!important">
<td
style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:11px;width:160px;vertical-align:bottom;padding:0px;border:0px
solid gray!important">
<p><span
style="font-size:14px;font-weight:bold">Petr
Gašparík</span><br>
solution architect<br>
<br>
gsm: [+420] 603 523 860<br>
e-mail: <a
href="mailto:petr.gasparik@ami.cz"
target="_blank" moz-do-not-send="true">petr.gasparik@ami.cz</a></p>
</td>
<td
style="color:rgb(0,0,0);font-family:Verdana,Arial,Helvetica,sans-serif;font-size:10px;border-right-width:1px;border-right-style:solid;border-right-color:rgb(204,204,204);padding:0px;border-top-width:0px!important;border-bottom-width:0px!important;border-left-width:0px!important;border-top-style:solid!important;border-bottom-style:solid!important;border-left-style:solid!important;border-top-color:gray!important;border-bottom-color:gray!important;border-left-color:gray!important"> </td>
<td
style="color:rgb(0,0,0);font-family:Verdana,Arial,Helvetica,sans-serif;font-size:10px;padding:0px;border:0px
solid gray!important"> </td>
<td
style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:11px;vertical-align:bottom;padding:0px;width:123px;border:0px
solid gray!important">
<p>AMI Praha a.s.<br>
Pláničkova 11<br>
162 00 Praha 6<br>
tel.: [+420] 274 783 239<br>
web: <a href="http://www.ami.cz/"
target="_blank" moz-do-not-send="true">www.ami.cz</a></p>
</td>
<td
style="color:rgb(0,0,0);font-family:Verdana,Arial,Helvetica,sans-serif;font-size:10px;border-right-width:1px;border-right-style:solid;border-right-color:rgb(204,204,204);padding:0px;border-top-width:0px!important;border-bottom-width:0px!important;border-left-width:0px!important;border-top-style:solid!important;border-bottom-style:solid!important;border-left-style:solid!important;border-top-color:gray!important;border-bottom-color:gray!important;border-left-color:gray!important"> </td>
<td
style="color:rgb(0,0,0);font-family:Verdana,Arial,Helvetica,sans-serif;font-size:10px;padding:0px;border:0px
solid gray!important"> </td>
<td
style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:11px;margin:8px;border:0px
solid gray!important;width:116px">
<p><img
src="http://www.ami.cz/images/podpis/ami_logo.gif"
alt="AMI Praha a.s." style="border:0px"
moz-do-not-send="true"></p>
</td>
</tr>
<tr style="padding:0px;margin:0px;border:0px solid
gray!important">
<td colspan="7"
style="color:rgb(0,0,0);font-family:Verdana,Arial,Helvetica,sans-serif;font-size:10px;padding:0px;width:480px;border:0px
solid gray!important"><br>
</td>
</tr>
<tr style="padding:0px;margin:0px;border:0px solid
gray!important">
<td colspan="7"
style="color:rgb(128,128,128);font-family:Arial,sans-serif;font-size:11px;padding:0px;border:0px
solid gray!important">Textem tohoto e-mailu
podepisující neslibuje uzavřít ani neuzavírá
za společnost AMI Praha a.s.<br>
jakoukoliv smlouvu. Každá smlouva, pokud bude
uzavřena, musí mít výhradně písemnou formu.<br>
<br>
</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
<br>
<div class="gmail_quote">2017-10-03 7:38 GMT+02:00 Pavol Mederly
<span dir="ltr"><<a href="mailto:mederly@evolveum.com"
target="_blank" moz-do-not-send="true">mederly@evolveum.com</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF">
<p>Hello,</p>
<p>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.<br>
</p>
<p>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. :-)<br>
</p>
<p>Best regards,</p>
<pre class="m_4290778675936729897moz-signature" cols="72">Pavol Mederly
Software developer
<a href="http://evolveum.com" target="_blank" moz-do-not-send="true">evolveum.com</a>
</pre>
<div>
<div class="h5">
<div class="m_4290778675936729897moz-cite-prefix">On
02.10.2017 23:17, Alcides Carlos de Moraes Neto
wrote:<br>
</div>
</div>
</div>
<blockquote type="cite">
<div>
<div class="h5">
<div dir="ltr">So, this only happens with
multivalued fields. Single valued fields work just
fine. Is this as intended? </div>
<div class="gmail_extra"><br>
<div class="gmail_quote">2017-09-28 18:51
GMT-03:00 Alcides Carlos de Moraes Neto <span
dir="ltr"><<a
href="mailto:alcides.neto@gmail.com"
target="_blank" moz-do-not-send="true">alcides.neto@gmail.com</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0
0 0 .8ex;border-left:1px #ccc
solid;padding-left:1ex">
<div dir="ltr">Hello all,
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>I can do this just fine:</div>
<div>
<div><query></div>
<div> <filter></div>
<div> <and></div>
<div> <ref></div>
<div>
<path>assignment/targetRef</pa<wbr>th></div>
<div> <value
oid="7d66fc64-1540-44da-8f3d-c<wbr>565bc917e12"/></div>
<div> </ref></div>
<div> <equal></div>
<div>
<path>organizationalUnit</path<wbr>></div>
<div>
<value>XYZ</value></div>
<div> </equal></div>
<div> </and></div>
<div> </filter></div>
<div></query></div>
</div>
<div><br>
</div>
<div>This will fetch users with the
assignment and the value
organizationalUnit XYZ</div>
<div><br>
</div>
<div>If I add the <not>...</div>
<div>
<div><query></div>
<div> <filter></div>
<div> <and></div>
<div> <ref></div>
<div>
<path>assignment/targetRef</pa<wbr>th></div>
<div> <value
oid="7d66fc64-1540-44da-8f3d-c<wbr>565bc917e12"/></div>
<div> </ref></div>
<div> <not></div>
<div> <equal></div>
<div>
<path>organizationalUnit</path<wbr>></div>
<div>
<value>NQPPPS</value></div>
<div> </equal></div>
</not>
<div> </and></div>
<div> </filter></div>
<div></query></div>
</div>
<div><br>
</div>
<div><br>
</div>
<div>This results in a hibernate error:</div>
<div><span style="color:rgb(51,51,51);font-family:monospace;font-size:11px;white-space:pre-wrap">org.hibernate.HibernateExcepti<wbr>on: SqlNode's text did not reference expected number of columns
at org.hibernate.hql.internal.ast<wbr>.tree.AbstractNullnessCheckNod<wbr>e.extractMutationTexts(Abstrac<wbr>tNullnessCheckNode.java:151)</span><br>
</div>
<div>...</div>
<div><span style="color:rgb(51,51,51);font-family:monospace;font-size:11px;white-space:pre-wrap">at com.evolveum.midpoint.repo.sql<wbr>.query2.hqm.RootHibernateQuery<wbr>.getAsHqlQuery(RootHibernateQu<wbr>ery.java:96)</span><br>
</div>
<div><br>
</div>
<div>Am I doing something wrong? Is it a
bug? I'm using midpoint 3.6</div>
<div>Is there any other way to get what I
want from a query?<br>
</div>
<div><br>
</div>
</div>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset
class="m_4290778675936729897mimeAttachmentHeader"></fieldset>
<br>
</div>
</div>
<span class="">
<pre>______________________________<wbr>_________________
midPoint mailing list
<a class="m_4290778675936729897moz-txt-link-abbreviated" href="mailto:midPoint@lists.evolveum.com" target="_blank" moz-do-not-send="true">midPoint@lists.evolveum.com</a>
<a class="m_4290778675936729897moz-txt-link-freetext" href="http://lists.evolveum.com/mailman/listinfo/midpoint" target="_blank" moz-do-not-send="true">http://lists.evolveum.com/<wbr>mailman/listinfo/midpoint</a>
</pre>
</span></blockquote>
<br>
</div>
<br>
______________________________<wbr>_________________<br>
midPoint mailing list<br>
<a href="mailto:midPoint@lists.evolveum.com"
moz-do-not-send="true">midPoint@lists.evolveum.com</a><br>
<a
href="http://lists.evolveum.com/mailman/listinfo/midpoint"
rel="noreferrer" target="_blank" moz-do-not-send="true">http://lists.evolveum.com/<wbr>mailman/listinfo/midpoint</a><br>
<br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
midPoint mailing list
<a class="moz-txt-link-abbreviated" href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a>
<a class="moz-txt-link-freetext" href="http://lists.evolveum.com/mailman/listinfo/midpoint">http://lists.evolveum.com/mailman/listinfo/midpoint</a>
</pre>
</blockquote>
<br>
</body>
</html>