TACTIC Expressions compared SQL Join/Subselects

SQL is problematic when it comes to data from querying complex relationships between tables.  At first, SQL seems simple enough.  If you just want to get all the column for a single row, you can use a simple select statement:

SELECT name
FROM shot
WHERE code = 'SHOT001';

Because SQL has no inherent knowledge of the relationships between tables (some would argue this is the entire point), it doesn’t reflect how most databases are used.  The relationship between tables are often well defined, yet SQL forces you to reiterate these relationships on every statement.

However, as soon as you have to use relationships to other tables, it starts to blow up.  Here is an example using connector

SELECT *
FROM "asset"
LEFT OUTER JOIN "asset_in_shot"
ON "asset"."code" = "asset_in_shot"."asset_code"
LEFT OUTER JOIN "shot"
ON "asset_in_shot"."shot_code" = "shot"."code"
WHERE "shot"."type" = 'beauty'
  and "asset"."type" = 'prop';

Or using sub-selects:

SELECT *
FROM asset
WHERE
  code in (
    SELECT asset_code
    FROM asset_in_shot
    WHERE shot_code in (
      SELECT code
      FROM shot
      WHERE shot.type = 'beauty'
    )
  )
  AND
  "type" = 'prop';

It becomes quite unwieldy very quickly.  Not only is it long, but you have to deconstruct it to understand exactly what is being queried for.  In order to make it work, you have to start scoping all of the table name because of potential conflicts between column names. Also, note that you are required to know the relationship between every table accessed.

The TACTIC expression makes this unnecessary as the built-in schema defines the relationships between the tables so you don’t have to keep re-establishing them on every query.

This it becomes possible for a developer to navigate tables much more simply

@SOBJECT(vfx/asset.vfx/asset_in_shot.vfx/shot['code','SHOT001')

Adding filters in makes it even more complicated for SQL.  Whereas in the expression language, you would use:

@SOBJECT(vfx/asset['asset_type','prop'].vfx/asset_in_shot.vfx/shot['code','SHOT001']

When using the expression language in code, expressions become even more powerful.  Instead of using @SOBJECT, you can use @SEARCH to return a search object which can then have more filters applied.

expression = "@SEARCH(vfx/asset['asset_type','prop'].vfx/asset_in_shot.vfx/shot['code','SHOT001']"
search = Search.eval(expression)
search.add_filter("status", "Complete")
sobjects = search.get_sobjects()

The TACTIC expression language is not meant to replace SQL (it is actually compiles to an SQL query), but is meant to allow for retrieval of multiple table dependent data from database using a succinct syntax, often just a single line.