Relationships

Table Relationship Objects

Relationship objects enable SQL Yoga to automate tasks that involve related tables in your database. Relationship objects unlock the following features in SQL Yoga:

• Turning SQL record sets into hierarchal arrays based on relationships between tables.
• Automatically linking and unlinking records in two related tables.
• Retrieving the related records from a table that is related to a SQL Record object's table.

Creating Relationship Objects

While Relationship objects can be created via the tblrelation_createObject API, it is simpler to create them in the table objects.yml file. You can see a list of the available relationship object properties by referring to the tblrelation_set documentation.

Here is some example YAML for creating relationship objects:

relationships:
  - name: projects to todo items
    type: one-to-many
    left table: projects
    left table key: id
    right table: todo_items
    right table key: project_id
    order by: todo_items.sequence
  - name: people to todo items
    type: many-to-many
    left table: people
    left table key: id
    cross-reference table: people_todo
    cross-reference table key for left table: people_id
    cross-reference table key for right table: todo_id
    right table: todo_items
    right table key: id
    order by: todo_items.name

Tip: You don't need to define table objects before you define relationship objects. When SQL Yoga parses the relationships it will create table objects as needed.

Using relationship objects when generating queries

Once you have defined Relationship objects you can leverage them when working with queries:

• Set the related table joins property on a SQL Query object to include columns from related tables.
• Call sqlquery_convertToRecords to retrieve the results of a SQL Query object query as a hierarchal array.
• Call sqlrecord_getRelated to get records related to the SQL Record objects.
• Link or unlink to records in the database using sqlrecord_link and sqlrecord_unlink
• When creating or updating a record (or records) using sqlquery_create, sqlquery_update, sqlrecord_create, and sqlrecord_update you can pass in nested arrays for related tables which will create new records in the related table and link them to the record passed in.

Joining Records

When working with SQL Query objects you can include records from other tables by setting the related table joins property. When defining a JOIN you usually have to specify the table to join as well as the fields to join on. For example, let's assume you have people and todo_items tables that have a many-to-many relationship through a table named people_todo and you want to get to-do items along with a list of all people associated with a todo_item.

If you didn't have any Relationship objects defined you would need to explicitly state how to join the tables together:

put sqlquery_createObject("todo_items") into tQueryA
sqlquery_set tQueryA, "related table joins", \
     "LEFT OUTER JOIN people_todo ON people_todo.todo_id = todo_items.id, " & \
     "LEFT OUTER JOIN people ON people.id = people_todo.people_id"

If you have defined Relationship objects then SQL Yoga will fill in the ON conditions for you. All you have to do is tell SQL Yoga which table to include in the JOIN:

put sqlquery_createObject("todo_items") into tQueryA
sqlquery_set tQueryA, "related table joins", "LEFT OUTER JOIN people"

SQL Yoga knows that people is related to todo_item through people_todo so all of the necessary SQL will be added to the query. The resulting query would be:

put sqlquery_get(tQueryA, "query")

SELECT todo_items.id, todo_items.name, todo_items.completed, todo_items.project_id, todo_items.sequence, people_todo.people_id, people_todo.todo_id, people.id, people.name, people.email
FROM todo_items
LEFT OUTER JOIN people_todo ON people_todo.todo_id = todo_items.id
LEFT OUTER JOIN people ON people.id = people_todo.people_id
ORDER BY todo_items.name