Author Topic: Using AlaSql in Archi HTML Report  (Read 128 times)

jbramwell

  • Newbie
  • *
  • Posts: 4
Using AlaSql in Archi HTML Report
« on: November 09, 2018, 16:06:36 PM »
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:

Code: [Select]
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:

Code: [Select]
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

jbramwell

  • Newbie
  • *
  • Posts: 4
Re: Using AlaSql in Archi HTML Report
« Reply #1 on: November 10, 2018, 00:22:36 AM »
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:
Code: [Select]
SELECT * FROM Properties WHERE Properties.value LIKE "%yes%"
Ex. 2: Simple query with two comparisons within the WHERE clause:
Code: [Select]
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:
Code: [Select]
SELECT * FROM Properties JOIN Elements ON Properties.id = Elements.id
Ex. 4: Simple query joining two tables with two comparisons within the WHERE clause:
Code: [Select]
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:
Code: [Select]
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

Hervé

  • Hero Member
  • *****
  • Posts: 369
Re: Using AlaSql in Archi HTML Report
« Reply #2 on: November 10, 2018, 14:37:54 PM »
Thank you very much Jeff :-)