<div dir="ltr">Pavoľ, thank you for deep explanation! I think "OR" was change somewhere around redefined exclusions.<div><br></div><div>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.</div><div><br></div><div>Petr</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">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">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"></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 8:52 GMT+02:00 Pavol Mederly <span dir="ltr"><<a href="mailto:mederly@evolveum.com" target="_blank">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 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 href="https://wiki.evolveum.com/display/midPoint/Updated+Query+API#UpdatedQueryAPI-Valuefilters" target="_blank">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?
:-)<span class=""><br>
<pre class="m_-3460144585147606145moz-signature" cols="72">Pavol Mederly
Software developer
<a href="http://evolveum.com" target="_blank">evolveum.com</a>
</pre>
</span><div><div class="h5"><div class="m_-3460144585147606145moz-cite-prefix">On 03.10.2017 8:18, Petr Gašparík - AMI
Praha a.s. wrote:<br>
</div>
<blockquote type="cite">
<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="m_-3460144585147606145gmail_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: <a href="tel:603%20523%20860" value="+420603523860" target="_blank">[+420] 603 523 860</a><br>
e-mail: <a href="mailto:petr.gasparik@ami.cz" target="_blank">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.: <a href="tel:274%20783%20239" value="+420274783239" target="_blank">[+420] 274 783 239</a><br>
web: <a href="http://www.ami.cz/" target="_blank">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"></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">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_-3460144585147606145m_4290778675936729897moz-signature" cols="72">Pavol Mederly
Software developer
<a href="http://evolveum.com" target="_blank">evolveum.com</a>
</pre>
<div>
<div class="m_-3460144585147606145h5">
<div class="m_-3460144585147606145m_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="m_-3460144585147606145h5">
<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">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_-3460144585147606145m_4290778675936729897mimeAttachmentHeader"></fieldset>
<br>
</div>
</div>
<span>
<pre>______________________________<wbr>_________________
midPoint mailing list
<a class="m_-3460144585147606145m_4290778675936729897moz-txt-link-abbreviated" href="mailto:midPoint@lists.evolveum.com" target="_blank">midPoint@lists.evolveum.com</a>
<a class="m_-3460144585147606145m_4290778675936729897moz-txt-link-freetext" href="http://lists.evolveum.com/mailman/listinfo/midpoint" target="_blank">http://lists.evolveum.com/mail<wbr>man/listinfo/midpoint</a>
</pre>
</span></blockquote>
<br>
</div>
<br>
______________________________<wbr>_________________<br>
midPoint mailing list<br>
<a href="mailto:midPoint@lists.evolveum.com" target="_blank">midPoint@lists.evolveum.com</a><br>
<a href="http://lists.evolveum.com/mailman/listinfo/midpoint" rel="noreferrer" target="_blank">http://lists.evolveum.com/mail<wbr>man/listinfo/midpoint</a><br>
<br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset class="m_-3460144585147606145mimeAttachmentHeader"></fieldset>
<br>
<pre>______________________________<wbr>_________________
midPoint mailing list
<a class="m_-3460144585147606145moz-txt-link-abbreviated" href="mailto:midPoint@lists.evolveum.com" target="_blank">midPoint@lists.evolveum.com</a>
<a class="m_-3460144585147606145moz-txt-link-freetext" href="http://lists.evolveum.com/mailman/listinfo/midpoint" target="_blank">http://lists.evolveum.com/<wbr>mailman/listinfo/midpoint</a>
</pre>
</blockquote>
<br>
</div></div></div>
<br>______________________________<wbr>_________________<br>
midPoint mailing list<br>
<a href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a><br>
<a href="http://lists.evolveum.com/mailman/listinfo/midpoint" rel="noreferrer" target="_blank">http://lists.evolveum.com/<wbr>mailman/listinfo/midpoint</a><br>
<br></blockquote></div><br></div>