The feature I mentioned in “SQL: joins and duplicates” is now implemented in Glom 1.3.3. It’s just a matter of choosing relationships from a tree rather than just a list, though it’s only 2 levels deep for now to keep it simple.
So if there are, for instance, Invoices with related Invoice Lines records, which refer to Products, then you could look at a Product details screen and see all the Invoices that use the Product (via their Invoice Lines records). If I added a 3rd level of child relationships then you could even see all the Customers (used by the Invoice table) that had ever been invoiced for the product.
Here’s a screenshot of the UI for the Licenses, Packages, Package Scans example:
The UI isn’t perfect. I don’t like that it’s enabled via a checkbox but I think the tree would be confusing if it was default. Trees in GtkComboBox widgets are also rendered as these confusing menus, but it could be replaced by a popup GtkTreeView some day. But I feel very satisfied that I’ve made it easy to do something that’s usually difficult, with only minor UI changes. Many thanks to Jerry Haltom for showing me how this could be done.
The SQL that’s generated is much the same as for regular related records (SELECT related_table.field1, related_table.field2 FROM related_table WHERE related_table_field3 = 123), but with an extra JOIN … AS … ON clause to link to the intermediate table, a slightly changed WHERE clause (to refer to that intermediate join), and a GROUP BY on the related table’s primary key to ensure that we get only one row for each related record. A sub-select query might be more efficient, but this allows me to reuse the existing code, and lets the user think in terms of the target related table rather than an intermediate one.
So using Glom’s –debug_sql now shows yet more complex SQL that you wouldn’t want to write yourself.