<html><body><div style="font-family: arial, helvetica, sans-serif; font-size: 12pt; color: #000000"><div>Hi Sebastien,</div><div>In your case since you use orgRef with scope ONE_LEVEL, this could be easily rewritten to ref filter with dereference (aka "JOIN", see for <a href="https://docs.evolveum.com/midpoint/reference/concepts/query/query-api/#special-symbols-in-item-paths">https://docs.evolveum.com/midpoint/reference/concepts/query/query-api/#special-symbols-in-item-paths</a> ). </div><div><br data-mce-bogus="1"></div><div>The filter you are looking for is following:</div><div><br data-mce-bogus="1"></div><pre>Object Type: UserType</pre><div><pre><query><br> <filter><br> <ref><br> <path>parentOrgRef/@/roleMembershipRef</path><br> <value oid="o<span style="color: #000000; font-family: Calibri, sans-serif; font-size: 14.6667px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; display: inline !important; float: none;" data-mce-style="color: #000000; font-family: Calibri, sans-serif; font-size: 14.6667px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; display: inline !important; float: none;">id of the meta role</span>" /><br> </ref><br> </filter><br></query></pre></div><div><br data-mce-bogus="1"></div><div>Notice the path and @ symbol, in this case this means we are looking into parentOrgRef, the @ symbol represents dereference (aka use values from ref target) and now we can use roleMembershipRef in ref filter.</div><div>This filter is translated into following hibernate query in Query Playground:</div><div><br data-mce-bogus="1"></div><div><div>select u.oid, u.fullObject from RUser u</div><div> left join u.parentOrgRef p</div><div> left join p.target t</div><div> left join t.roleMembershipRef r</div><div>where</div><div> (</div><div> r.targetOid = :targetOid and</div><div> r.relation in (:relation)</div><div> )</div></div><div><br data-mce-bogus="1"></div><div><br data-mce-bogus="1"></div><div><br data-mce-bogus="1"></div><div>I tested this filter on 4.0.4 and it works that it selects all users in organization units, which has assigned meta role.</div><div>Hope this helps.</div><div><br data-mce-bogus="1"></div><div>Anton Tkacik</div><div><br data-mce-bogus="1"></div><hr id="zwchr" data-marker="__DIVIDER__"><div data-marker="__HEADERS__"><b>From: </b>"midPoint General Discussion" <midpoint@lists.evolveum.com><br><b>To: </b>"midPoint General Discussion" <midpoint@lists.evolveum.com><br><b>Cc: </b>"Sébastien MARBRIER" <smarbrier@positivethinking.tech><br><b>Sent: </b>Tuesday, June 7, 2022 5:07:20 PM<br><b>Subject: </b>[midPoint] Writing a query using the ouput of an other query.<br></div><div><br><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Segoe UI";
panose-1:2 11 5 2 4 2 4 2 2 3;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style></div><div data-marker="__QUOTED_TEXT__">
<div class="WordSection1">
<p class="MsoNormal">Hello dear midpoint users.</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"><span lang="EN-US">I need to list the users which are a members of organizations which are member of a given metarole.</span></p>
<p class="MsoNormal"><span lang="EN-US">I can get the list of the organizations using this simple query in the query playground:</span></p>
<p class="MsoNormal"><span lang="EN-US">Object type : OrgType</span></p>
<p class="MsoNormal"><span lang="EN-US"><query></span></p>
<p class="MsoNormal"><span lang="EN-US"> <filter></span></p>
<p class="MsoNormal"><span lang="EN-US"> <ref></span></p>
<p class="MsoNormal"><span lang="EN-US"> <path>roleMembershipRef</path></span></p>
<p class="MsoNormal"><span lang="EN-US"> <value oid="oid of the meta role/></span></p>
<p class="MsoNormal"><span lang="EN-US"> </ref></span></p>
<p class="MsoNormal"><span lang="EN-US"> </filter></span></p>
<p class="MsoNormal"><span lang="EN-US"></query></span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">I can also get of the users of such an organization using this other simple query:</span></p>
<p class="MsoNormal"><span lang="EN-US">Object Type : UserType </span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;font-family:'segoe ui' , sans-serif;color:#242424;background:white"><query></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;font-family:'segoe ui' , sans-serif;color:#242424;background:white"> <filter></span><span lang="EN-US" style="font-size:10.5pt;font-family:'segoe ui' , sans-serif;color:#242424"><br>
<span style="background:white"> <org></span><br>
<span style="background:white"> <orgRef></span><br>
<span style="background:white"> <oid>oid of an organization</oid></span><br>
<span style="background:white"> </orgRef></span><br>
<span style="background:white"> <scope>ONE_LEVEL</scope></span><br>
<span style="background:white"> </org></span><br>
<span style="background:white"> </filter></span><br>
<span style="background:white"></query></span></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;font-family:'segoe ui' , sans-serif;color:#242424;background:white"> </span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;font-family:'segoe ui' , sans-serif;color:#242424;background:white">However I am clueless how it is possible to mixing those two queries to automate the listing process.</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;font-family:'segoe ui' , sans-serif;color:#242424;background:white">Doing this by hand is a bit too tedious because there too many organizations.</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;font-family:'segoe ui' , sans-serif;color:#242424;background:white"> </span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;font-family:'segoe ui' , sans-serif;color:#242424;background:white">Alternative suggestions are welcome.</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;font-family:'segoe ui' , sans-serif;color:#242424;background:white">I am using a 4.0.1 version of midpoint.</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;font-family:'segoe ui' , sans-serif;color:#242424;background:white"> </span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;font-family:'segoe ui' , sans-serif;color:#242424;background:white">Best regards,</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.5pt;font-family:'segoe ui' , sans-serif;color:#242424;background:white"> </span></p>
<p class="MsoNormal"> </p>
<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0"><tbody><tr><td width="188" style="width:141pt;border:none;border-right:solid #ff1738 2.25pt;padding:0cm 7.5pt 0cm 0cm">
<p class="MsoNormal" align="right" style="text-align:right">
<a href="https://www.positivethinking.tech/" target="_blank" rel="nofollow noopener noreferrer"><span style="font-size:12pt;font-family:'times new roman' , serif;color:blue;text-decoration:none"><img border="0" width="170" height="79" style="width:1.7708in;height:0.8229in" id="_x0000_i1030" src="cid:image001.png@01D87A91.0B45CCC0" alt="logo"></span></a><span style="font-size:12pt;font-family:'times new roman' , serif"></span></p>
</td><td width="10" style="width:7.5pt;padding:0cm 0cm 0cm 0cm">
<p class="MsoNormal"><span style="font-size:12pt;font-family:'times new roman' , serif"> </span></p>
</td><td width="497" valign="top" style="width:372.75pt;padding:0cm 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10pt;font-family:'tahoma' , sans-serif;color:#ff0044">Sébastien Marbrier</span></b><span lang="EN-US" style="font-size:10pt;font-family:'tahoma' , sans-serif;color:#ff0044">
</span><span lang="EN-US" style="font-size:10pt;font-family:'tahoma' , sans-serif;color:#777777"> | Senior IT Consultant<br>
</span><span style="font-size:9pt;font-family:'tahoma' , sans-serif;color:#777777"><a href="mailto:smarbrier@positivethinking.tech" target="_blank" rel="nofollow noopener noreferrer"><span lang="EN-US" style="color:#ff0044">smarbrier@positivethinking.tech</span></a></span><span style="font-size:9pt;font-family:'tahoma' , sans-serif;color:#777777">
</span><span lang="EN-US" style="font-size:5pt;font-family:'tahoma' , sans-serif;color:#777777"><br>
</span><span lang="EN-US" style="font-size:9pt;font-family:'tahoma' , sans-serif;color:#777777"><br>
Tel. </span><span style="font-size:9pt;font-family:'tahoma' , sans-serif;color:#777777"><span style="color:#777777;text-decoration:none">+41 21 601 81 00</span>
</span><span style="font-size:5pt;font-family:'tahoma' , sans-serif;color:#777777"><br>
</span><span style="font-size:9pt;font-family:'tahoma' , sans-serif;color:#777777"></span></p>
<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0"><tbody><tr><td style="padding:0cm 0cm 0cm 0cm">
<p class="MsoNormal"><a href="https://teams.microsoft.com/l/chat/0/0?users=smarbrier@positivethinking.tech" target="_blank" rel="nofollow noopener noreferrer"><span style="font-size:12pt;font-family:'times new roman' , serif;color:blue;text-decoration:none"><img border="0" width="20" height="20" style="width:0.2083in;height:0.2083in" id="_x0000_i1029" src="cid:image002.png@01D87A91.0B45CCC0" alt="Teams chat"></span></a><span style="font-size:12pt;font-family:'times new roman' , serif"></span></p>
</td><td style="padding:0cm 0cm 0cm 0cm">
<p class="MsoNormal"><span style="font-size:12pt;font-family:'times new roman' , serif"><a href="https://teams.microsoft.com/l/chat/0/0?users=smarbrier@positivethinking.tech" target="_blank" rel="nofollow noopener noreferrer"><span style="font-size:9pt;font-family:'tahoma' , sans-serif;color:#777777;text-decoration:none"> </span></a>
</span></p>
</td><td style="padding:0cm 0cm 0cm 0cm">
<p class="MsoNormal"><span style="font-size:12pt;font-family:'times new roman' , serif"><a href="https://teams.microsoft.com/l/chat/0/0?users=smarbrier@positivethinking.tech" target="_blank" rel="nofollow noopener noreferrer"><span lang="EN-US" style="font-size:9pt;font-family:'tahoma' , sans-serif;color:#777777">Chat
with me on Teams</span></a></span><span style="font-size:12pt;font-family:'times new roman' , serif">
<span lang="EN-US"></span></span></p>
</td></tr></tbody></table>
<p class="MsoNormal"><span lang="EN-US" style="font-size:1pt;font-family:'tahoma' , sans-serif;color:#777777"><br>
</span><span lang="EN-US" style="font-size:12pt;font-family:'times new roman' , serif"></span></p>
</td></tr><tr><td style="border:none;border-right:solid #ff1738 2.25pt;padding:0cm 7.5pt 0cm 0cm">
<p class="MsoNormal" align="right" style="text-align:right">
<span lang="EN-US" style="font-size:12pt;font-family:'times new roman' , serif"><br>
</span><a href="https://www.linkedin.com/company/the-positive-thinking-company/" target="_blank" rel="nofollow noopener noreferrer"><span style="font-size:12pt;font-family:'times new roman' , serif;color:blue;text-decoration:none"><img border="0" width="20" height="20" style="width:0.2083in;height:0.2083in" id="_x0000_i1028" src="cid:image003.png@01D87A91.0B45CCC0"></span></a><span style="font-size:12pt;font-family:'times new roman' , serif">
</span><a href="https://www.instagram.com/positivethinkingcompany/" target="_blank" rel="nofollow noopener noreferrer"><span style="font-size:12pt;font-family:'times new roman' , serif;color:blue;text-decoration:none"><img border="0" width="20" height="20" style="width:0.2083in;height:0.2083in" id="_x0000_i1027" src="cid:image004.png@01D87A91.0B45CCC0"></span></a><span style="font-size:12pt;font-family:'times new roman' , serif">
</span><a href="https://twitter.com/PTC_Tech" target="_blank" rel="nofollow noopener noreferrer"><span style="font-size:12pt;font-family:'times new roman' , serif;color:blue;text-decoration:none"><img border="0" width="20" height="20" style="width:0.2083in;height:0.2083in" id="_x0000_i1026" src="cid:image005.png@01D87A91.0B45CCC0"></span></a><span style="font-size:12pt;font-family:'times new roman' , serif">
</span><a href="https://youtube.com/channel/UCfaImWa6r0IoZoUYLhbiF7w" target="_blank" rel="nofollow noopener noreferrer"><span style="font-size:12pt;font-family:'times new roman' , serif;color:blue;text-decoration:none"><img border="0" width="20" height="20" style="width:0.2083in;height:0.2083in" id="_x0000_i1025" src="cid:image006.png@01D87A91.0B45CCC0"></span></a><span style="font-size:12pt;font-family:'times new roman' , serif"></span></p>
</td><td valign="bottom" style="padding:0cm 0cm 0cm 0cm"></td><td valign="bottom" style="padding:0cm 0cm 0cm 0cm">
<p class="MsoNormal"><span style="font-size:9pt;font-family:'tahoma' , sans-serif;color:#777777">Avenue de cour, 135 – 1007 Lausanne<br>
<a href="https://www.positivethinking.tech/" target="_blank" rel="nofollow noopener noreferrer"><span style="color:#ff0044">www.positivethinking.tech</span></a>
<br>
</span><span style="font-size:3.5pt;font-family:'tahoma' , sans-serif;color:#777777"> </span><span style="font-size:12pt;font-family:'times new roman' , serif"></span></p>
</td></tr></tbody></table>
<p class="MsoNormal"> </p>
<p class="MsoNormal"><span style="font-size:10.5pt;font-family:'segoe ui' , sans-serif;color:#242424;background:white"> </span></p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"> </p>
</div>
<p></p>
<hr>
<span style="font-size:8px"><em><span style="font-family:'roboto' , 'arial'"><span style="color:#777777">Positive Thinking Company puts security at a high priority in its conduct of business. Therefore, we have put our best efforts into ensuring that this email and
its attached documents are error and virus-free. Nonetheless, full security of emails/documents cannot be ensured. Therefore, the recipient is responsible for checking the email/documents for threats with its own security measures, prior to opening it. Positive
Thinking Company does not accept liability for any damage inflicted by using the content of this email/documents. If you are not the intended recipient, please notify the sender and delete this email/document.</span></span></em></span>
<p></p>
<br>_______________________________________________<br>midPoint mailing list<br>midPoint@lists.evolveum.com<br>https://lists.evolveum.com/mailman/listinfo/midpoint<br></div></div></body></html>