7.19 Get Cartesian product with filtering condition
Calculate Cartesian product of two associated tables and perform filtering on the result.
Find the two types of sandwiches that use the most similar ingredients according to the Sandwich table and Ingredient table.
Sandwich:
| ID | Name | Price |
|---|---|---|
| 1 | BLT | 5.5 |
| 2 | Reuben | 7.0 |
| 3 | Grilled Cheese | 3.75 |
Ingredient:
| ID | Ingredient |
|---|---|
| 1 | bacon |
| 1 | lettuce |
| 1 | tomato |
| … | … |
SPL offers xjoin() function to calculate Cartesian product. Note that records of the result set of SPL Cartesian product is combined records from two tables rather than simply joining all fields.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select i.ID ID, i.Ingredient Ingredient, s.Name Name from Sandwich s, Ingredient i where s.ID=i.ID order by ID”) |
| 3 | =A2.group@o(ID;Name,~.(Ingredient):Collection) |
| 4 | =xjoin(A3:A;A3:B,A.ID<ID) |
| 5 | =A4.new((A.Collection ^ B.Collection).len():Count, A.Name:Name1, B.Name:Name2).sort@z(Count) |
A1 Connect to the database.
A2 Query records by associating Sandwich table and Ingredient table.
A3 Use group@o() function to merge and group A2’s records by ID, and store ingredients of each type of sandwich in Collection field.
A4 Use xjoin() function to calculate Cartesian product of A3’s result set and itself, and get collections containing different IDs.
A5 Find the number of same ingredients used in two types of sandwiches and arrange records by the count in descending order.
Execution result:
| Count | Name1 | Name2 |
|---|---|---|
| 1 | Reuben | Grilled Cheese |
| 0 | BLT | Reuben |
| 0 | BLT | Grilled Cheese |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
