I am relatively new to the Archi tool. I've exported some models/views to HTML and I noticed the Query tab at the top level of the map. I've figured out that if I run a query like this, it will return some results:
SELECT * FROM Properties WHERE Key = "Supported By"
However, I would like to filter the results further by finding entries where the Value is "Enterprise Architects". For example:
SELECT * FROM Properties WHERE key = 'Supported By' AND VALUE = 'Enterprise Architects'
However, this returns zero results. I've also tried the LIKE operator. No matter what I do, I can't seem to figure out how to query on the Value of the Key/Value pairs in Properties.
Also, once the above issue is figured out/resolved, I'll want to join the results back to the model to get the name of the item that matched the query.
I've looked for documentation for this but haven't found anything specific to Archi. Can anyone help me out and/or point me in the right direction?
--- Thanks, Jeff
OK, so after a little spelunking with F12 in Firefox, I am able to answer my own questions. :-)
For anyone else that is curious, here's the quick rundown of what I discovered...
First of all, three tables are exposed for querying with the Query tab at the root of the model tree:
- Elements
- Properties
- Relations
Knowing this, I was able to come up with some working query examples such as:
Ex. 1: Simple query matching the
value column within the
Properties table using a LIKE clause:
SELECT * FROM Properties WHERE Properties.value LIKE "%yes%"Ex. 2: Simple query with two comparisons within the WHERE clause:
SELECT * FROM Properties WHERE key = "Supported By" AND Properties.value LIKE "%architects%"Ex. 3: Simple query joining two tables,
Properties and
Elements, to display more information:
SELECT * FROM Properties JOIN Elements ON Properties.id = Elements.idEx. 4: Simple query joining two tables with two comparisons within the WHERE clause:
SELECT * FROM Properties JOIN Elements ON Properties.id = Elements.id WHERE key = "Supported By" AND Properties.value LIKE "%architects%"Ex. 5: Simple query that returns only one column, giving the column a custom name:
SELECT Elements.name AS [System Name] FROM Properties JOIN Elements ON Properties.id = Elements.id WHERE key = "Supported By" AND Properties.value LIKE "%architects%"Hopefully this will help someone else that might be looking for the same information.
--- Thanks, Jeff
Thank you very much Jeff :-)
If you're just trying this in 2019 versions of Archi generated html, be aware that there's been some renaming of table columns. For example, the last query would be something like:
SELECT Elements.name, Elements.documentation FROM Properties INNER JOIN Elements ON Properties.conceptid = Elements.id WHERE propkey = 'Ref' AND propvalue = 'Enterprise Architects'
So Properties has columns of propkey, propvalue and conceptid