This discussion has been locked.

You can no longer post new replies to this discussion. If you have a question you can start a new discussion

DEVELOPERS FORUM - AML - Differentiate between subquery results

オフライン Archive User 11 年以上前

dyonyssos - Monday, January 5, 2015 10:01 AM:

Hi,

I have an aml query that returns multiple relationship items. Id like to know which Item was returned from which subquery. Any ideas? (e.g. how to add attribute to item from aml query/how to add constant value to item from query (maybe a special select syntax?))

The AML:

<Item isNew="1" isTemp="1" type="ItemType" action="get" select="id">
<name>Action</name>
<Relationships>
    <Item isNew="1" isTemp="1" type="Property" select="name,data_source">
      <name condition="like">%d</name>
    </Item>
    <Item isNew="1" isTemp="1" type="Property" select="name,data_source">
      <name condition="like">t%</name>
    </Item>
</Relationships>
</Item>

PS: obviously this is a POC, my queries are much more complex.

DavidSpackman - Thursday, January 8, 2015 7:29 PM:

Hi dyonyssos,

Have a look at what the server returns by running this query using a tool such as AML studio. There is not a simple method to achieve this.

I agree that setting a dummy attribute/property is a good method

Couple of options

If your conditions will always be the same, you could add an OnAfterGet server event method to the action itemtype to add the dummy attribute/property
Perform the query with one set of criteria at a time, dummy attribute/property, then combine all results.

From the Aras Programmers Guide

Method setProperty(…)/setPropertyAttribute(…)not only sets new value for an existing Item property/ property’s attributes but creates new property/ property’s attributes and then sets its value if the property/ property’s attributes with a given name does not yet exist.

Once you get your results back from your query, you can iterate through to add your dummy attribute/property

Dave

Martin Fraser - Tuesday, January 13, 2015 6:03 AM:

Hi dyonyssos,

How are you using the results of your queries?

If you are iterating through the results, perhapse you could reapply the filters from the queries to the result set you get back?

Many thanks,
Martin.

dyonyssos - Monday, January 12, 2015 9:20 AM:

Hi David,

unfortunately our "famework" should support this for an arbitrary (well actually probably max 50) number of subqueries (which will end up as anonymous type properties).

In addition, this should work for any type, meaning we would have to add the onAfterGet to all item types. Then we would need to split all our queries based on subqueries, which I am afraid that can have a performance impact.

Currently our best idea is to duplicate all the subqueries, but limiting the second subquery to one result, and selecting only a handful of properties (more on this in a sec).. Then since the order of the results is guaranteed to be in the order of the subqueries, we wanted to use this second result as a delimiter (when we get the same id as the first result, its a match), that defines that now we are on the second subquery. Unfortunately since we can have subqueries without results too, we still need to differentiate between the delimiters, but that might be achieved by permutating some selected properties(e,g nothing, config_id, generation, config_id + generation, etc...).

We will probably get back to you if this doesnt work.

The result should not be overly bloated, so it is probably ok, but my concern is, that we need to duplicate the subqueries, which almost doubles the aml query size. Especially if we have queries with condition="in" & idlist on related item property, this can be quite big... (maybe compression would help?).

Anyways being able to select a static value into a result would simplify a lot, any idea if this feature could be added?

Cheers,

Matyas

PS: if you have any other ideas, please write!

dyonyssos - Tuesday, January 13, 2015 6:27 AM:

Hi Martin,

yes i know, i was thinking about that too, there are 2 problems with that:

1) you need to return all the data that is used in any filter along with the results. That might be unacceptable in some cases (e.g. when you search baseon on a child record ownership, and there is a lot of child records, but they would normally not be part of your result set).

2) Even if you do get back additionally the filter properties too, you still have a lot of null checking, which makes the code quite complex :(

But the first is a conceptual problem, so I would not like to go with that...

DavidSpackman - Monday, January 12, 2015 6:30 PM:

Hi Matyas,

Would need to know some detail behind how / when you are making these changes.

Here was a quick mock up of what I was suggesting using IOM

Item itm = MyInnovator.newItem("part", "get");
itm.setAttribute("select", "id,created_on,item_number, name");
itm.setProperty("name", "A%");
itm.setPropertyCondition("name", "like");
Item res = itm.apply();

// Test for an error.
if (res.isError())
{
	return MyInnovator.newError("Item not found: " + res.getErrorDetail());
}

int first_result_count = res.getItemCount();

for (var i = 0; i < res.getItemCount(); i++)
{
	res.getItemByIndex(i).setProperty("search_condition", "A%");
}


itm = MyInnovator.newItem("part", "get");
itm.setAttribute("select", "id,created_on,item_number, name");
itm.setProperty("name", "C%");
itm.setPropertyCondition("name", "like");
Item res2 = itm.apply();

// Test for an error.
if (res2.isError())
{
	return MyInnovator.newError("Item not found: " + res2.getErrorDetail());
}

int second_result_count = res2.getItemCount();

for (var i = 0; i < res2.getItemCount(); i++)
{
	res2.getItemByIndex(i).setProperty("search_condition", "C%");
}

res.appendItem(res2);

int final_count = res.getItemCount();

Result looks like this.

<SOAP-ENV:Envelope xmlns:SOAP-ENV="schemas.xmlsoap.org/.../">
	<SOAP-ENV:Body>
		<Result>
			<Item type="Part" typeId="4F1AC04A2B484F3ABA4E20DB63808A88" id="4FB82B66DA2644FABB5651042F7B6A9A">
				<created_on>2007-05-14T13:56:58</created_on>
				<css is_null="1"> .state {background-color:#9ACD32}</css>
				<current_state_bg_color>#9ACD32</current_state_bg_color>
				<external_owner is_null="1" />
				<has_cad>0</has_cad>
				<has_change_pending>0</has_change_pending>
				<has_documents>0</has_documents>
				<has_part_bom>0</has_part_bom>
				<id keyed_name="1599BSGY ABS Plastic Enclosure for XM Radio" type="Part">4FB82B66DA2644FABB5651042F7B6A9A</id>
				<name>ABS Plastic Enclosure for XM Radio</name>
				<state>Released</state>
				<item_number>1599BSGY</item_number>
				<itemtype>4F1AC04A2B484F3ABA4E20DB63808A88</itemtype>
				<has_change_icon><img src=''/></has_change_icon>
				<has_part_bom_icon><img src=''/></has_part_bom_icon>
				<has_documents_icon><img src=''/></has_documents_icon>
				<has_cad_icon><img src=''/></has_cad_icon>
				<search_condition>A%</search_condition>
			</Item>
			<Item type="Part" typeId="4F1AC04A2B484F3ABA4E20DB63808A88" id="52A5DA08D74647F6999D9CE501B60A6A">
				<created_on>2010-01-07T12:34:40</created_on>
				<css is_null="1"> .state {background-color:#9ACD32}</css>
				<current_state_bg_color>#9ACD32</current_state_bg_color>
				<external_owner is_null="1" />
				<has_cad>0</has_cad>
				<has_change_pending>0</has_change_pending>
				<has_documents>0</has_documents>
				<has_part_bom>0</has_part_bom>
				<id keyed_name="XX-370226" type="Part">52A5DA08D74647F6999D9CE501B60A6A</id>
				<name>Chasis - frame bar Black</name>
				<state>Released</state>
				<item_number>XX-370226</item_number>
				<itemtype>4F1AC04A2B484F3ABA4E20DB63808A88</itemtype>
				<has_change_icon><img src=''/></has_change_icon>
				<has_part_bom_icon><img src=''/></has_part_bom_icon>
				<has_documents_icon><img src=''/></has_documents_icon>
				<has_cad_icon><img src=''/></has_cad_icon>
				<search_condition>C%</search_condition>
			</Item>
		</Result>
	</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Dave

Martin Fraser - Tuesday, January 13, 2015 8:39 AM:

I am trying to understand your reason for wanting to return the subquery which found the records. There may be another ay of looking at the problem.

dyonyssos - Wednesday, January 14, 2015 3:38 AM:

Hi David,

thank you very much for the reply.

The concept i understand, but im a bit confused how woulld that work for a complexer scenario..

For example, lets say we want to know where we are loosing on precision in our datamodel.

For that, i want to have all the item types with decimal properties, and item properties that have again decimal or item properties. For the sake of no overcomplication, we skip relationships, and dont go deeper. (I also added the limit of getting only item types starting with h, so that we dont get so many of them.)

In c# i would do something like:

            var items = subject.ItemTypes
                .Where(i => i.Name.StartsWith("his"))
                .Where(i => i.Properties.Any(p => p.DataType == DataTypes.Decimal) || i.Properties.Any(p => p.DataSource != null))
                .SelectToList(a => new
                {
                    DecimalPrecisions = a.Properties.Where(p => p.DataType == DataTypes.Decimal).Select(x =>
                    new
                    {
                        Precision = x.Prec,
                        Name= x.Name
                    }),
                    ItemName = a.Name,
                    ArasProperties = a.Properties.Where(p => p.DataSource != null).Select(x => new
                    {
                        Name = x.Name,
                        ArasType = x.DataSource.Name,
                        DecimalPrecisions = x.DataSource.Properties.Where(p => p.DataType == DataTypes.Decimal).Select(pr =>
                        new
                        {
                            Precision = pr.Prec,
                            Name= pr.Name
                        }),
                        ArasProperties = x.DataSource.Properties.Where(p => p.DataSource != null).Select(pr =>
                        new
                        {
                            Name= pr.Name
                        })
                    }),
                });

Now in aml, i came up with (I added further restrictions in the property name to cr*, to reduce the test set:

<Item isNew="1" isTemp="1" type="ItemType" action="get" select="id,name">
<and>
    <name condition="like">his%</name>
      <Relationships>
        <Item isNew="1" isTemp="1" type="Property" action="get">
          <and>
            <name condition="like">cr%</name>
            <or>
              <data_type>decimal</data_type>
              <data_source condition="is not null" />
            </or>
          </and>
        </Item>
      </Relationships>
</and>
<Relationships>
        
    <Item isNew="1" isTemp="1" type="Property" action="get" isCriteria="0" select="prec,name">
      <data_type>decimal</data_type>
    </Item>
        
    <Item isNew="1" isTemp="1" type="Property" action="get" isCriteria="0" select="name,data_source">
      <and>
        <data_source condition="is not null"></data_source>
        <name condition="like">cr%</name>
      </and>
      <data_source>
        <Item isNew="1" isTemp="1" type="ItemType" action="get" select="name">
          <Relationships>
          
            <Item isNew="1" isTemp="1" type="Property" isCriteria="0" action="get" select="prec,name">
              <data_type>decimal</data_type>
              <name condition="like">cr%</name>
            </Item>
        
            <Item isNew="1" isTemp="1" type="Property" action="get" isCriteria="0" select="name">
              <data_source condition="is not null" />
              <name condition="like">cr%</name>
            </Item>
          </Relationships>
        </Item>
      </data_source>
    </Item>
</Relationships>
</Item>

Now if I understand correctly, I would need to create a custom method: getAndMark.I need to change the main action to getAndMark, and all subqueries I want to indentify as well. Then I have to find all the subqueries that need to be marked by an XPath Relationship/Item[@isCriteria="0"][@action="getAndMark"], and then include those subqueries one by one (obviously need to somehow recognize, that subquery 3 and 4 are children of 2, so I am actually having only 3 different cases). Lets say I manage to do that, the big question is, how do I merge the results?

I mean merging result (only showing item type: History) from subquery 1:

and subquery 3:

and subquery 4:

      <Item type="ItemType" typeId="450906E86E304F55A34B3C0D65C097EA" id="6323EE2C82E94CC4BB83779DDFDDD6F5">
        <id keyed_name="History" type="ItemType">6323EE2C82E94CC4BB83779DDFDDD6F5</id>
        <name>History</name>
        <Relationships>
          <Item type="Property" typeId="26D7CD4E033242148E2724D3D054B4D3" id="BE0AA91B52F341ACBB514304FC8E3D2A">
            <data_source keyed_name="User" type="ItemType" name="User">
              <Item type="ItemType" typeId="450906E86E304F55A34B3C0D65C097EA" id="45E899CD2859442982EB22BB2DF683E5">
                <id keyed_name="User" type="ItemType">45E899CD2859442982EB22BB2DF683E5</id>
                <name>User</name>
                <Relationships>
                  <Item type="Property" typeId="26D7CD4E033242148E2724D3D054B4D3" id="C80BF0625E524DBB95CAE332BAE944D4">
                    <id keyed_name="created_by_id" type="Property">C80BF0625E524DBB95CAE332BAE944D4</id>
                    <source_id keyed_name="User" type="ItemType" name="User">45E899CD2859442982EB22BB2DF683E5</source_id>
                    <name>created_by_id</name>
                  </Item>
                </Relationships>
              </Item>
            </data_source>
            <data_type>item</data_type>
            <id keyed_name="created_by_id" type="Property">BE0AA91B52F341ACBB514304FC8E3D2A</id>
            <source_id keyed_name="History" type="ItemType" name="History">6323EE2C82E94CC4BB83779DDFDDD6F5</source_id>
            <name>created_by_id</name>
          </Item>
        </Relationships>
      </Item>

Seems to be already not that straightforward, any ideas maybe?

PS unfortunately the approach we tried, failed, since maxRecords is a global counter for a subquery, so we didnt manage to convert it to any separators :(

The easiest would still be selecting a static value to a property...

dyonyssos - Wednesday, January 14, 2015 7:13 AM:

If you look at the above c# query, we are trying to convert it to AML.

In the anonumous type construction, we need to create the anonymous types from the Items returned. For that I need to know which result Item (from the subqueries) belongs to which anonymous property initialization, otherwise the result is incorrect. Until now I didnt find a way to do this distinguishing...

DavidSpackman - Thursday, January 15, 2015 2:16 AM:

Hi Matyas,

I'm not sure I 100% your requirements.

Where is the query ran from? (Within Aras OOTB client / Custom client/application / etc.)

How is the query generated?

Is it important that the query is written directly in AML? (If so, why?)

Is it an option to write this in .NET(C#) using the IOM (Innovator Object Model)?

Dave

dyonyssos - Thursday, January 15, 2015 5:21 AM:

Hi David,

maybe I forgot to give you some context :)

so we are developing a custom client relying on the IOM dll to communicate with Aras. We dont use any aras custom solutions, cause we have our own classes. We use the core db and our own types. Unfortunately after a while, the IOM API became to tedious, not refactory friendly, and in general even with method wrappers and some type casting, not ideal. So we set on a goal to get away from the string based API, and generated CLR classes for the Aras ItemTypes (we have a static datamodel, so it is not a problem).

Once we had classes, we set out to implement a query provider to search in aras. Obviously a lot of the methods are not supported in aras, but basic search functionality, ordering, paging, simple projection is already done. Now I am trying to extend our projection visitor (the one that analyzes the select method's expression tree), to support filtering of the returned subitems. (for example give me all itemtypes, and also the properties starting with c).

This is where we are atm.

            var items = context.ItemTypes
                .SelectToList(a => new
               {
                   PropertiesStartN = a.Properties.Where(p => p.Name.StartsWith("n")), //new_version is expected to be incluced in both
                   PropertiesEndsN = a.Properties.Where(p => p.Name.EndsWith("n"))
               });

So i needed a way to know how to couple up my subquery with the result it returns, so i can handle the filling out of anonymous properties correctly in the result.

We failed in our first attemp: duplicate the query, limit the second query to 1 record, and then in the result set find the result for the second query (which have to match by id the first result of the first query), and then you have the result of the first one. Unfortunately maxRecords="1" is a global setting, so if there are many results, the second subquery onlly works for one item, so tihs was a fail.

ATM I am evaluating your proposal, of splitting our main query, to include always exactly one of these special subqueries. The splitting might work (although not easy...), reconstructing the result does not sound easy either, and somehow after the first result we should limit the returned properties for the items that will always be in the result, to prevent enormous queries being duplicated or truplicated. This sounds really not easy.

So all at all it all sounds like an overcomplicated solution, that could be avoided, if we could mark in any recognizable way the result of a subquery.

Does this clarify a bit what I am trying to achieve?

Regards

Matyas

dyonyssos - Thursday, January 15, 2015 5:28 AM:

@David: Ah, sorry, i forgot to answer your questions.

- So the query runs from a client (IOM.dll).

- we generate the query using the IOM API

- if you have an idea for IOM based solution, I'm listening

- yes :)

Cheers

Martin Fraser - Monday, January 19, 2015 2:25 AM:

Matyas,

If you are not bound by AML, you could consider using SQL to build a temporary table holding the result of your queries, you could then insert the properties into this temporary table.

However you manage to add the property to your results, you are going to run into problems if 2 subquery's overlap. ie, you would need a new property for each subquery and be able to handle an ever more complex series of subqueries.

Have you considered splitting your queries up? If you are able to build your queries to return the full set of results you require and then apply a filter to the items locally, you can apply multiple filters to the same search results and append them together.

Many thanks,
Martin

dyonyssos - Thursday, January 22, 2015 6:34 AM:

Hi Martin,

do you mean totally skipping AML and writing directly to SQL?

If so, i was hoping that since the introduction of L2S, i would never ever have to write SQL embedded in c# code again, and I prefer to keep it that way.

The idea of running the queries locally seems to be the only solution, I can think of, as you mentioned. This is not so simple, but is probably doable. We might implement it in the future. For the time being we removed the support for multiple filtering on the same relationship within one query. It still works with different types, so the limitation is only on the same relationship, which is probably ok for now.

Luckily throwing a meaningful exception here is possible.

If there would ever come the support of selecting static values to a result, then we will definately reimplement this part. Otherwise only if we run into use cases, where we cannot live without it :)

Thank you very much for your help, it is really nice to know that there is someone reading these posts :)

Have a great day!

Matyas

DavidSpackman - Monday, January 26, 2015 5:53 PM:

How did you go with getting this working Matyas?

Dave.

dyonyssos - Wednesday, January 28, 2015 4:37 AM:

Hi David,

unfortunately I ran out of time (capacity), so we ended up not supporting this scenario. Luckily throwing the correct and meaningful exception was possible in case someone is trying to filter twice on the same subproperty. Filtering for different properties works fine (different aras relationship types), so I believe we can live with this limitation for the time being. In case someone gets the exception, he can always rewrite the query, and send it in 2 pieces.

I know it is disappointing, and if we would have more time, I would have probably implemented the splitting of the query on the server side (that seemed to be the solution with least limitations), but that sounds like at least 3-5 days of work and testing, and I have to do other stuff unfortunately.

In the meantime I hope we might get a feature of selecting a static value into a query, or marking results with attributes somehow, so that the solution would become much more straightforward :)

Cheers

Matyas

DEVELOPERS FORUM - AML - Differentiate between subquery results

Legal

Connect With Us