0

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.

4
  • 1
    Let's get started by writing a query that focuses only on getting the first 10 rows of IDN_CONFIG_RESOURCE without being concerned about the other tables.
    – Rick James
    Jun 21, 2020 at 4:33
  • @RickJames Thanks. I've already tried and seems it's not a solution. ` (select * from IDN_CONFIG_RESOURCE limit 10)AS R` Here, it selects first 10 records from RESOURCES table. But those 10 records may not match the WHERE clause condition. Preventing the result set from returning 10 rows. (Even though there are more than 10 resources in the database which matches this condition.) Jun 21, 2020 at 15:59
  • This does not make sense: 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, the LIMIT is also meaningless.
    – Rick James
    Jun 21, 2020 at 16:58
  • @RickJames, Re: 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 Jun 21, 2020 at 17:22

1 Answer 1

0

Thanks for your inputs. I've decided to follow the below approach.

  1. Create a new DB view aggregating all 3 tables.
CREATE VIEW CONFIG_VIEW AS
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
)
  1. Use below query to search and obtain the matching resource IDs.
SELECT DISTINCT ID
FROM CONFIG_VIEW
WHERE R.TENANT_ID = ?
LIMIT 50 OFFSET 0;
  1. Then, use below query to obtain all the rows of the retrieved RESOURCE IDs from the below query.
SELECT 
  ID,
  TENANT_ID,
  NAME,
  CREATED_TIME,
  LAST_MODIFIED,
  RESOURCE_TYPE,
  DESCRIPTION,
  FILE_ID,
  ATTR_ID,
  ATTR_KEY,
  ATTR_VALUE
FROM
  CONFIG_VIEW
WHERE
  ID IN (
    '0a80998d-b8de-49e6-ad71-20014745a42a', '29fb9c05-1733-4bf5-918f-933f829633c7'
 )

Cheers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.