Custom literals in OUTER JOIN criteria

Feedback and Requests
1

Hello,

I have the following shcema :

product (id, name, price)
property (id, name, value, product_id)

Products may have, or not, some properties.

If I want to study "Material" and "Color" properties, the corresponding SQL is :

SELECT ALL p.id, p.name, p.price, p_material.value AS material, p_color.value AS color
FROM product AS p
LEFT OUTER JOIN property AS p_material ON p.id = p_material.product_id AND p_material.name = 'Material'
LEFT OUTER JOIN property AS p_color ON p.id = p_color.product_id AND p_color.name = 'Color'

How can I make this query with the Metabase editor ?

We can't put directly literals in ON criterias (nice feature incoming ?), so I made custom expressions.
I can't make a custom expression like = "Material" or = concat("Material"),
so I named a custom column Literal "Material" containing = concat("Material", "")
and another one named Literal "Color" containing = concat("Color", "").
These custom expressions can be inserted in OUTER JOIN's ON criterias.

That seems tricky. Is it the normal way to do that ?

Thx !

2

I assume you are referring to the GUI

Have you tried adding a custom column in product something like p_custom = 'Material' ... then join this custom column as part of your GUI join process and same for color?

3

Your problem is due to nulls. Standard SQL stuff rather than Metabase.
Try:
coalesce( p_color.value, '') AS colour

I've corrected the spelling for you too :laughing:

4

Yes, I talk about the graphical query editor (not the SQL one)
I try to reproduce the OUTER JOIN SQL query I wrote higher with it
I believe that what you suggest is what I do actually

5

Sorry, my question is certainly unclear. I haven't problem with the SQL, but with the graphical query editor with which I would like to reproduce the OUTER JOIN criterias.

"colour" and not "color", thx :slight_smile:

1 Like