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