kaneo (minimalist kanban) fork to experiment adding a tangled integration
github.com/usekaneo/kaneo
1import {
2 and,
3 asc,
4 desc,
5 eq,
6 gte,
7 inArray,
8 lte,
9 type SQL,
10 sql,
11} from "drizzle-orm";
12import { HTTPException } from "hono/http-exception";
13import db from "../../database";
14import {
15 columnTable,
16 externalLinkTable,
17 labelTable,
18 projectTable,
19 taskTable,
20 userTable,
21} from "../../database/schema";
22
23type GetTasksOptions = {
24 assigneeId?: string;
25 dueAfter?: string;
26 dueBefore?: string;
27 limit?: number;
28 page?: number;
29 priority?: string;
30 sortBy?:
31 | "createdAt"
32 | "priority"
33 | "dueDate"
34 | "position"
35 | "title"
36 | "number";
37 sortOrder?: "asc" | "desc";
38 status?: string;
39};
40
41const priorityCaseExpr = sql<number>`CASE
42 WHEN ${taskTable.priority} = 'urgent' THEN 4
43 WHEN ${taskTable.priority} = 'high' THEN 3
44 WHEN ${taskTable.priority} = 'medium' THEN 2
45 WHEN ${taskTable.priority} = 'low' THEN 1
46 ELSE 0
47END`;
48
49function buildOrderBy(
50 sortBy: GetTasksOptions["sortBy"],
51 sortOrder: GetTasksOptions["sortOrder"],
52): SQL {
53 const direction = sortOrder === "desc" ? desc : asc;
54
55 switch (sortBy) {
56 case "createdAt":
57 return direction(taskTable.createdAt);
58 case "priority":
59 return direction(priorityCaseExpr);
60 case "dueDate":
61 return direction(taskTable.dueDate);
62 case "title":
63 return direction(taskTable.title);
64 case "number":
65 return direction(taskTable.number);
66 default:
67 return direction(taskTable.position);
68 }
69}
70
71async function getTasks(projectId: string, options: GetTasksOptions = {}) {
72 const project = await db.query.projectTable.findFirst({
73 where: eq(projectTable.id, projectId),
74 });
75
76 if (!project) {
77 throw new HTTPException(404, {
78 message: "Project not found",
79 });
80 }
81
82 const conditions = [eq(taskTable.projectId, projectId)];
83
84 if (options.status) {
85 conditions.push(eq(taskTable.status, options.status));
86 }
87
88 if (options.priority) {
89 conditions.push(eq(taskTable.priority, options.priority));
90 }
91
92 if (options.assigneeId) {
93 conditions.push(eq(taskTable.userId, options.assigneeId));
94 }
95
96 if (options.dueBefore) {
97 conditions.push(lte(taskTable.dueDate, new Date(options.dueBefore)));
98 }
99
100 if (options.dueAfter) {
101 conditions.push(gte(taskTable.dueDate, new Date(options.dueAfter)));
102 }
103
104 const whereClause = and(...conditions);
105 const usePagination = options.page != null || options.limit != null;
106 const page = options.page && options.page > 0 ? options.page : 1;
107 const pageSize =
108 options.limit && options.limit > 0 ? Math.min(options.limit, 100) : 50;
109 const offset = (page - 1) * pageSize;
110
111 const orderByClause = buildOrderBy(
112 options.sortBy ?? "position",
113 options.sortOrder ?? "asc",
114 );
115
116 const [taskCount] = await db
117 .select({ count: sql<number>`count(*)` })
118 .from(taskTable)
119 .where(whereClause);
120
121 const total = Number(taskCount?.count ?? 0);
122
123 const taskSelection = {
124 id: taskTable.id,
125 title: taskTable.title,
126 number: taskTable.number,
127 description: taskTable.description,
128 status: taskTable.status,
129 priority: taskTable.priority,
130 startDate: taskTable.startDate,
131 dueDate: taskTable.dueDate,
132 position: taskTable.position,
133 createdAt: taskTable.createdAt,
134 userId: taskTable.userId,
135 assigneeName: userTable.name,
136 assigneeId: userTable.id,
137 assigneeImage: userTable.image,
138 projectId: taskTable.projectId,
139 };
140
141 const query = db
142 .select(taskSelection)
143 .from(taskTable)
144 .leftJoin(userTable, eq(taskTable.userId, userTable.id))
145 .leftJoin(projectTable, eq(taskTable.projectId, projectTable.id))
146 .where(whereClause)
147 .orderBy(orderByClause);
148
149 const paginatedTasks = usePagination
150 ? await query.limit(pageSize).offset(offset)
151 : await query;
152
153 const taskIds = paginatedTasks.map((task) => task.id);
154
155 const labelsData =
156 taskIds.length > 0
157 ? await db
158 .select({
159 id: labelTable.id,
160 name: labelTable.name,
161 color: labelTable.color,
162 taskId: labelTable.taskId,
163 })
164 .from(labelTable)
165 .where(inArray(labelTable.taskId, taskIds))
166 : [];
167
168 const externalLinksData =
169 taskIds.length > 0
170 ? await db
171 .select()
172 .from(externalLinkTable)
173 .where(inArray(externalLinkTable.taskId, taskIds))
174 : [];
175
176 const taskLabelsMap = new Map<
177 string,
178 Array<{ id: string; name: string; color: string }>
179 >();
180 for (const label of labelsData) {
181 if (label.taskId) {
182 if (!taskLabelsMap.has(label.taskId)) {
183 taskLabelsMap.set(label.taskId, []);
184 }
185 taskLabelsMap.get(label.taskId)?.push({
186 id: label.id,
187 name: label.name,
188 color: label.color,
189 });
190 }
191 }
192
193 const taskExternalLinksMap = new Map<
194 string,
195 Array<{
196 id: string;
197 taskId: string;
198 integrationId: string;
199 resourceType: string;
200 externalId: string;
201 url: string;
202 title: string | null;
203 metadata: Record<string, unknown> | null;
204 }>
205 >();
206 for (const externalLink of externalLinksData) {
207 if (!taskExternalLinksMap.has(externalLink.taskId)) {
208 taskExternalLinksMap.set(externalLink.taskId, []);
209 }
210 taskExternalLinksMap.get(externalLink.taskId)?.push({
211 ...externalLink,
212 metadata: externalLink.metadata
213 ? JSON.parse(externalLink.metadata)
214 : null,
215 });
216 }
217
218 const projectColumns = await db
219 .select()
220 .from(columnTable)
221 .where(eq(columnTable.projectId, projectId))
222 .orderBy(asc(columnTable.position));
223
224 const columns = projectColumns.map((column) => ({
225 id: column.slug,
226 name: column.name,
227 isFinal: column.isFinal,
228 tasks: paginatedTasks
229 .filter((task) => task.status === column.slug)
230 .map((task) => ({
231 ...task,
232 labels: taskLabelsMap.get(task.id) || [],
233 externalLinks: taskExternalLinksMap.get(task.id) || [],
234 })),
235 }));
236
237 const archivedTasks = paginatedTasks
238 .filter((task) => task.status === "archived")
239 .map((task) => ({
240 ...task,
241 labels: taskLabelsMap.get(task.id) || [],
242 externalLinks: taskExternalLinksMap.get(task.id) || [],
243 }));
244
245 const plannedTasks = paginatedTasks
246 .filter((task) => task.status === "planned")
247 .map((task) => ({
248 ...task,
249 labels: taskLabelsMap.get(task.id) || [],
250 externalLinks: taskExternalLinksMap.get(task.id) || [],
251 }));
252
253 return {
254 data: {
255 id: project.id,
256 name: project.name,
257 slug: project.slug,
258 icon: project.icon,
259 description: project.description,
260 isPublic: project.isPublic,
261 workspaceId: project.workspaceId,
262 columns,
263 archivedTasks,
264 plannedTasks,
265 },
266 pagination: usePagination
267 ? {
268 total,
269 page,
270 pageSize,
271 totalPages: Math.max(1, Math.ceil(total / pageSize)),
272 }
273 : {
274 total,
275 page: 1,
276 pageSize: total,
277 totalPages: 1,
278 },
279 };
280}
281
282export default getTasks;