GetElementsByQuery - sort/ordering results

[OpenIT Solutions]

Hi,

I'm using GetElementsByQuery. The query has an order by clause. However the collection returned by GetElementsByQuery is not using the same order.

(its not using TreePos and Pos is not avalible)...Any idea how i can force the ordering of the collection returned by GetElementsByQuery ?

Regards,

Jon.

[qwerty]

If it doesn't sort (bug?!) then you probably need to move the result into a sortable collection and have that sorted externally.

q.

P.S. I tried it with my own SQL search and it did not return anything. Using e.g. Simple works. So how do you get the order clause into it?

[OpenIT Solutions]

Hi,

Your Model Search will need the CLASSGUID and CLASSTYPE columns, ie:

SELECT distinct t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE, t_object.name, t_diagramobjects.RectLeft, t_diagramobjects.RectTop FROM t_object, t_diagramobjects, t_connector, t_diagramlinks
WHERE t_diagramobjects.Diagram_ID = '<Search Term>'
AND t_object.Object_ID = t_diagramobjects.Object_ID
AND t_object.Object_Type IN ('Activity')
AND t_connector.Stereotype = 'SequenceFlow'
AND (t_connector.Start_Object_ID = t_object.Object_ID OR t_connector.End_Object_ID = t_object.Object_ID)
AND t_connector.Connector_ID = t_diagramlinks.ConnectorID
AND t_diagramlinks.DiagramID = t_diagramobjects.Diagram_ID
ORDER BY t_diagramobjects.RectLeft ASC, t_diagramobjects.RectTop DESC

I then call Repository.GetElementsByQuery ("My Query Name", <diagram_id>). I'd expect the activities returned to be in the order returned by the SQL query - they are not !

Regards,

Jon.

[qwerty]

I can confirm that the sort order is wrong. So: you need to sort yourself and to report a bug...

q.

[OpenIT Solutions]

OK - Do you know how i can get the RectLeft and RectTop coordinates from t_diagramobjects via the automation interface, ie i have a Diagram object, i can call Diagram.DiagramObjects, but this returns a collection of Elements....i need the rectleft and recttop in order to sort the collection

Any ideas ?

Thanks,

Jon.

[OpenIT Solutions]

Hi,

One you might want to add into your book qwerty - see response from Sparx re this:

Hello Jon,

Thank you for your enquiry.

Try using Repository.GetElementSet() instead of GetElementsByQuery.
GetElementSet supports loading of a collection of elements based upon a SQL query, and should observe any ORDER BY clause that is applied.

Example (JScript):

      var sql, i;
      var elements as EA.Collection;
      
      sql = "SELECT [...] FROM [...] WHERE [...] ORDER BY [...]";
      elements = Repository.GetElementSet(sql, 2);
      
      Repository.EnsureOutputVisible("Script");
      Session.Output("elements.Count=" + elements.Count);
      for (var i = 0; i < elements.Count; i++)
      {
            Session.Output("elements[" + i + "]=" + elements.GetAt(i).Name);
      }


This worked for me after i realised that Object_ID must be a column returned in the resultset....


Regards,


Jon.

[qwerty]

Thanks for the pointer! I'll definitely add that info.

q.

[stewartd]

Hi,

One you might want to add into your book qwerty - see response from Sparx re this:

Hello Jon,

Thank you for your enquiry.

Try using Repository.GetElementSet() instead of GetElementsByQuery.

Hi there,

I flagged this thread last year when I was experimenting with scripts that would run SQL queries against our model. I found a curious result and wonder if it is a bug?

In our project we've been using VBScript as the language of choice. I wrote a simple query (returning CLASSGUID, CLASSTYPE, Object_ID, name from t_object...) and pass that into GetElementSet

Code: [Select]

dim elements as EA.Collection
elements = Repository.GetElementSet(sql,2)

This fails with the error: Argument not optional.

So I then recrafted the script as a JScript one:

Code: [Select]

var elements as EA.Collection
elements = Repository.GetElementSet(sql,2)

This works as expected. Can anyone else confirm?

This is using EA 10, Build 1006.

Regards,

Damien.

[Aaron B]

GetElementSet returns an Object, so you need to use the Set keyword in VBScript when assigning the return value to a variable.

Code: [Select]

dim elements as EA.Collection
Set elements = Repository.GetElementSet(sql,2)Also, your SQL should only return the Object_ID column (i.e. ElementID values).  Other columns are not needed or used in this context and may only confuse things.

[stewartd]

Thanks Aaron B - that was the bit that was missing! I had the elements defined as EA.Collection, but had omitted the set keyword.

Finally, the SQL query ran fine. I could return the ID and other values without any problems (in this case, the name of the element.)

Thanks,

Damien.