RR
DD
DD
if anything might be terrible in terms of performance 
Maybe some index I'm missing? Maybe the order by?RRDDDRRDDDD
RRD
RRRDDRRDDDRRRDDRRRRDDDRRDRDDRD
thumbnailJobthumbnailJobthumbnailJobvideos.createdAtonthumbnail_jobswhere(subquery)const videosWithTimestamps = db
.select({ videoId: timestamps.videoId })
.from(timestamps)
.where(eq(timestamps.videoId, videos.id));
console.log(
"all videos with timestamps",
await db.query.videos.findMany({
where: inArray(videos.id, videosWithTimestamps),
with: {
timestamps: true,
},
}),
);
// or
const numberOfTimestamps = db
.select({ count: count(timestamps.id) })
.from(timestamps)
.where(eq(timestamps.videoId, videos.id));
console.log(
"all videos with timestamps 2",
await db.query.videos.findMany({
where: gt(numberOfTimestamps, 0),
with: {
timestamps: true,
},
}),
);export async function selectVideos(limit: number = 10) {
limit = Math.min(limit, 20);
// Subquery for count of timestamps
const numberOfTimestamps = db
.select({ count: count(timestamps.id) })
.from(timestamps)
.where(eq(timestamps.videoId, videos.id));
// Select only videos with at least 1 timestamp
return db.query.videos.findMany({
where: gt(numberOfTimestamps, 0),
with: {
// timestamps: {
// orderBy: (timestamps, { asc }) => [asc(timestamps.startsAt)],
// },
thumbnailJob: true,
},
orderBy: (videos, { desc }) => [desc(videos.createdAt)],
limit,
});
}export async function selectVideos(limit: number = 10) {
limit = Math.min(limit, 20);
// Subquery for count of timestamps
const numberOfTimestamps = db
.select({ count: count(timestamps.id) })
.from(timestamps)
.where(eq(timestamps.videoId, videos.id));
return db
.select()
.from(videos)
.where(gt(numberOfTimestamps, 0))
.innerJoin(thumbnailJob)
.on(eq(thumbnailJob.videoId, videos.id))
.orderBy(desc(videos.createdAt))
.limit(limit);
}return db
.select()
.from(videos)
.where(gt(numberOfTimestamps, 0))
// .innerJoin(thumbnailJobs, eq(thumbnailJobs.videoId, videos.id))
.orderBy(desc(videos.createdAt))
.limit(limit);