Queries

In Plato, you query tables not with SQL but with visual controls for filtering, sorting, hiding, and joining.

To gather all the data needed for a workflow, you must often join together two or more tables. To process appointments, for example, a medical provider may need to join the appointments table with a table containing data on the users scheduling the appointments. In Plato, they can join these tables by expanding a foreign key column.

A foreign key is a special type of column that links a child table to a parent table. In Plato, the child table offers an Expand control that lets you expand foreign keys. When a foreign key is expanded, all the columns from the parent table are joined into the child table, creating new columns with names such as users.name or users.address.

Note: Plato does not yet support deeply nested joins traversing multiple foreign keys.

Expanding foreign keys is a great way for our medical provider above to view user information alongside each appointment, but what if they also want to see all the appointments for a given user? One way is to filter the appointments table by the user’s ID, but this such a common need that Plato offers a convenient builtin feature: backlinks.

For every foreign key, Plato places a backlink column on the parent table pointed to by the key. So in our example above, the users table will contain a backlink, “appointments”, that the medical provider can click to view all appointments for that user. It’s a great way to navigate one-to-many and many-to-many relationships in Plato.

In Plato, queries are stored as views. You can create, delete, and select views in the view selector at the top left of your table. Every table initially has a single view named “Default”.

Each view is visible to all members of your workspace.

A common pattern in Plato is to construct a query that presents data in a way that’s most convenient to another user, such as a support agent or operator. You may choose to construct this query, save it as a view, then share it, but this exposes the low-level query details to the other user who a) doesn’t care about those details and b) may want further query the data without disrupting the original view. This is where virtual tables come in.

A virtual table lets you present the results of a query as if it were its own table. It allows you to share a new view on your data without exposing the details of how it was constructed.

To create a virtual table: