nandi.uk
This repository has no description
semble
/
src
/
modules
/
cards
/
infrastructure
/
repositories
/
DrizzleCollectionQueryRepository.ts
16 kB
514 lines
1import {
2 eq,
3 desc,
4 asc,
5 count,
6 sql,
7 or,
8 ilike,
9 and,
10 inArray,
11} from 'drizzle-orm';
12import { PostgresJsDatabase } from 'drizzle-orm/postgres-js';
13import {
14 ICollectionQueryRepository,
15 CollectionQueryOptions,
16 PaginatedQueryResult,
17 CollectionQueryResultDTO,
18 CollectionSortField,
19 SortOrder,
20 CollectionContainingCardDTO,
21 CollectionForUrlRawDTO,
22 CollectionForUrlQueryOptions,
23 SearchCollectionsOptions,
24 GetOpenCollectionsWithContributorOptions,
25} from '../../domain/ICollectionQueryRepository';
26import { collections, collectionCards } from './schema/collection.sql';
27import { publishedRecords } from './schema/publishedRecord.sql';
28import { cards } from './schema/card.sql';
29import { CollectionMapper } from './mappers/CollectionMapper';
30import { CardTypeEnum } from '../../domain/value-objects/CardType';
31
32export class DrizzleCollectionQueryRepository
33 implements ICollectionQueryRepository
34{
35 constructor(private db: PostgresJsDatabase) {}
36
37 async findByCreator(
38 curatorId: string,
39 options: CollectionQueryOptions,
40 ): Promise<PaginatedQueryResult<CollectionQueryResultDTO>> {
41 try {
42 const { page, limit, sortBy, sortOrder, searchText } = options;
43 const offset = (page - 1) * limit;
44
45 // Build the sort order
46 const orderDirection = sortOrder === SortOrder.ASC ? asc : desc;
47
48 // Build where conditions
49 const whereConditions = [eq(collections.authorId, curatorId)];
50
51 // Add search condition if searchText is provided
52 if (searchText && searchText.trim()) {
53 const searchTerm = `%${searchText.trim()}%`;
54 whereConditions.push(
55 or(
56 ilike(collections.name, searchTerm),
57 ilike(collections.description, searchTerm),
58 )!,
59 );
60 }
61
62 // Simple query: get collections with their stored card counts and URIs
63 const collectionsQuery = this.db
64 .select({
65 id: collections.id,
66 name: collections.name,
67 description: collections.description,
68 accessType: collections.accessType,
69 createdAt: collections.createdAt,
70 updatedAt: collections.updatedAt,
71 authorId: collections.authorId,
72 cardCount: collections.cardCount,
73 uri: publishedRecords.uri,
74 })
75 .from(collections)
76 .leftJoin(
77 publishedRecords,
78 eq(collections.publishedRecordId, publishedRecords.id),
79 )
80 .where(
81 sql`${whereConditions.reduce((acc, condition, index) =>
82 index === 0 ? condition : sql`${acc} AND ${condition}`,
83 )}`,
84 )
85 .orderBy(orderDirection(this.getSortColumn(sortBy)))
86 .limit(limit)
87 .offset(offset);
88
89 const collectionsResult = await collectionsQuery;
90
91 // Get total count with same search conditions
92 const totalCountResult = await this.db
93 .select({ count: count() })
94 .from(collections)
95 .where(
96 sql`${whereConditions.reduce((acc, condition, index) =>
97 index === 0 ? condition : sql`${acc} AND ${condition}`,
98 )}`,
99 );
100
101 const totalCount = totalCountResult[0]?.count || 0;
102 const hasMore = offset + collectionsResult.length < totalCount;
103
104 // Map to DTOs
105 const items = collectionsResult.map((raw) =>
106 CollectionMapper.toQueryResult({
107 id: raw.id,
108 uri: raw.uri,
109 name: raw.name,
110 description: raw.description,
111 accessType: raw.accessType,
112 createdAt: raw.createdAt,
113 updatedAt: raw.updatedAt,
114 authorId: raw.authorId,
115 cardCount: raw.cardCount,
116 }),
117 );
118
119 return {
120 items,
121 totalCount,
122 hasMore,
123 };
124 } catch (error) {
125 console.error('Error in findByCreator:', error);
126 throw error;
127 }
128 }
129
130 async getCollectionsContainingCardForUser(
131 cardId: string,
132 curatorId: string,
133 ): Promise<CollectionContainingCardDTO[]> {
134 try {
135 // Find collections authored by this curator that contain this card
136 const collectionResults = await this.db
137 .select({
138 id: collections.id,
139 name: collections.name,
140 description: collections.description,
141 uri: publishedRecords.uri,
142 })
143 .from(collections)
144 .leftJoin(
145 publishedRecords,
146 eq(collections.publishedRecordId, publishedRecords.id),
147 )
148 .innerJoin(
149 collectionCards,
150 eq(collections.id, collectionCards.collectionId),
151 )
152 .where(
153 and(
154 eq(collections.authorId, curatorId),
155 eq(collectionCards.cardId, cardId),
156 ),
157 )
158 .orderBy(asc(collections.name));
159
160 return collectionResults.map((result) => ({
161 id: result.id,
162 uri: result.uri || undefined,
163 name: result.name,
164 description: result.description || undefined,
165 }));
166 } catch (error) {
167 console.error('Error in getCollectionsContainingCardForUser:', error);
168 throw error;
169 }
170 }
171
172 async getCollectionsWithUrl(
173 url: string,
174 options: CollectionForUrlQueryOptions,
175 ): Promise<PaginatedQueryResult<CollectionForUrlRawDTO>> {
176 try {
177 const { page, limit, sortBy, sortOrder } = options;
178 const offset = (page - 1) * limit;
179
180 // Build the sort order
181 const orderDirection = sortOrder === SortOrder.ASC ? asc : desc;
182
183 // Find all URL cards with this URL
184 const urlCardsQuery = this.db
185 .select({
186 id: cards.id,
187 })
188 .from(cards)
189 .where(and(eq(cards.url, url), eq(cards.type, CardTypeEnum.URL)));
190
191 const urlCardsResult = await urlCardsQuery;
192
193 if (urlCardsResult.length === 0) {
194 return {
195 items: [],
196 totalCount: 0,
197 hasMore: false,
198 };
199 }
200
201 const cardIds = urlCardsResult.map((card) => card.id);
202
203 // Find all collections that contain any of these cards with pagination and sorting
204 const collectionsQuery = this.db
205 .selectDistinct({
206 id: collections.id,
207 name: collections.name,
208 description: collections.description,
209 accessType: collections.accessType,
210 authorId: collections.authorId,
211 uri: publishedRecords.uri,
212 createdAt: collections.createdAt,
213 updatedAt: collections.updatedAt,
214 cardCount: collections.cardCount,
215 })
216 .from(collections)
217 .leftJoin(
218 publishedRecords,
219 eq(collections.publishedRecordId, publishedRecords.id),
220 )
221 .innerJoin(
222 collectionCards,
223 eq(collections.id, collectionCards.collectionId),
224 )
225 .where(inArray(collectionCards.cardId, cardIds))
226 .orderBy(orderDirection(this.getSortColumn(sortBy)))
227 .limit(limit)
228 .offset(offset);
229
230 const collectionsResult = await collectionsQuery;
231
232 // Get total count of distinct collections
233 const totalCountQuery = this.db
234 .selectDistinct({
235 id: collections.id,
236 })
237 .from(collections)
238 .innerJoin(
239 collectionCards,
240 eq(collections.id, collectionCards.collectionId),
241 )
242 .where(inArray(collectionCards.cardId, cardIds));
243
244 const totalCountResult = await totalCountQuery;
245 const totalCount = totalCountResult.length;
246 const hasMore = offset + collectionsResult.length < totalCount;
247
248 const items = collectionsResult.map((result) => ({
249 id: result.id,
250 uri: result.uri || undefined,
251 name: result.name,
252 description: result.description || undefined,
253 accessType: result.accessType,
254 authorId: result.authorId,
255 }));
256
257 return {
258 items,
259 totalCount,
260 hasMore,
261 };
262 } catch (error) {
263 console.error('Error in getCollectionsWithUrl:', error);
264 throw error;
265 }
266 }
267
268 async searchCollections(
269 options: SearchCollectionsOptions,
270 ): Promise<PaginatedQueryResult<CollectionQueryResultDTO>> {
271 try {
272 const {
273 page,
274 limit,
275 sortBy,
276 sortOrder,
277 searchText,
278 authorId,
279 accessType,
280 } = options;
281 const offset = (page - 1) * limit;
282
283 // Build the sort order
284 const orderDirection = sortOrder === SortOrder.ASC ? asc : desc;
285
286 // Build where conditions
287 const whereConditions = [];
288
289 // Add author filter if provided
290 if (authorId) {
291 whereConditions.push(eq(collections.authorId, authorId));
292 }
293
294 // Add access type filter if provided
295 if (accessType) {
296 whereConditions.push(eq(collections.accessType, accessType));
297 }
298
299 // Add tokenized search condition if searchText is provided
300 if (searchText && searchText.trim()) {
301 const searchWords = searchText.trim().split(/\s+/);
302 const searchConditions = searchWords.map(
303 (word) =>
304 or(
305 ilike(collections.name, `%${word}%`),
306 ilike(collections.description, `%${word}%`),
307 )!,
308 );
309
310 // All words must be found (AND logic)
311 whereConditions.push(and(...searchConditions)!);
312 }
313
314 // Build the where clause
315 const whereClause =
316 whereConditions.length > 0
317 ? sql`${whereConditions.reduce((acc, condition, index) =>
318 index === 0 ? condition : sql`${acc} AND ${condition}`,
319 )}`
320 : sql`1=1`; // Always true when no conditions
321
322 // Query collections with their stored card counts and URIs
323 const collectionsQuery = this.db
324 .select({
325 id: collections.id,
326 name: collections.name,
327 description: collections.description,
328 accessType: collections.accessType,
329 createdAt: collections.createdAt,
330 updatedAt: collections.updatedAt,
331 authorId: collections.authorId,
332 cardCount: collections.cardCount,
333 uri: publishedRecords.uri,
334 })
335 .from(collections)
336 .leftJoin(
337 publishedRecords,
338 eq(collections.publishedRecordId, publishedRecords.id),
339 )
340 .where(whereClause)
341 .orderBy(orderDirection(this.getSortColumn(sortBy)))
342 .limit(limit)
343 .offset(offset);
344
345 const collectionsResult = await collectionsQuery;
346
347 // Get total count with same search conditions
348 const totalCountResult = await this.db
349 .select({ count: count() })
350 .from(collections)
351 .where(whereClause);
352
353 const totalCount = totalCountResult[0]?.count || 0;
354 const hasMore = offset + collectionsResult.length < totalCount;
355
356 // Map to DTOs
357 const items = collectionsResult.map((raw) =>
358 CollectionMapper.toQueryResult({
359 id: raw.id,
360 uri: raw.uri,
361 name: raw.name,
362 description: raw.description,
363 accessType: raw.accessType,
364 createdAt: raw.createdAt,
365 updatedAt: raw.updatedAt,
366 authorId: raw.authorId,
367 cardCount: raw.cardCount,
368 }),
369 );
370
371 return {
372 items,
373 totalCount,
374 hasMore,
375 };
376 } catch (error) {
377 console.error('Error in searchCollections:', error);
378 throw error;
379 }
380 }
381
382 async getOpenCollectionsWithContributor(
383 options: GetOpenCollectionsWithContributorOptions,
384 ): Promise<PaginatedQueryResult<CollectionQueryResultDTO>> {
385 try {
386 const { contributorId, page, limit, sortBy, sortOrder } = options;
387 const offset = (page - 1) * limit;
388
389 // Build the sort order
390 const orderDirection = sortOrder === SortOrder.ASC ? asc : desc;
391
392 // Get collections where:
393 // 1. User has added cards (via collection_cards.addedBy)
394 // 2. User is NOT the author (collections.authorId != contributorId)
395 // 3. Collection is OPEN (collections.accessType = 'OPEN')
396 // Sort by most recent contribution (addedAt DESC) as primary sort
397
398 const collectionsQuery = this.db
399 .selectDistinct({
400 id: collections.id,
401 name: collections.name,
402 description: collections.description,
403 accessType: collections.accessType,
404 createdAt: collections.createdAt,
405 updatedAt: collections.updatedAt,
406 authorId: collections.authorId,
407 cardCount: collections.cardCount,
408 uri: publishedRecords.uri,
409 // Get the most recent contribution date for sorting
410 lastContributionDate: sql<Date>`MAX(${collectionCards.addedAt})`.as(
411 'last_contribution_date',
412 ),
413 })
414 .from(collections)
415 .leftJoin(
416 publishedRecords,
417 eq(collections.publishedRecordId, publishedRecords.id),
418 )
419 .innerJoin(
420 collectionCards,
421 eq(collections.id, collectionCards.collectionId),
422 )
423 .where(
424 and(
425 eq(collectionCards.addedBy, contributorId),
426 sql`${collections.authorId} != ${contributorId}`, // Not the author
427 eq(collections.accessType, 'OPEN'),
428 ),
429 )
430 .groupBy(
431 collections.id,
432 collections.name,
433 collections.description,
434 collections.accessType,
435 collections.createdAt,
436 collections.updatedAt,
437 collections.authorId,
438 collections.cardCount,
439 publishedRecords.uri,
440 )
441 .orderBy(
442 // Primary sort: by most recent contribution (addedAt DESC)
443 desc(sql`MAX(${collectionCards.addedAt})`),
444 // Secondary sort: by the specified field
445 orderDirection(this.getSortColumn(sortBy)),
446 )
447 .limit(limit)
448 .offset(offset);
449
450 const collectionsResult = await collectionsQuery;
451
452 // Get total count with same conditions
453 const totalCountQuery = this.db
454 .selectDistinct({
455 id: collections.id,
456 })
457 .from(collections)
458 .innerJoin(
459 collectionCards,
460 eq(collections.id, collectionCards.collectionId),
461 )
462 .where(
463 and(
464 eq(collectionCards.addedBy, contributorId),
465 sql`${collections.authorId} != ${contributorId}`,
466 eq(collections.accessType, 'OPEN'),
467 ),
468 );
469
470 const totalCountResult = await totalCountQuery;
471 const totalCount = totalCountResult.length;
472 const hasMore = offset + collectionsResult.length < totalCount;
473
474 // Map to DTOs
475 const items = collectionsResult.map((raw) =>
476 CollectionMapper.toQueryResult({
477 id: raw.id,
478 uri: raw.uri,
479 name: raw.name,
480 description: raw.description,
481 accessType: raw.accessType,
482 createdAt: raw.createdAt,
483 updatedAt: raw.updatedAt,
484 authorId: raw.authorId,
485 cardCount: raw.cardCount,
486 }),
487 );
488
489 return {
490 items,
491 totalCount,
492 hasMore,
493 };
494 } catch (error) {
495 console.error('Error in getOpenCollectionsWithContributor:', error);
496 throw error;
497 }
498 }
499
500 private getSortColumn(sortBy: CollectionSortField) {
501 switch (sortBy) {
502 case CollectionSortField.NAME:
503 return collections.name;
504 case CollectionSortField.CREATED_AT:
505 return collections.createdAt;
506 case CollectionSortField.UPDATED_AT:
507 return collections.updatedAt;
508 case CollectionSortField.CARD_COUNT:
509 return collections.cardCount;
510 default:
511 return collections.name;
512 }
513 }
514}