I have 3 tables as below.
mysql> describe IDN_CONFIG_RESOURCE;
+---------------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+-------------------+-------+
| ID | varchar(255) | NO | PRI | NULL | |
| TENANT_ID | int(11) | NO | | NULL | |
| NAME | varchar(255) | NO | MUL | NULL | |
| CREATED_TIME | timestamp | NO | | CURRENT_TIMESTAMP | |
| LAST_MODIFIED | timestamp | NO | | CURRENT_TIMESTAMP | |
| HAS_FILE | tinyint(1) | NO | | NULL | |
| HAS_ATTRIBUTE | tinyint(1) | NO | | NULL | |
| TYPE_ID | varchar(255) | NO | MUL | NULL | |
+---------------+--------------+------+-----+-------------------+-------+
8 rows in set (0.09 sec)
mysql> describe IDN_CONFIG_TYPE;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| ID | varchar(255) | NO | PRI | NULL | |
| NAME | varchar(255) | NO | UNI | NULL | |
| DESCRIPTION | varchar(1023) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> describe IDN_CONFIG_ATTRIBUTE;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| ID | varchar(255) | NO | PRI | NULL | |
| RESOURCE_ID | varchar(255) | NO | MUL | NULL | |
| ATTR_KEY | varchar(1023) | NO | | NULL | |
| ATTR_VALUE | varchar(1023) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
My REST API response is as below. It returns an array of resource objects. Each resource may contain multiple attributes(Eg. 'from', 'to' etc.) as key value pairs stored in the IDN_CONFIG_ATTRIBUTE
table. And linked to a resource by RESOURCE_ID
column.
{
"resources":[
{
"id":"e0dd8199-d45f-49c8-a4ca-6b863d4bc157",
"tenantDomain":"carbon.super",
"tenantDomain":"smtp",
"resourceType":"e-mail",
"lastModified":"2019-01-24T10:15:51Z",
"created":"2019-01-24T10:15:51Z",
"files":[
{
"value":"/resource/file/489684c4-77ee-47e0-ad74-38ccb0762672",
"id":"489684c4-77ee-47e0-ad74-38ccb0762672"
}
],
"attributes":[
{
"key":"from",
"value":"hey",
"id":"de983448-8700-4fde-8a4b-f0becdb7ebe1"
},
{
"key":"to",
"value":"3wso2.com",
"id":"4b4c5c85-dbed-46cb-b576-e15de4acc76a"
}
],
"hasFile":true,
"hasAttribute":true
},
{
"id":"4f731dc0-1581-4f00-b904-3d42bebdc9b6",
"tenantDomain":"carbon.super",
"resourceName":"pop3",
"resourceType":"e-mail",
"lastModified":"2019-01-24T10:15:51Z",
"created":"2019-01-24T10:15:51Z",
"files":[
],
"attributes":[
{
"key":"server",
"value":"pop3.abc.com",
"id":"6f7aa9ee-2bca-4813-8405-9a5ea8728e2f"
},
{
"key":"to",
"value":"wso2.com",
"id":"7569948c-9aed-451c-af4c-64c943e85c91"
}
],
"hasFile":false,
"hasAttribute":true
}
]
}
Issue
I'm going to call the API and retrieve all the resources which matches the search condition as below.
filter=tenantDomain eq carbon.super&limit=10&offset=0&sortBy=tenantId&order=ASC
So the generated query in the backend will be like below.
SELECT
R.ID,
R.TENANT_ID,
R.NAME,
R.CREATED_TIME,
R.LAST_MODIFIED,
T.NAME AS RESOURCE_TYPE,
T.DESCRIPTION AS DESCRIPTION,
F.ID AS FILE_ID,
A.ID AS ATTR_ID,
A.ATTR_KEY AS ATTR_KEY,
A.ATTR_VALUE AS ATTR_VALUE
FROM
IDN_CONFIG_RESOURCE AS R
INNER JOIN IDN_CONFIG_TYPE AS T ON R.TYPE_ID = T.ID
LEFT JOIN IDN_CONFIG_ATTRIBUTE AS A ON (
R.HAS_ATTRIBUTE = TRUE
AND A.RESOURCE_ID = R.ID
)
LEFT JOIN IDN_CONFIG_FILE AS F ON (
R.HAS_FILE = TRUE
AND F.RESOURCE_ID = R.ID
)
WHERE
R.TENANT_ID = ?
ORDER BY R.TENANT_ID ASC
LIMIT 10 OFFSET 0
As you can see from both the query and the API filter condition, I'm trying to implement pagination and sorting for this search. I'm expecting to impose the pagination in the RESOURCE level. So that my API response would contain 10 resources only. But this query doesn't have any understanding of the RESOURCE level. It just throttles out the result tuples. (Which may belong to 1-10 resources)
I don't see any way to implement this. Any suggestions? Or let me know if the database design is wrong at the first place? P.S. The where condition is a dynamically generated one. It may have conditions to search in either column of either table. Not much flexibility on that though
Thanks in advance.
IDN_CONFIG_RESOURCE
without being concerned about the other tables.WHERE R.TENANT_ID = ? ORDER BY R.TENANT_ID ASC LIMIT 10
-- It lists to a single id, so there is no need for the ordering. And, unless there are multiple rows with that id, theLIMIT
is also meaningless.ORDER BY R.TENANT_ID
yes, I'm wrong there. Will fix that. My task is to introduce pagination and sorting for this API. Sorting attribute is decided by the user. I'll consider your point when fixing that. Re :LIMIT
, yes, there can be multiple rows matching the WHERE condition. That's where I'm struggling to paginate