Forum Discussion

PLMNewbie's avatar
PLMNewbie
Ideator I
15 days ago

AML Relationship traversal — filtering items from the related side

I have a relationship between two item types structured as:

ItemTypeA (source) → RelationshipType → ItemTypeB (related)

When querying from the source side it works correctly:

<Item type="ItemTypeA" action="get">
  <Relationships>
    <Item type="RelationshipType" action="get" select="related_id"/>
  </Relationships>
</Item>

But when I try to filter ItemTypeB by a property on ItemTypeA using the Relationships block, it returns zero results:

<Item type="ItemTypeB" action="get">
  <Relationships>
    <Item type="RelationshipType" action="get">
      <source_id>
        <Item type="ItemTypeA" action="get">
          <some_property condition="like">value%</some_property>
        </Item>
      </source_id>
    </Item>
  </Relationships>
</Item>

Using a SQL subquery in the where attribute is also not an option as Aras explicitly blocks it with:

<Item type="ItemTypeB" action="get"
  where="ItemTypeB.id IN (SELECT related_id FROM RelationshipType WHERE source_id IN (SELECT id FROM ItemTypeA WHERE some_property LIKE 'value%'))">
</Item>

Error:ItemAnalysisException

SELECT is forbidden in whereattribute

Question: Is there any supported AML syntax to filter items from the related side of a relationship without a separate pre-query? Or is a two-call approach the only supported option?

5 Replies

  • Kishor_Ramishte's avatar
    Kishor_Ramishte
    Ideator II
    11 days ago

    Hello!

    If you need to find parent items, you have a couple of approaches depending on whether you are looking up a single item or multiple items. Here is a clearer breakdown of how to handle this:

    Method 1: Using the getItemWhereUsed Action

    You can retrieve all the parents of an item using the getItemWhereUsed AML action. The primary limitation of this method is that it requires a specific id attribute, meaning you can only retrieve the parents for one item at a time.

    <AML>
    <Item type="ItemTypeB" action="getItemWhereUsed" id="value"/>
</AML>

    Method 2: Using an SQL Subquery (For Multiple Items)

    If you need to query multiple items at once, you can use an SQL subquery within the where attribute. However, for this to work, you must explicitly suppress the standard Item Analysis for these types of queries.

    Here are the steps to generate that suppression:

    1. Create a Suppressions File: Navigate to your \Innovator\Server\App_Data folder and create a new XML file following the naming convention ItemAnalysis.Suppressions.*.xml.

    Example: \Innovator\Server\App_Data\ItemAnalysis.Suppressions.MyCustom.xml

    2. Define the Template: Open the file and add the following XML format to define your suppression template:

    <itemAnalysis>
    <suppressions>
        <whereAttribute>
            <template><![CDATA[ItemTypeB.id IN (SELECT related_id FROM RelationshipType WHERE source_id IN (SELECT id FROM ItemTypeA WHERE some_property LIKE @Parameter))]]></template>
        </whereAttribute>
    </suppressions>
</itemAnalysis>

    3. Restart IIS and Execute: Save the file, restart your IIS server to apply the changes, and then execute your AML query.

    <AML>
    <Item type="ItemTypeB" action="get" where="ItemTypeB.id IN (SELECT related_id FROM RelationshipType WHERE source_id IN (SELECT id FROM ItemTypeA WHERE some_property LIKE 'value%'))"> </Item>
</AML>

     

  • mconstantin_aras_com's avatar
    mconstantin_aras_com
    Creator I
    14 days ago

    Good morning,

    try this:

    <Item type="ItemTypeB" action="get">
      <id condition="in">
        <Item type="RelationshipType" action="get" select="related_id">
          <source_id condition="in">
            <Item type="ItemTypeA" action="get" select="id">
              <some_property condition="like">value%</some_property>
            </Item>
          </source_id>
        </Item>
      </id>
    </Item>



    regards

    Michael

    • PLMNewbie's avatar
      PLMNewbie
      Ideator I
      14 days ago

      Hi Michael, 

      Thank you for the quick reply. I have tried this solution and the query was accepted by AML Studio without errors but returned incorrect results — ex: Count of 70 items (All the items) when the  expected result of items is around 5. It appears that related_id returns nested <Item> nodes rather than scalar GUIDs, so the id condition="in" filter does not match correctly.

      I ended up using a two-step approach which works but it needs to do an additional server call.

      1. Query the relationship type to collect the related IDs
      2. Inject those IDs as literal GUIDs using where="ItemTypeB.id IN ('GUID1','GUID2',...)" on the root query

      This gives the correct filtered results and also allows other AML filters (e.g. Relationships) to AND correctly alongside the ID filter.

      Let me know if this is the right approach. My intention was to do this in a single server call but instead I have to make two round trips. Is there a supported AML syntax that can achieve this in one call?

       

      • mconstantin_aras_com's avatar
        mconstantin_aras_com
        Creator I
        13 days ago

        Hello,

        I don’t think this can be achieved reliably in a single AML call.

        Relationships in AML are always evaluated from the source (parent) side, and source_id / related_id are just reference properties, not join operators. Because of that, AML does not support filtering items from the related side using conditions on the source item.

        A common approach is to implement a server-side method that performs the intermediate query and returns the final result in a single call.

        Regards,

        Michael