Lazarus

Programming => Databases => Topic started by: Zvoni on November 24, 2022, 11:11:58 am

Title: [HowTo SQLite] Setting an order within a Group-Concat
Post by: Zvoni on November 24, 2022, 11:11:58 am

In another Forum i helped out an User who was struggling with the following:
He had a table "tbl_employee" and a table "tbl_task", which were connected in an "m:m"-scenario via "tbl_emp_task"

He wanted to query his SQLite-Database to show for all employees their assigned tasks, but the tasks as a single text, preferably in alphabetic order
Example
employee    task
John Smith    task1, task4, task6
Robert Miller    task3, task4, task5

The GROUP_CONCAT-Function of SQLite doesn't have a ORDER BY-Modifier (Compared to other DBMS)
The documenation on sqlite.org even says:

Quote

The order of the concatenated elements is arbitrary.

Though i think, the order is the order the elements are "presented", in case of a base-table my guess is the (hidden) row_id

Therefore i developed a solution as follows:
Setup for test-Database

Code: SQL  [Select][+][-]

1. CREATE TABLE "tbl_employee" (
2.     "ID"    INTEGER,
3.     "FirstName"    TEXT NOT NULL,
4.     "LastName"    TEXT NOT NULL,    
5.     PRIMARY KEY("ID")
6. );
7. CREATE TABLE "tbl_task" (
8.     "ID"    INTEGER,
9.     "Description"    TEXT NOT NULL,
10.     PRIMARY KEY("ID")
11. );
12. CREATE TABLE "tbl_emp_task" (
13.     "emp_id"    INTEGER,
14.     "task_id"    INTEGER,
15.     FOREIGN KEY("task_id") REFERENCES "tbl_task"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
16.     FOREIGN KEY("emp_id") REFERENCES "tbl_employee"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
17.     PRIMARY KEY("emp_id","task_id")
18. );
19. /*Testdata */
20. INSERT INTO tbl_employee ("ID", "FirstName", "LastName") VALUES (1, 'Tom', 'Brown'), (2, 'Robert', 'Miller'), (3, 'John', 'Smith');
21. INSERT INTO tbl_task ("ID", "Description") VALUES (1, 'answering phones'), (2, 'preparing meeting'), (3, 'receiving visitors'), (4, 'Maintaining security'), (5, 'Arranging couriers');
22. INSERT INTO tbl_emp_task ("emp_id", "task_id") VALUES (1,1), (1,2), (1,4), (2,2),(2,3),(2,5);

Note the combined Primary Key for tbl_emp_task consisting of the Foreign Keys.
That way we can make sure that each employee can be assigned a specific task only once


and here the solution:

Code: SQL  [Select][+][-]

1. WITH CTE AS
2.     (SELECT
3.         ET.emp_ID AS EID,
4.         T.Description AS TDESC
5.     FROM tbl_emp_task AS ET
6.     INNER JOIN tbl_task AS T
7.     ON T.ID=ET.task_id
8.     ORDER BY LOWER(T.Description))  /*For Case-Sensitive: ORDER BY T.Description --> No Lower-Function*/
9. SELECT
10.     E.FirstName || ' ' || E.LastName AS FullName,  /*Instead of constructing this within the SELECT we could also use a GENERATED Column*/
11.     COALESCE(C.AssignedTasks, 'No assigned Tasks') AS AssignedTasks
12. FROM tbl_employee AS E
13. LEFT JOIN
14.     (SELECT
15.         EID,
16.         GROUP_CONCAT(TDESC, ', ') AS AssignedTasks
17.     FROM CTE GROUP BY EID) AS C
18. ON C.EID=E.ID
19. GROUP BY E.FirstName || ' ' || E.LastName ORDER BY E.LastName, E.FirstName

The order within the Group-Concat is Case-Insensitive!!
If you want Case-Sensitive order, remove the "Lower"-Function within the CTE.

btw: Using this you could use the returned Column "AssignedTasks" in DB-bound controls (DB-Grid)