This repository has no description
Something went wrong. Try again.
20 kB · 633 lines
TypeScript
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634import { eq, desc, asc, count, sql, or, ilike, and, inArray,} from 'drizzle-orm';import { PostgresJsDatabase } from 'drizzle-orm/postgres-js';import { ICollectionQueryRepository, CollectionQueryOptions, PaginatedQueryResult, CollectionQueryResultDTO, CollectionSortField, SortOrder, CollectionContainingCardDTO, CollectionForUrlRawDTO, CollectionForUrlQueryOptions, SearchCollectionsOptions, GetOpenCollectionsWithContributorOptions, CollectionContributorDTO,} from '../../domain/ICollectionQueryRepository';import { collections, collectionCards } from './schema/collection.sql';import { publishedRecords } from './schema/publishedRecord.sql';import { cards } from './schema/card.sql';import { CollectionMapper } from './mappers/CollectionMapper';import { CardTypeEnum } from '../../domain/value-objects/CardType';
export class DrizzleCollectionQueryRepository implements ICollectionQueryRepository{ constructor(private db: PostgresJsDatabase) {}
async findByCreator( curatorId: string, options: CollectionQueryOptions, ): Promise<PaginatedQueryResult<CollectionQueryResultDTO>> { try { const { page, limit, sortBy, sortOrder, searchText } = options; const offset = (page - 1) * limit;
// Build the sort order const orderDirection = sortOrder === SortOrder.ASC ? asc : desc;
// Build where conditions const whereConditions = [eq(collections.authorId, curatorId)];
// Add search condition if searchText is provided if (searchText && searchText.trim()) { const searchTerm = `%${searchText.trim()}%`; whereConditions.push( or( ilike(collections.name, searchTerm), ilike(collections.description, searchTerm), )!, ); }
// Simple query: get collections with their stored card counts and URIs const collectionsQuery = this.db .select({ id: collections.id, name: collections.name, description: collections.description, accessType: collections.accessType, createdAt: collections.createdAt, updatedAt: collections.updatedAt, authorId: collections.authorId, cardCount: collections.cardCount, uri: publishedRecords.uri, }) .from(collections) .leftJoin( publishedRecords, eq(collections.publishedRecordId, publishedRecords.id), ) .where( sql`${whereConditions.reduce((acc, condition, index) => index === 0 ? condition : sql`${acc} AND ${condition}`, )}`, ) .orderBy(orderDirection(this.getSortColumn(sortBy))) .limit(limit) .offset(offset);
const collectionsResult = await collectionsQuery;
// Get total count with same search conditions const totalCountResult = await this.db .select({ count: count() }) .from(collections) .where( sql`${whereConditions.reduce((acc, condition, index) => index === 0 ? condition : sql`${acc} AND ${condition}`, )}`, );
const totalCount = totalCountResult[0]?.count || 0; const hasMore = offset + collectionsResult.length < totalCount;
// Map to DTOs const items = collectionsResult.map((raw) => CollectionMapper.toQueryResult({ id: raw.id, uri: raw.uri, name: raw.name, description: raw.description, accessType: raw.accessType, createdAt: raw.createdAt, updatedAt: raw.updatedAt, authorId: raw.authorId, cardCount: raw.cardCount, }), );
return { items, totalCount, hasMore, }; } catch (error) { console.error('Error in findByCreator:', error); throw error; } }
async getCollectionsContainingCardForUser( cardId: string, curatorId: string, ): Promise<CollectionContainingCardDTO[]> { try { // Find all collections where this user added this card // Includes collections owned by the user AND open collections where they added the card const collectionResults = await this.db .select({ id: collections.id, name: collections.name, description: collections.description, uri: publishedRecords.uri, }) .from(collectionCards) .innerJoin( collections, eq(collectionCards.collectionId, collections.id), ) .leftJoin( publishedRecords, eq(collections.publishedRecordId, publishedRecords.id), ) .where( and( eq(collectionCards.cardId, cardId), eq(collectionCards.addedBy, curatorId), ), ) .orderBy(asc(collections.name));
return collectionResults.map((result) => ({ id: result.id, uri: result.uri || undefined, name: result.name, description: result.description || undefined, })); } catch (error) { console.error('Error in getCollectionsContainingCardForUser:', error); throw error; } }
async getCollectionsWithUrl( url: string, options: CollectionForUrlQueryOptions, ): Promise<PaginatedQueryResult<CollectionForUrlRawDTO>> { try { const { page, limit, sortBy, sortOrder } = options; const offset = (page - 1) * limit;
// Build the sort order const orderDirection = sortOrder === SortOrder.ASC ? asc : desc;
// Find all URL cards with this URL const urlCardsQuery = this.db .select({ id: cards.id, }) .from(cards) .where(and(eq(cards.url, url), eq(cards.type, CardTypeEnum.URL)));
const urlCardsResult = await urlCardsQuery;
if (urlCardsResult.length === 0) { return { items: [], totalCount: 0, hasMore: false, }; }
const cardIds = urlCardsResult.map((card) => card.id);
// Find all collections that contain any of these cards with pagination and sorting const collectionsQuery = this.db .select({ id: collections.id, name: collections.name, description: collections.description, accessType: collections.accessType, authorId: collections.authorId, uri: publishedRecords.uri, createdAt: collections.createdAt, updatedAt: collections.updatedAt, cardCount: collections.cardCount, addedAt: sql<Date>`MAX(${collectionCards.addedAt})`.as('added_at'), }) .from(collections) .leftJoin( publishedRecords, eq(collections.publishedRecordId, publishedRecords.id), ) .innerJoin( collectionCards, eq(collections.id, collectionCards.collectionId), ) .where(inArray(collectionCards.cardId, cardIds)) .groupBy( collections.id, collections.name, collections.description, collections.accessType, collections.authorId, collections.createdAt, collections.updatedAt, collections.cardCount, publishedRecords.uri, ) .orderBy( sortBy === CollectionSortField.ADDED_AT ? orderDirection(sql`MAX(${collectionCards.addedAt})`) : orderDirection(this.getSortColumn(sortBy)), ) .limit(limit) .offset(offset);
const collectionsResult = await collectionsQuery;
// Get total count of distinct collections const totalCountQuery = this.db .selectDistinct({ id: collections.id, }) .from(collections) .innerJoin( collectionCards, eq(collections.id, collectionCards.collectionId), ) .where(inArray(collectionCards.cardId, cardIds));
const totalCountResult = await totalCountQuery; const totalCount = totalCountResult.length; const hasMore = offset + collectionsResult.length < totalCount;
const items = collectionsResult.map((result) => ({ id: result.id, uri: result.uri || undefined, name: result.name, description: result.description || undefined, accessType: result.accessType, authorId: result.authorId, }));
return { items, totalCount, hasMore, }; } catch (error) { console.error('Error in getCollectionsWithUrl:', error); throw error; } }
async searchCollections( options: SearchCollectionsOptions, ): Promise<PaginatedQueryResult<CollectionQueryResultDTO>> { try { const { page, limit, sortBy, sortOrder, searchText, authorId, accessType, } = options; const offset = (page - 1) * limit;
// Build the sort order const orderDirection = sortOrder === SortOrder.ASC ? asc : desc;
// Build where conditions const whereConditions = [];
// Add author filter if provided if (authorId) { whereConditions.push(eq(collections.authorId, authorId)); }
// Add access type filter if provided if (accessType) { whereConditions.push(eq(collections.accessType, accessType)); }
// Add tokenized search condition if searchText is provided if (searchText && searchText.trim()) { const searchWords = searchText.trim().split(/\s+/); const searchConditions = searchWords.map( (word) => or( ilike(collections.name, `%${word}%`), ilike(collections.description, `%${word}%`), )!, );
// All words must be found (AND logic) whereConditions.push(and(...searchConditions)!); }
// Build the where clause const whereClause = whereConditions.length > 0 ? sql`${whereConditions.reduce((acc, condition, index) => index === 0 ? condition : sql`${acc} AND ${condition}`, )}` : sql`1=1`; // Always true when no conditions
// Query collections with their stored card counts and URIs const collectionsQuery = this.db .select({ id: collections.id, name: collections.name, description: collections.description, accessType: collections.accessType, createdAt: collections.createdAt, updatedAt: collections.updatedAt, authorId: collections.authorId, cardCount: collections.cardCount, uri: publishedRecords.uri, }) .from(collections) .leftJoin( publishedRecords, eq(collections.publishedRecordId, publishedRecords.id), ) .where(whereClause) .orderBy(orderDirection(this.getSortColumn(sortBy))) .limit(limit) .offset(offset);
const collectionsResult = await collectionsQuery;
// Get total count with same search conditions const totalCountResult = await this.db .select({ count: count() }) .from(collections) .where(whereClause);
const totalCount = totalCountResult[0]?.count || 0; const hasMore = offset + collectionsResult.length < totalCount;
// Map to DTOs const items = collectionsResult.map((raw) => CollectionMapper.toQueryResult({ id: raw.id, uri: raw.uri, name: raw.name, description: raw.description, accessType: raw.accessType, createdAt: raw.createdAt, updatedAt: raw.updatedAt, authorId: raw.authorId, cardCount: raw.cardCount, }), );
return { items, totalCount, hasMore, }; } catch (error) { console.error('Error in searchCollections:', error); throw error; } }
async getOpenCollectionsWithContributor( options: GetOpenCollectionsWithContributorOptions, ): Promise<PaginatedQueryResult<CollectionQueryResultDTO>> { try { const { contributorId, page, limit, sortBy, sortOrder } = options; const offset = (page - 1) * limit;
// Build the sort order const orderDirection = sortOrder === SortOrder.ASC ? asc : desc;
// Get collections where: // 1. User has added cards (via collection_cards.addedBy) // 2. User is NOT the author (collections.authorId != contributorId) // 3. Collection is OPEN (collections.accessType = 'OPEN') // Sort by most recent contribution (addedAt DESC) as primary sort
const collectionsQuery = this.db .selectDistinct({ id: collections.id, name: collections.name, description: collections.description, accessType: collections.accessType, createdAt: collections.createdAt, updatedAt: collections.updatedAt, authorId: collections.authorId, cardCount: collections.cardCount, uri: publishedRecords.uri, // Get the most recent contribution date for sorting lastContributionDate: sql<Date>`MAX(${collectionCards.addedAt})`.as( 'last_contribution_date', ), }) .from(collections) .leftJoin( publishedRecords, eq(collections.publishedRecordId, publishedRecords.id), ) .innerJoin( collectionCards, eq(collections.id, collectionCards.collectionId), ) .where( and( eq(collectionCards.addedBy, contributorId), sql`${collections.authorId} != ${contributorId}`, // Not the author eq(collections.accessType, 'OPEN'), ), ) .groupBy( collections.id, collections.name, collections.description, collections.accessType, collections.createdAt, collections.updatedAt, collections.authorId, collections.cardCount, publishedRecords.uri, ) .orderBy( // Primary sort: by most recent contribution (addedAt DESC) desc(sql`MAX(${collectionCards.addedAt})`), // Secondary sort: by the specified field orderDirection(this.getSortColumn(sortBy)), ) .limit(limit) .offset(offset);
const collectionsResult = await collectionsQuery;
// Get total count with same conditions const totalCountQuery = this.db .selectDistinct({ id: collections.id, }) .from(collections) .innerJoin( collectionCards, eq(collections.id, collectionCards.collectionId), ) .where( and( eq(collectionCards.addedBy, contributorId), sql`${collections.authorId} != ${contributorId}`, eq(collections.accessType, 'OPEN'), ), );
const totalCountResult = await totalCountQuery; const totalCount = totalCountResult.length; const hasMore = offset + collectionsResult.length < totalCount;
// Map to DTOs const items = collectionsResult.map((raw) => CollectionMapper.toQueryResult({ id: raw.id, uri: raw.uri, name: raw.name, description: raw.description, accessType: raw.accessType, createdAt: raw.createdAt, updatedAt: raw.updatedAt, authorId: raw.authorId, cardCount: raw.cardCount, }), );
return { items, totalCount, hasMore, }; } catch (error) { console.error('Error in getOpenCollectionsWithContributor:', error); throw error; } }
async getCollectionContributors( collectionId: string, authorId: string, options: { page: number; limit: number }, ): Promise<PaginatedQueryResult<CollectionContributorDTO>> { try { const { page, limit } = options; const offset = (page - 1) * limit;
// Get unique contributors with their contribution count and last contribution time // Exclude the collection author const contributorsQuery = this.db .select({ userId: collectionCards.addedBy, contributionCount: sql<number>`CAST(COUNT(*) AS INTEGER)`.as( 'contribution_count', ), lastContributedAt: sql<Date>`MAX(${collectionCards.addedAt})`.as( 'last_contributed_at', ), }) .from(collectionCards) .where( and( eq(collectionCards.collectionId, collectionId), sql`${collectionCards.addedBy} != ${authorId}`, // Exclude author ), ) .groupBy(collectionCards.addedBy) .orderBy(sql`MAX(${collectionCards.addedAt}) DESC`) // Most recent contribution first .limit(limit) .offset(offset);
const contributors = await contributorsQuery;
// Get total count of distinct contributors (excluding author) const countQuery = this.db .select({ count: sql<number>`COUNT(DISTINCT ${collectionCards.addedBy})`, }) .from(collectionCards) .where( and( eq(collectionCards.collectionId, collectionId), sql`${collectionCards.addedBy} != ${authorId}`, ), );
const countResult = await countQuery; const totalCount = countResult[0]?.count || 0;
return { items: contributors.map((c) => ({ userId: c.userId, contributionCount: c.contributionCount, lastContributedAt: c.lastContributedAt, })), totalCount, hasMore: page * limit < totalCount, }; } catch (error) { console.error('Error in getCollectionContributors:', error); throw error; } }
async getCollectionCountForUrl(url: string): Promise<number> { try { // Count distinct collections that contain cards with this URL const result = await this.db .select({ count: sql<number>`COUNT(DISTINCT ${collectionCards.collectionId})`, }) .from(collectionCards) .innerJoin(cards, eq(collectionCards.cardId, cards.id)) .where(and(eq(cards.type, CardTypeEnum.URL), eq(cards.url, url)));
return Number(result[0]?.count || 0); } catch (error) { console.error('Error in getCollectionCountForUrl:', error); throw error; } }
async getProfileCollectionCount(authorId: string): Promise<number> { try { // Count collections created by this user const result = await this.db .select({ count: count(collections.id), }) .from(collections) .where(eq(collections.authorId, authorId));
return Number(result[0]?.count || 0); } catch (error) { console.error('Error in getProfileCollectionCount:', error); throw error; } }
private getSortColumn(sortBy: CollectionSortField) { switch (sortBy) { case CollectionSortField.NAME: return collections.name; case CollectionSortField.CREATED_AT: return collections.createdAt; case CollectionSortField.UPDATED_AT: return collections.updatedAt; case CollectionSortField.CARD_COUNT: return collections.cardCount; default: return collections.name; } }}