Press n or j to go to the next uncovered block, b, p or k for the previous block.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 | 53x 51x 49x 49x 49x 19x 19x 19x 18x 18x 21x 19x 19x 19x 18x 2x 19x 2x 2x 2x 2x 2x 19x 19x 19x 17x 15x 19x 19x 53x 53x 18x 13x 13x 7x 18x 13x 13x 2x 20x | // Shared room-categories builder — used by both the standalone
// `/api/marketplace/room-categories` endpoint and the homepage. Resolves a
// cover image + published-project count per room type WITHOUT correlated
// per-room-type subqueries (the old homepage 3-tier COALESCE was O(room_types ×
// media_rows) and timed out at prod scale). Cover priority:
// Tier 1: room_types.cover_media_id (admin-curated)
// Tier 2: any isCover=true media from a room of this type
// Tier 3: first media (by fetch order) from a room of this type
// Tiers 2/3 use a single sample-room-per-type scan + one media fetch + an
// in-memory pick — all index-backed, no correlated subqueries.
import { and, inArray, sql } from "drizzle-orm";
import type { Dal } from "../../dal";
import type { getDb } from "../../db";
import * as schema from "../../db/schema";
export type RoomCategory = {
value: string;
label: string;
icon: string | null;
coverImage: string | null;
projectCount: number;
};
type Db = ReturnType<typeof getDb>;
export async function buildRoomCategories(
db: Db,
dal: Dal,
): Promise<RoomCategory[]> {
// No published pros means no published rooms either — bail early.
const publishedProIds = await dal.pros.findIdsByStatus("published");
if (publishedProIds.length === 0) return [];
// Constrain rooms to those whose project belongs to a published pro via a
// correlated EXISTS (PK lookups) — avoids materializing an unbounded project
// ID bind list that, with the roomType IN (...) list, blew past D1's
// 100-parameter statement limit. Mirrors /api/marketplace/projects (PR #403).
const publishedProjectExists = sql`EXISTS (
SELECT 1 FROM ${schema.projects}
INNER JOIN ${schema.pros} ON ${schema.pros.id} = ${schema.projects.proId}
WHERE ${schema.projects.id} = ${schema.rooms.projectId}
AND ${schema.projects.status} = 'published'
AND ${schema.pros.status} = 'published'
)`;
const [roomStats, allRoomTypes] = await Promise.all([
db
.select({
roomType: schema.rooms.roomType,
projectCount: sql<number>`count(distinct ${schema.rooms.projectId})`,
})
.from(schema.rooms)
.where(publishedProjectExists)
.groupBy(schema.rooms.roomType),
db.select().from(schema.roomTypes),
]);
if (roomStats.length === 0) return [];
const labelMap = new Map<string, string>();
const iconMap = new Map<string, string | null>();
for (const rt of allRoomTypes) {
labelMap.set(rt.code, rt.displayName);
iconMap.set(rt.code, rt.icon);
}
const roomTypeCodes = roomStats.map((rs) => rs.roomType);
const codeSet = new Set(roomTypeCodes);
const coverByRoomType = new Map<string, string | null>();
// Tier 1: admin-curated covers (room_types.cover_media_id). One batched
// media lookup for the non-null cover ids of the room types in play.
const adminCoverIds = allRoomTypes
.filter((rt) => codeSet.has(rt.code) && rt.coverMediaId != null)
.map((rt) => rt.coverMediaId as number);
if (adminCoverIds.length > 0) {
const adminMedia = await dal.media.findByIds(adminCoverIds);
const storageById = new Map(adminMedia.map((m) => [m.id, m.storageKey]));
for (const rt of allRoomTypes) {
Eif (rt.coverMediaId != null && storageById.has(rt.coverMediaId)) {
coverByRoomType.set(rt.code, storageById.get(rt.coverMediaId) ?? null);
}
}
}
// Tiers 2/3: one sample room per type that lacks an admin cover, then one
// media fetch for those rooms; prefer isCover, else first by fetch order.
const sampleRooms = await db
.select({ id: schema.rooms.id, roomType: schema.rooms.roomType })
.from(schema.rooms)
.where(
and(
inArray(schema.rooms.roomType, roomTypeCodes),
publishedProjectExists,
),
);
const sampleRoomByType = new Map<string, number>();
for (const room of sampleRooms) {
if (
!coverByRoomType.has(room.roomType) &&
!sampleRoomByType.has(room.roomType)
) {
sampleRoomByType.set(room.roomType, room.id);
}
}
const sampleRoomIds = Array.from(sampleRoomByType.values());
const sampleMedia =
sampleRoomIds.length > 0
? await dal.media.findByRoomIds(sampleRoomIds)
: [];
const roomIdToType = new Map<number, string>();
for (const [type, roomId] of sampleRoomByType) roomIdToType.set(roomId, type);
// First pass: covers explicitly marked isCover.
for (const m of sampleMedia) {
const type = roomIdToType.get(m.roomId);
if (type && m.isCover && !coverByRoomType.has(type)) {
coverByRoomType.set(type, m.storageKey);
}
}
// Second pass: fall back to the first media for any type still uncovered.
for (const m of sampleMedia) {
const type = roomIdToType.get(m.roomId);
if (type && !coverByRoomType.has(type)) {
coverByRoomType.set(type, m.storageKey);
}
}
return roomStats.map((rs) => ({
value: rs.roomType,
label: labelMap.get(rs.roomType) ?? rs.roomType,
icon: iconMap.get(rs.roomType) ?? null,
coverImage: coverByRoomType.get(rs.roomType) ?? null,
projectCount: rs.projectCount,
}));
}
|