[Cartesian product / outer join / self join / compute all pairs] it's possible in grist!

[Antonin_P]

For some time, there was only one thing I was missing in Grist compared to a “real” database: the ability to compute the (cartesian) product of 2 tables.

This can seem rather complex for someone who never used databases, but it’s a very simple and fundamental concept. If you have a column A from some table, and a column B from another table, how do you create automatically the table of all possible combinations ? We usually note this table AxB

Let’s take a practical example. In your company, each employe must give some infomations (like number of hours worked and number of meetings) for each day of the week. In this situation, you have a table “Employes” and a table “Days“. How do you create the table of all combinations, Employes x Days ?

The most straightforward way to do it is to create a new empty table with a column “Employee” which is a reference to the employee table, and a column “Day” which is a reference to the Day table.

But then, you have to fill all the combinations manually !

What if you add an employee ? Or what if you add a day ?

The first solution I implemented to make sure I have all possible combinations was to use the “action button” widget. In the “Employee” table, you can create a button that will create all dates FOR A SPECIFIC EMPLOYEE. You can open a view with the “action button” widget next to the Employee table, and put this use this code in the Action column:

existing_ = Staff_Info.lookupRecords(Employee=$id).Day
# weird Grist bug — not important
existing = existing_.id if existing_ else []
to_add = [r for r in Days.all if r.id not in existing]
actions = [("AddRecord", "Staff_Info", None, {"Employee": $id, "Day": r.id}) for r in to_add]
return {"actions": actions, 
        "button": f"add dates for employee {$Name}", 
        "description": f"this will add {len(actions)} days"
}

You get something like this:

While it is very useful to populate the table automatically, but this is quite cumbersome and not very elegant.

And then, I realized there was a simpler way !

The trick is to use a multiple reference.

1. Go to the “Employee” table
2. Add a new “Possible days” column
3. use the “Reference list” type
4. Use this formula: Days.lookupRecords()
5. Create a new view. Select the “Employee” table and aggregate with “Name” and “Possible_days”

And you get the cartesian product directly:

This has a very important limitation: this is a summary table, and for this reason it cannot be edited. You cannot add informations in a column.

That said, you can use this summary table to do all kinds of things: computations, selections, visualisations … The key is to use LookupRecords on the table of the cross product.

technical note

At a more fundamental point, I think this trick shows that Grist is SQL-complete.

If you look at relational algebra, you learn that you only need a few operators in order to construct any database operation:

• Projection (this is done using the summary table)
• Selection (easy to do with auxiliary columns and filters)
• Renaming (same, easy to do with formulas)
• Cartesian products (with the trick I explained)

For example, joining a table with itself can be done in this way. You just have to be smart about the formula you put into the column you will use in your agregation.

[Facundo_Karanik]

I’d like to add that you can also use formula columns for the aggregate, this allows you to use an id_ column with the formula rec.id, making the cartesian product more robust against things like duplicate names.

Also, I found the guide a bit hard to follow, so I’ll provide screenshots and the schema for my attempt at reproducing this.

Days table:

Employees table:

Employees table id_ column info:

Employees table Possible days column info:

(.all is equivalent to .lookupRecords())

Cartesian product table:

Cartesian product table info:

Finally here’s the document schema (Code View)

import grist
from functions import *       # global uppercase functions
import datetime, math, re     # modules commonly needed in formulas


@grist.UserTable
class Days:
  Day_name = grist.Text()


@grist.UserTable
class Employees:
  Name = grist.Text()

  @grist.formulaType(grist.Int())
  def id_(rec, table):
    return rec.id

  @grist.formulaType(grist.ReferenceList('Days'))
  def Possible_days(rec, table):
    return Days.all

  class _Summary:

    @grist.formulaType(grist.ReferenceList('Employees'))
    def group(rec, table):
      return table.getSummarySourceGroup(rec)

    @grist.formulaType(grist.Int())
    def count(rec, table):
      return len(rec.group)

[Antonin_P]

I will edit my post with the informations you provided, it will be more accessible to the future readers.

Seems like I can’t, it’s quite frustrating …

[nick]

Is it not letting you edit the post?

[nick]

I tried to recover your images, but some seem to have been lost, apologies. Regardless, you should be able to upload multiple images now!