Tilda JSON Syntax: View Joins

<-- View Syntax

Joins

In most cases, Tilda will be able to automatically deduce which joins are needed when pulling together multiple tables. For example, if you join Table A with Table B and A has a foreign key to B, Tilda will automatically use that: if the column is nullable, it will do a left join and if the column is not nullable, it will do an inner join.

      ,"joins": [
          { "object": "com.myCo.patients.data.Patients.Patient"
          , "joinType": "LEFT"
          , "on": [ { "db":"*", "clause":"Person.\"refnum\" = Patient.\"personRefnum\"" }
                  ]
          }

         ,{ "object": "com.myCo.patients.data.Patients.Visit"
          , "joinType": "LEFT"
          , "on": [ { "db":"*", "clause":"Visit.\"patientRefnum\" = Patient.\"refnum\"" }
                  ]
          }

The main fields are:

• joins: An array of zero or more join specifications.
  • object: The targeted object/view, using the "<package>.<schema>.<object|view>" notation, or simply <object|view> if the target is located in the same schema as this view definition.
  • as: a suffix, such as "_2" or "_3", to differentiate if the same table is joined multiple times.
  • joinType: LEFT|RIGHT|INNER|OUTER
  • on: An array of where clauses that can be specified for multiple database targets
    • db: Either "*" to denote a universal where clause, or a string such as "postgresql" or "sqlserver" that would match the target of the JDBC Url for the target database.
    • clause: A where clause fragment where column names are fully escaped.

🎈 NOTE: Automatic joins are deduced based on the order of the defined columns. So, if you start with columns from TableA and then follow with columns from TableB, and the two tables have a FK relationship (either from A to B or from B to A, then Tilda will automatically deduce it. And then if you follow with columns from TableC and TableC has a foreign key to TableA, Tilda's algorithm will walk up the list of columns until it finds a table with an FK relationship. In this case, it will skip B and hone in onto A.

🎈 NOTE: If a table is referenced in a list of columns and there is no obvious relationship found in prior column definitions, Tilda will check the list of explicit joins for a match. Even if an automatic join is found, a specified join will take precedence.

🎈 NOTE: Currently, Tilda only supports Postgres, so most people are using db="*" for their join clauses. In general., it's a good practice to try to use more standard SQL and avoid specific database idioms when possible. Sometimes, it's as easy as wrapping that functionality in a function or stored procedure to abstract the functionality. Most of the time though, it's unlikely that you'd write a complex enough application and target multiple backends concurrently.