Something went wrong. Try again.
A social knowledge tool for researchers built on ATProto
Something went wrong. Try again.
8.2 kB · 276 lines
TypeScript
at development
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277import { 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, CollectionForUrlDTO, CollectionForUrlQueryOptions,} 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, 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, 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 collections authored by this curator that contain this card const collectionResults = await this.db .select({ id: collections.id, name: collections.name, description: collections.description, uri: publishedRecords.uri, }) .from(collections) .leftJoin( publishedRecords, eq(collections.publishedRecordId, publishedRecords.id), ) .innerJoin( collectionCards, eq(collections.id, collectionCards.collectionId), ) .where( and( eq(collections.authorId, curatorId), eq(collectionCards.cardId, cardId), ), ) .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<CollectionForUrlDTO>> { 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 .selectDistinct({ id: collections.id, name: collections.name, description: collections.description, authorId: collections.authorId, uri: publishedRecords.uri, createdAt: collections.createdAt, updatedAt: collections.updatedAt, cardCount: collections.cardCount, }) .from(collections) .leftJoin( publishedRecords, eq(collections.publishedRecordId, publishedRecords.id), ) .innerJoin( collectionCards, eq(collections.id, collectionCards.collectionId), ) .where(inArray(collectionCards.cardId, cardIds)) .orderBy(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, authorId: result.authorId, }));
return { items, totalCount, hasMore, }; } catch (error) { console.error('Error in getCollectionsWithUrl:', 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; } }}