Let's say I have a table of events:
id bigint NOT NULL PRIMARY KEY,
tenant_id CHARACTER VARYING(50) NOT NULL,
name CHARACTER VARYING(100) NOT NULL,
code CHARACTER VARYING(50) NOT NULL,
UNIQUE (code, tenant_id)
Problem Description + Example Data:
I want to retrieve a list of events, but there are some complications. Some example data:
| tenant_id | name | code |
|---|---|---|
| USA | user signup | user-signup |
| Canada | user signup | user-signup |
| default | user signup | user-signup |
| Canada | user account deletion | user-account-deletion |
| Spain | user account deletion | user-account-deletion |
As you can see, the same event code can be present in multiple tenants. For some special events, they will also have an entry in a special tenant named "default".
I want my search to retrieve all columns in the event table (some were omitted for brevity), filtered by tenant_id, but I only want distinct values of event code, and want to include "default" tenant_id if not already present in the requested tenant.
Example requests and desired responses
For example, if I requested my search with tenant_id of "Canada", I want the results to be:
| tenant_id | name | code |
|---|---|---|
| Canada | user signup | user-signup |
| Canada | user account deletion | user-account-deletion |
Note that user-signup should not appear twice, even though it is present in both Canada and default tenants.
Then if I want to search with tenant_id of "Spain", I want the results to be:
| tenant_id | name | code |
|---|---|---|
| default | user signup | user-signup |
| Spain | user account deletion | user-account-deletion |
Note that because user-signup is not present in Spain tenant, but is present in default tenant, it shows up.
If I search with a random tenant, I want all the events in default tenant to be present:
| tenant_id | name | code |
|---|---|---|
| default | user signup | user-signup |
What I've tried:
I'm not too familiar with SQL, but have tried some basic ideas. Apologies for confusions, I'm using HQL.
Function signature:
searchEvents(String tenantId);
Using WHERE IN:
SELECT new Event(e.id, e.tenantId, e.name, e.code)
FROM Event e
WHERE e.tenantId IN (:tenantId, 'default')
This works to get events in the requested tenant or default tenant, but contains duplicate event codes if it is in the specified tenant and default tenant.
Using GROUP BY code:
SELECT new Event(e.id, e.tenantId, e.name, e.code)
FROM Event e
WHERE e.tenantId IN (:tenantId, 'default')
GROUP BY e.code
Errors out. ERROR: column "event0_.id" must appear in the GROUP BY clause or be used in an aggregate function.
This question I found seems similar, but instead of getting the "max" value of the tenant column and grouping by code, I want a maximum that I define where the max value is provided tenant, and next value would be "default" tenant. SQL select only rows with max value on a column
Another option is possibly this to define my own ordering SQL Select only rows with custom Max Value on a Column but I didn't want to go down that rabbit hole if there is something simpler.
