How to JOIN using LIKE '<column>%'

Question

I have a list of items, each of which has a category code:

id | name       | category_code
---+------------+--------------
11 | 'Cookies'  | '43040'
53 | 'Potatoes' | '43100'

These category codes work hierarchically, for example the beginning 43 might mean food, the ending 04 pastry or the ending 10 root vegetables.

I also have a table of the names of the codes like so:

code    | name
--------+------------------------------------
'4304'  | 'Food -> Pastery'
'43040' | 'Food -> Pastery -> Cookie'
'43100' | 'Food -> Root Vegetables -> Potato'

These codes and the system is not designed by me, so I can't influence it. It is what it is.

Now the problem is, I have a mapping of keywords to the codes, such as:

id | key       | code
---+-----------+--------
22 | 'cookie'  | '43040'
23 | 'pastery' | '4304'
24 | 'cake'    | '43042'

And I need to perform search based on these keywords. What I tried so far:

SELECT item.id, item.name, category.code, category.name
FROM item
LEFT JOIN category ON category.code = item.category_code
LEFT JOIN keyword ON keyword.code = category.code
WHERE LOWER(keyword.key) = ANY(ARRAY['pastery', 'root']);

But the problem appears on the middle like JOIN category ON category.code = item.category_code because items always have the full code of the length five, but there are main categories for codes like 4304 and keywords matching to them.

I would need to perform a join with LIKE and %, ideally something like this:

SELECT item.id, item.name, category.code, category.name
FROM item
LEFT JOIN category ON (category.code + '%') LIKE item.category_code
LEFT JOIN keyword ON keyword.code = category.code
WHERE LOWER(keyword.key) = ANY(ARRAY['pastery', 'root']);

But obviously (category.code + '%') is not supported. How can I achieve this goal in other ways?

Answer 1

I think this is what you want. The like is in the wrong order:

SELECT i.id, i.name, c.code, c.name
FROM item i LEFT JOIN
     category c
     ON  i.category_code like c.code || '%' LEFT JOIN
     keyword k
     ON k.code = c.code
WHERE LOWER(keyword.key) = ANY(ARRAY['pastery', 'root']);