alternative enum handling

server/src/dtos/search.dto.ts

@@ -3,7 +3,14 @@ import { Place } from 'src/database';
 import { HistoryBuilder } from 'src/decorators';
 import { AlbumResponseSchema } from 'src/dtos/album.dto';
 import { AssetResponseSchema } from 'src/dtos/asset-response.dto';
-import { AssetOrder, AssetOrderSchema, AssetTypeSchema, AssetVisibilitySchema } from 'src/enum';
+import {
+  AssetOrder,
+  AssetOrderSchema,
+  AssetTypeSchema,
+  AssetVisibilitySchema,
+  SearchOrderField,
+  SearchOrderFieldSchema,
+} from 'src/enum';
 import { emptyStringToNull, isoDatetimeToDate, stringToBool } from 'src/validation';
 import z from 'zod';
 
@@ -141,6 +148,197 @@ const SearchSuggestionRequestSchema = z
   })
   .meta({ id: 'SearchSuggestionRequestDto' });
 
+const atLeastOneKey = <T extends z.ZodObject>(schema: T) => {
+  const keys = Object.keys(schema.shape);
+  return schema.refine((value) => Object.values(value).some((v) => v !== undefined), {
+    message: `At least one of the following keys is required: ${keys.join(', ')}`,
+  });
+};
+
+const IdFilterSchema = atLeastOneKey(
+  z.strictObject({
+    eq: z.uuidv4().optional(),
+    ne: z.uuidv4().optional(),
+  }),
+).meta({ id: 'IdFilter' });
+
+const IdFilterNullableSchema = atLeastOneKey(
+  z.strictObject({
+    eq: z.uuidv4().nullable().optional(),
+    ne: z.uuidv4().nullable().optional(),
+  }),
+).meta({ id: 'IdFilterNullable' });
+
+const IdsFilterSchema = atLeastOneKey(
+  z.strictObject({
+    any: z.array(z.uuidv4()).min(1).optional(),
+    all: z.array(z.uuidv4()).min(1).optional(),
+    none: z.array(z.uuidv4()).min(1).optional(),
+  }),
+).meta({ id: 'IdsFilter' });
+
+const StringFilterSchema = atLeastOneKey(
+  z.strictObject({
+    eq: z.string().optional(),
+    ne: z.string().optional(),
+    in: z.array(z.string()).min(1).optional(),
+    notIn: z.array(z.string()).min(1).optional(),
+  }),
+).meta({ id: 'StringFilter' });
+
+const StringFilterNullableSchema = atLeastOneKey(
+  z.strictObject({
+    eq: z.string().nullable().optional(),
+    ne: z.string().nullable().optional(),
+    in: z.array(z.string()).min(1).optional(),
+    notIn: z.array(z.string()).min(1).optional(),
+  }),
+).meta({ id: 'StringFilterNullable' });
+
+const StringPatternFilterSchema = atLeastOneKey(
+  z.strictObject({
+    eq: z.string().nullable().optional(),
+    ne: z.string().nullable().optional(),
+    in: z.array(z.string()).min(1).optional(),
+    notIn: z.array(z.string()).min(1).optional(),
+    like: z.string().min(1).optional(),
+    notLike: z.string().min(1).optional(),
+    startsWith: z.string().min(1).optional(),
+    endsWith: z.string().min(1).optional(),
+  }),
+).meta({ id: 'StringPatternFilter' });
+
+const NumberFilterSchema = atLeastOneKey(
+  z.strictObject({
+    eq: z.number().optional(),
+    ne: z.number().optional(),
+    lt: z.number().optional(),
+    lte: z.number().optional(),
+    gt: z.number().optional(),
+    gte: z.number().optional(),
+    in: z.array(z.number()).min(1).optional(),
+    notIn: z.array(z.number()).min(1).optional(),
+  }),
+).meta({ id: 'NumberFilter' });
+
+const NumberFilterNullableSchema = atLeastOneKey(
+  z.strictObject({
+    eq: z.number().nullable().optional(),
+    ne: z.number().nullable().optional(),
+    lt: z.number().optional(),
+    lte: z.number().optional(),
+    gt: z.number().optional(),
+    gte: z.number().optional(),
+    in: z.array(z.number()).min(1).optional(),
+    notIn: z.array(z.number()).min(1).optional(),
+  }),
+).meta({ id: 'NumberFilterNullable' });
+
+const DateFilterSchema = atLeastOneKey(
+  z.strictObject({
+    eq: isoDatetimeToDate.optional(),
+    ne: isoDatetimeToDate.optional(),
+    gt: isoDatetimeToDate.optional(),
+    gte: isoDatetimeToDate.optional(),
+    lt: isoDatetimeToDate.optional(),
+    lte: isoDatetimeToDate.optional(),
+  }),
+).meta({ id: 'DateFilter' });
+
+const DateFilterNullableSchema = atLeastOneKey(
+  z.strictObject({
+    eq: isoDatetimeToDate.nullable().optional(),
+    ne: isoDatetimeToDate.nullable().optional(),
+    gt: isoDatetimeToDate.optional(),
+    gte: isoDatetimeToDate.optional(),
+    lt: isoDatetimeToDate.optional(),
+    lte: isoDatetimeToDate.optional(),
+  }),
+).meta({ id: 'DateFilterNullable' });
+
+const BoolFilterSchema = z.strictObject({ eq: z.boolean() }).meta({ id: 'BoolFilter' });
+
+const enumFilterSchema = <T extends z.core.util.EnumLike>(values: z.ZodEnum<T>, id: string) =>
+  atLeastOneKey(
+    z.strictObject({
+      eq: values.optional(),
+      ne: values.optional(),
+      in: z.array(values).min(1).optional(),
+      notIn: z.array(values).min(1).optional(),
+    }),
+  ).meta({ id });
+
+const EnumFilterAssetTypeSchema = enumFilterSchema(AssetTypeSchema, 'EnumFilterAssetType');
+const EnumFilterAssetVisibilitySchema = enumFilterSchema(AssetVisibilitySchema, 'EnumFilterAssetVisibility');
+
+const StringSimilarityFilterSchema = z
+  .strictObject({
+    matches: z.string().min(1),
+  })
+  .meta({ id: 'StringSimilarityFilter' });
+
+export const SearchOrderSchema = z
+  .strictObject({
+    field: SearchOrderFieldSchema.default(SearchOrderField.FileCreatedAt),
+    direction: AssetOrderSchema.default(AssetOrder.Desc),
+  })
+  .meta({ id: 'SearchOrder' });
+
+const SearchFilterBranchSchema = z
+  .strictObject({
+    id: IdFilterSchema.optional(),
+    libraryId: IdFilterNullableSchema.optional(),
+    type: EnumFilterAssetTypeSchema.optional(),
+    visibility: EnumFilterAssetVisibilitySchema.optional(),
+    isFavorite: BoolFilterSchema.optional(),
+    isMotion: BoolFilterSchema.optional(),
+    isOffline: BoolFilterSchema.optional(),
+    isEncoded: BoolFilterSchema.optional(),
+    hasAlbums: BoolFilterSchema.optional(),
+    hasPeople: BoolFilterSchema.optional(),
+    hasTags: BoolFilterSchema.optional(),
+    city: StringFilterNullableSchema.optional(),
+    state: StringFilterNullableSchema.optional(),
+    country: StringFilterNullableSchema.optional(),
+    make: StringFilterNullableSchema.optional(),
+    model: StringFilterNullableSchema.optional(),
+    lensModel: StringFilterNullableSchema.optional(),
+    description: StringPatternFilterSchema.optional(),
+    originalFileName: StringPatternFilterSchema.optional(),
+    originalPath: StringPatternFilterSchema.optional(),
+    ocr: StringSimilarityFilterSchema.optional(),
+    rating: NumberFilterNullableSchema.optional(),
+    fileSizeInBytes: NumberFilterSchema.optional(),
+    takenAt: DateFilterSchema.optional(),
+    createdAt: DateFilterSchema.optional(),
+    updatedAt: DateFilterSchema.optional(),
+    trashedAt: DateFilterNullableSchema.optional(),
+    personIds: IdsFilterSchema.optional(),
+    tagIds: IdsFilterSchema.optional(),
+    albumIds: IdsFilterSchema.optional(),
+    checksum: StringFilterSchema.optional(),
+    encodedVideoPath: StringFilterSchema.optional(),
+  })
+  .meta({ id: 'SearchFilterBranch' });
+
+export const SearchFilterSchema = SearchFilterBranchSchema.extend({
+  or: z.array(SearchFilterBranchSchema).min(1).optional(),
+}).meta({ id: 'SearchFilter' });
+
+export type IdFilter = z.infer<typeof IdFilterSchema>;
+export type IdFilterNullable = z.infer<typeof IdFilterNullableSchema>;
+export type IdsFilter = z.infer<typeof IdsFilterSchema>;
+export type StringFilter = z.infer<typeof StringFilterSchema>;
+export type StringFilterNullable = z.infer<typeof StringFilterNullableSchema>;
+export type StringPatternFilter = z.infer<typeof StringPatternFilterSchema>;
+export type NumberFilter = z.infer<typeof NumberFilterSchema>;
+export type NumberFilterNullable = z.infer<typeof NumberFilterNullableSchema>;
+export type DateFilter = z.infer<typeof DateFilterSchema>;
+export type DateFilterNullable = z.infer<typeof DateFilterNullableSchema>;
+export type SearchOrder = z.infer<typeof SearchOrderSchema>;
+export type SearchFilter = z.infer<typeof SearchFilterSchema>;
+export type SearchFilterBranch = z.infer<typeof SearchFilterBranchSchema>;
+
 export class RandomSearchDto extends createZodDto(RandomSearchSchema) {}
 export class LargeAssetSearchDto extends createZodDto(LargeAssetSearchSchema) {}
 export class MetadataSearchDto extends createZodDto(MetadataSearchSchema) {}

server/src/enum.ts

@@ -1176,3 +1176,12 @@ export enum WorkflowType {
 }
 
 export const WorkflowTypeSchema = z.enum(WorkflowType).describe('Workflow type').meta({ id: 'WorkflowType' });
+
+export enum SearchOrderField {
+  FileCreatedAt = 'fileCreatedAt',
+  LocalDateTime = 'localDateTime',
+  FileSizeInBytes = 'fileSizeInBytes',
+  Rating = 'rating',
+}
+
+export const SearchOrderFieldSchema = z.enum(SearchOrderField).meta({ id: 'SearchOrderField' });

server/src/queries/search.repository.sql

@@ -281,3 +281,444 @@ where
   and "deletedAt" is null
   and "lensModel" is not null
   and "lensModel" != $3
+
+-- SearchRepository.searchMetadataV3 (baseline)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and $2
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $3
+
+-- SearchRepository.searchMetadataV3 (empty)
+select
+  "asset".*
+from
+  "asset"
+where
+  $1
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $2
+
+-- SearchRepository.searchMetadataV3 (or-exif-only)
+select
+  "asset".*
+from
+  "asset"
+  inner join "asset_exif" on "asset"."id" = "asset_exif"."assetId"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and "asset_exif"."city" = $2
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $3
+
+-- SearchRepository.searchMetadataV3 (string-eq-null)
+select
+  "asset".*
+from
+  "asset"
+  inner join "asset_exif" on "asset"."id" = "asset_exif"."assetId"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and "asset_exif"."city" is null
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $2
+
+-- SearchRepository.searchMetadataV3 (string-pattern-like)
+select
+  "asset".*
+from
+  "asset"
+  inner join "asset_exif" on "asset"."id" = "asset_exif"."assetId"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and f_unaccent ("asset_exif"."description") ilike ('%' || f_unaccent ($2) || '%')
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $3
+
+-- SearchRepository.searchMetadataV3 (string-pattern-notLike)
+select
+  "asset".*
+from
+  "asset"
+  inner join "asset_exif" on "asset"."id" = "asset_exif"."assetId"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and f_unaccent ("asset_exif"."description") not ilike ('%' || f_unaccent ($2) || '%')
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $3
+
+-- SearchRepository.searchMetadataV3 (string-pattern-startsWith)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and f_unaccent ("asset"."originalFileName") ilike (f_unaccent ($2) || '%')
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $3
+
+-- SearchRepository.searchMetadataV3 (string-similarity-ocr)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and exists (
+    select
+    from
+      "ocr_search"
+    where
+      "ocr_search"."assetId" = "asset"."id"
+      and f_unaccent (ocr_search.text) %>> f_unaccent ($2)
+  )
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $3
+
+-- SearchRepository.searchMetadataV3 (ids-any)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and exists (
+    select
+    from
+      "album_asset"
+    where
+      "album_asset"."assetId" = "asset"."id"
+      and "album_asset"."albumId" = any ($2::uuid[])
+  )
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $3
+
+-- SearchRepository.searchMetadataV3 (ids-all)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and exists (
+    select
+      "asset_face"."assetId"
+    from
+      "asset_face"
+    where
+      "asset_face"."assetId" = "asset"."id"
+      and "asset_face"."personId" = any ($2::uuid[])
+      and "asset_face"."deletedAt" is null
+      and "asset_face"."isVisible" = $3
+    group by
+      "asset_face"."assetId"
+    having
+      count(distinct "asset_face"."personId") = $4
+  )
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $5
+
+-- SearchRepository.searchMetadataV3 (ids-all-single)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and exists (
+    select
+    from
+      "album_asset"
+    where
+      "album_asset"."assetId" = "asset"."id"
+      and "album_asset"."albumId" = any ($2::uuid[])
+  )
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $3
+
+-- SearchRepository.searchMetadataV3 (ids-none)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and not exists (
+    select
+    from
+      "tag_asset"
+      inner join "tag_closure" on "tag_asset"."tagId" = "tag_closure"."id_descendant"
+    where
+      "tag_asset"."assetId" = "asset"."id"
+      and "tag_closure"."id_ancestor" = any ($2::uuid[])
+  )
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $3
+
+-- SearchRepository.searchMetadataV3 (ids-tags-all)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and exists (
+    select
+      "tag_asset"."assetId"
+    from
+      "tag_asset"
+      inner join "tag_closure" on "tag_asset"."tagId" = "tag_closure"."id_descendant"
+    where
+      "tag_asset"."assetId" = "asset"."id"
+      and "tag_closure"."id_ancestor" = any ($2::uuid[])
+    group by
+      "tag_asset"."assetId"
+    having
+      count(distinct "tag_closure"."id_ancestor") >= $3
+  )
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $4
+
+-- SearchRepository.searchMetadataV3 (has-albums-false)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and not exists (
+    select
+    from
+      "album_asset"
+    where
+      "album_asset"."assetId" = "asset"."id"
+  )
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $2
+
+-- SearchRepository.searchMetadataV3 (is-encoded)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and exists (
+    select
+    from
+      "asset_file"
+    where
+      "asset_file"."assetId" = "asset"."id"
+      and "asset_file"."type" = $2
+  )
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $3
+
+-- SearchRepository.searchMetadataV3 (number-range)
+select
+  "asset".*
+from
+  "asset"
+  inner join "asset_exif" on "asset"."id" = "asset_exif"."assetId"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and (
+    "asset_exif"."fileSizeInByte" <= $2
+    and "asset_exif"."fileSizeInByte" >= $3
+  )
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $4
+
+-- SearchRepository.searchMetadataV3 (date-eq)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and "asset"."fileCreatedAt" = $2
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $3
+
+-- SearchRepository.searchMetadataV3 (date-range)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and (
+    "asset"."fileCreatedAt" >= $2
+    and "asset"."fileCreatedAt" < $3
+  )
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $4
+
+-- SearchRepository.searchMetadataV3 (order-fileSize-noExif)
+select
+  "asset".*
+from
+  "asset"
+  inner join "asset_exif" on "asset"."id" = "asset_exif"."assetId"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and $2
+order by
+  "asset_exif"."fileSizeInByte" desc
+limit
+  $3
+
+-- SearchRepository.searchMetadataV3 (order-rating-withExif)
+select
+  to_json("asset_exif") as "exifInfo",
+  "asset".*
+from
+  "asset"
+  inner join "asset_exif" on "asset"."id" = "asset_exif"."assetId"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and $2
+order by
+  "asset_exif"."rating" asc
+limit
+  $3
+
+-- SearchRepository.searchMetadataV3 (or-branches)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and (
+    "asset"."isFavorite" = $2
+    or exists (
+      select
+      from
+        "asset_face"
+      where
+        "asset_face"."assetId" = "asset"."id"
+        and "asset_face"."personId" = any ($3::uuid[])
+        and "asset_face"."deletedAt" is null
+        and "asset_face"."isVisible" = $4
+    )
+  )
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $5
+
+-- SearchRepository.searchMetadataV3 (or-with-top-level)
+select
+  "asset".*
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and (
+    "asset"."fileCreatedAt" >= $2
+    and "asset"."fileCreatedAt" < $3
+    and (
+      "asset"."isFavorite" = $4
+      or exists (
+        select
+        from
+          "album_asset"
+        where
+          "album_asset"."assetId" = "asset"."id"
+          and "album_asset"."albumId" = any ($5::uuid[])
+      )
+    )
+  )
+order by
+  "asset"."fileCreatedAt" desc
+limit
+  $6
+
+-- SearchRepository.searchStatisticsV3 (baseline)
+select
+  count(*) as "total"
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and $2
+order by
+  "asset"."fileCreatedAt" desc
+
+-- SearchRepository.searchStatisticsV3 (with-filter)
+select
+  count(*) as "total"
+from
+  "asset"
+  inner join "asset_exif" on "asset"."id" = "asset_exif"."assetId"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and (
+    "asset_exif"."fileSizeInByte" >= $2
+    and "asset"."fileCreatedAt" >= $3
+    and "asset"."fileCreatedAt" < $4
+  )
+order by
+  "asset"."fileCreatedAt" desc
+
+-- SearchRepository.searchStatisticsV3 (with-or)
+select
+  count(*) as "total"
+from
+  "asset"
+where
+  "asset"."ownerId" = any ($1::uuid[])
+  and (
+    "asset"."isFavorite" = $2
+    or not exists (
+      select
+      from
+        "album_asset"
+      where
+        "album_asset"."assetId" = "asset"."id"
+    )
+  )
+order by
+  "asset"."fileCreatedAt" desc

server/src/repositories/search.repository.ts

@@ -2,11 +2,12 @@ import { Injectable } from '@nestjs/common';
 import { Kysely, OrderByDirection, Selectable, ShallowDehydrateObject, sql } from 'kysely';
 import { InjectKysely } from 'nestjs-kysely';
 import { DummyValue, GenerateSql } from 'src/decorators';
-import { AssetStatus, AssetType, AssetVisibility, VectorIndex } from 'src/enum';
+import { SearchFilter, SearchOrder } from 'src/dtos/search.dto';
+import { AssetOrder, AssetStatus, AssetType, AssetVisibility, SearchOrderField, VectorIndex } from 'src/enum';
 import { probes } from 'src/repositories/database.repository';
 import { DB } from 'src/schema';
 import { AssetExifTable } from 'src/schema/tables/asset-exif.table';
-import { anyUuid, searchAssetBuilder, withExifInner } from 'src/utils/database';
+import { anyUuid, searchAssetBuilder, searchAssetBuilderLegacy, withExifInner } from 'src/utils/database';
 import { paginationHelper } from 'src/utils/pagination';
 import { isValidInteger } from 'src/validation';
 
@@ -121,6 +122,22 @@ export type AssetSearchOptions = BaseAssetSearchOptions & SearchRelationOptions;
 
 export type AssetSearchBuilderOptions = Omit<AssetSearchOptions, 'orderDirection'>;
 
+export interface AssetSearchBuilderV3Options {
+  filter?: SearchFilter;
+  /** Server-derived ownership scope. Never client-controlled. */
+  userIds?: string[];
+  withExif?: boolean;
+  withFaces?: boolean;
+  withPeople?: boolean;
+  withStacked?: boolean;
+  order?: SearchOrder;
+}
+
+export interface AssetSearchPaginationV3Options {
+  cursor?: string;
+  size: number;
+}
+
 export type SmartSearchOptions = SearchDateOptions &
   SearchEmbeddingOptions &
   SearchExifOptions &
@@ -195,7 +212,7 @@ export class SearchRepository {
   })
   async searchMetadata(pagination: SearchPaginationOptions, options: AssetSearchOptions) {
     const orderDirection = (options.orderDirection?.toLowerCase() || 'desc') as OrderByDirection;
-    const items = await searchAssetBuilder(this.db, options)
+    const items = await searchAssetBuilderLegacy(this.db, options)
       .selectAll('asset')
       .orderBy('asset.fileCreatedAt', orderDirection)
       .limit(pagination.size + 1)
@@ -216,7 +233,7 @@ export class SearchRepository {
     ],
   })
   searchStatistics(options: AssetSearchOptions) {
-    return searchAssetBuilder(this.db, options)
+    return searchAssetBuilderLegacy(this.db, options)
       .select((qb) => qb.fn.countAll<number>().as('total'))
       .executeTakeFirstOrThrow();
   }
@@ -234,7 +251,7 @@ export class SearchRepository {
     ],
   })
   async searchRandom(size: number, options: AssetSearchOptions) {
-    return searchAssetBuilder(this.db, options)
+    return searchAssetBuilderLegacy(this.db, options)
       .selectAll('asset')
       .orderBy(sql`random()`)
       .limit(size)
@@ -255,7 +272,7 @@ export class SearchRepository {
   })
   searchLargeAssets(size: number, options: LargeAssetSearchOptions) {
     const orderDirection = (options.orderDirection?.toLowerCase() || 'desc') as OrderByDirection;
-    return searchAssetBuilder(this.db, options)
+    return searchAssetBuilderLegacy(this.db, options)
       .selectAll('asset')
       .$call(withExifInner)
       .where('asset_exif.fileSizeInByte', '>', options.minFileSize || 0)
@@ -284,7 +301,7 @@ export class SearchRepository {
 
     return this.db.transaction().execute(async (trx) => {
       await sql`set local vchordrq.probes = ${sql.lit(probes[VectorIndex.Clip])}`.execute(trx);
-      const items = await searchAssetBuilder(trx, options)
+      const items = await searchAssetBuilderLegacy(trx, options)
         .selectAll('asset')
         .innerJoin('smart_search', 'asset.id', 'smart_search.assetId')
         .orderBy(sql`smart_search.embedding <=> ${options.embedding}`)
@@ -489,6 +506,175 @@ export class SearchRepository {
     return res.map((row) => row.lensModel!);
   }
 
+  @GenerateSql(
+    { name: 'baseline', params: [{ size: 100 }, { userIds: [DummyValue.UUID] }] },
+    { name: 'empty', params: [{ size: 100 }, {}] },
+    {
+      name: 'or-exif-only',
+      params: [{ size: 100 }, { userIds: [DummyValue.UUID], filter: { or: [{ city: { eq: DummyValue.STRING } }] } }],
+    },
+    {
+      name: 'string-eq-null',
+      params: [{ size: 100 }, { userIds: [DummyValue.UUID], filter: { city: { eq: null } } }],
+    },
+    {
+      name: 'string-pattern-like',
+      params: [{ size: 100 }, { userIds: [DummyValue.UUID], filter: { description: { like: DummyValue.STRING } } }],
+    },
+    {
+      name: 'string-pattern-notLike',
+      params: [{ size: 100 }, { userIds: [DummyValue.UUID], filter: { description: { notLike: DummyValue.STRING } } }],
+    },
+    {
+      name: 'string-pattern-startsWith',
+      params: [
+        { size: 100 },
+        { userIds: [DummyValue.UUID], filter: { originalFileName: { startsWith: DummyValue.STRING } } },
+      ],
+    },
+    {
+      name: 'string-similarity-ocr',
+      params: [{ size: 100 }, { userIds: [DummyValue.UUID], filter: { ocr: { matches: DummyValue.STRING } } }],
+    },
+    {
+      name: 'ids-any',
+      params: [{ size: 100 }, { userIds: [DummyValue.UUID], filter: { albumIds: { any: [DummyValue.UUID] } } }],
+    },
+    {
+      name: 'ids-all',
+      params: [
+        { size: 100 },
+        { userIds: [DummyValue.UUID], filter: { personIds: { all: [DummyValue.UUID, DummyValue.UUID] } } },
+      ],
+    },
+    {
+      name: 'ids-all-single',
+      params: [{ size: 100 }, { userIds: [DummyValue.UUID], filter: { albumIds: { all: [DummyValue.UUID] } } }],
+    },
+    {
+      name: 'ids-none',
+      params: [{ size: 100 }, { userIds: [DummyValue.UUID], filter: { tagIds: { none: [DummyValue.UUID] } } }],
+    },
+    {
+      name: 'ids-tags-all',
+      params: [
+        { size: 100 },
+        { userIds: [DummyValue.UUID], filter: { tagIds: { all: [DummyValue.UUID, DummyValue.UUID] } } },
+      ],
+    },
+    {
+      name: 'has-albums-false',
+      params: [{ size: 100 }, { userIds: [DummyValue.UUID], filter: { hasAlbums: { eq: false } } }],
+    },
+    {
+      name: 'is-encoded',
+      params: [{ size: 100 }, { userIds: [DummyValue.UUID], filter: { isEncoded: { eq: true } } }],
+    },
+    {
+      name: 'number-range',
+      params: [{ size: 100 }, { userIds: [DummyValue.UUID], filter: { fileSizeInBytes: { gte: 100, lte: 1000 } } }],
+    },
+    {
+      name: 'date-eq',
+      params: [{ size: 100 }, { userIds: [DummyValue.UUID], filter: { takenAt: { eq: DummyValue.DATE } } }],
+    },
+    {
+      name: 'date-range',
+      params: [
+        { size: 100 },
+        {
+          userIds: [DummyValue.UUID],
+          filter: { takenAt: { gte: DummyValue.DATE, lt: DummyValue.DATE } },
+        },
+      ],
+    },
+    {
+      name: 'order-fileSize-noExif',
+      params: [
+        { size: 100 },
+        {
+          userIds: [DummyValue.UUID],
+          order: { field: SearchOrderField.FileSizeInBytes, direction: AssetOrder.Desc },
+          withExif: false,
+        },
+      ],
+    },
+    {
+      name: 'order-rating-withExif',
+      params: [
+        { size: 100 },
+        {
+          userIds: [DummyValue.UUID],
+          order: { field: SearchOrderField.Rating, direction: AssetOrder.Asc },
+          withExif: true,
+        },
+      ],
+    },
+    {
+      name: 'or-branches',
+      params: [
+        { size: 100 },
+        {
+          userIds: [DummyValue.UUID],
+          filter: {
+            or: [{ isFavorite: { eq: true } }, { personIds: { any: [DummyValue.UUID] } }],
+          },
+        },
+      ],
+    },
+    {
+      name: 'or-with-top-level',
+      params: [
+        { size: 100 },
+        {
+          userIds: [DummyValue.UUID],
+          filter: {
+            takenAt: { gte: DummyValue.DATE, lt: DummyValue.DATE },
+            or: [{ isFavorite: { eq: true } }, { albumIds: { any: [DummyValue.UUID] } }],
+          },
+        },
+      ],
+    },
+  )
+  async searchMetadataV3(pagination: AssetSearchPaginationV3Options, options: AssetSearchBuilderV3Options) {
+    return await searchAssetBuilder(this.db, options)
+      .selectAll('asset')
+      .limit(pagination.size + 1)
+      .execute();
+  }
+
+  @GenerateSql(
+    { name: 'baseline', params: [{ userIds: [DummyValue.UUID] }] },
+    {
+      name: 'with-filter',
+      params: [
+        {
+          userIds: [DummyValue.UUID],
+          filter: {
+            takenAt: { gte: DummyValue.DATE, lt: DummyValue.DATE },
+            fileSizeInBytes: { gte: 100 },
+          },
+        },
+      ],
+    },
+    {
+      name: 'with-or',
+      params: [
+        {
+          userIds: [DummyValue.UUID],
+          filter: {
+            or: [{ isFavorite: { eq: true } }, { hasAlbums: { eq: false } }],
+          },
+        },
+      ],
+    },
+  )
+  searchStatisticsV3(options: AssetSearchBuilderV3Options) {
+    return searchAssetBuilder(this.db, options)
+      .select((qb) => qb.fn.countAll<number>().as('total'))
+      .executeTakeFirstOrThrow();
+  }
+
   private getExifField(field: 'city' | 'state' | 'country' | 'make' | 'model' | 'lensModel', userIds: string[]) {
     return this.db
       .selectFrom('asset_exif')

server/src/utils/database.ts

@@ -11,17 +11,42 @@ import {
   SelectQueryBuilder,
   ShallowDehydrateObject,
   sql,
+  SqlBool,
 } from 'kysely';
 import { PostgresJSDialect } from 'kysely-postgres-js';
 import { jsonArrayFrom, jsonObjectFrom } from 'kysely/helpers/postgres';
 import { Notice, PostgresError } from 'postgres';
 import { columns, lockableProperties, LockableProperty, Person } from 'src/database';
 import { AssetEditActionItem } from 'src/dtos/editing.dto';
-import { AssetFileType, AssetOrderBy, AssetVisibility, DatabaseExtension, ExifOrientation } from 'src/enum';
-import { AssetSearchBuilderOptions } from 'src/repositories/search.repository';
+import {
+  DateFilter,
+  DateFilterNullable,
+  IdFilter,
+  IdFilterNullable,
+  IdsFilter,
+  NumberFilter,
+  NumberFilterNullable,
+  SearchFilter,
+  SearchFilterBranch,
+  StringFilter,
+  StringFilterNullable,
+  StringPatternFilter,
+} from 'src/dtos/search.dto';
+import {
+  AssetFileType,
+  AssetOrder,
+  AssetOrderBy,
+  AssetType,
+  AssetVisibility,
+  DatabaseExtension,
+  ExifOrientation,
+  SearchOrderField,
+} from 'src/enum';
+import { AssetSearchBuilderOptions, AssetSearchBuilderV3Options } from 'src/repositories/search.repository';
 import { DB } from 'src/schema';
 import { AssetExifTable } from 'src/schema/tables/asset-exif.table';
 import { AudioStreamInfo, VectorExtension, VideoFormat, VideoPacketInfo, VideoStreamInfo } from 'src/types';
+import { fromChecksum } from 'src/utils/request';
 
 export const getKyselyConfig = (connection: DatabaseConnectionParams): KyselyConfig => {
   return {
@@ -368,7 +393,7 @@ export function withEdits(eb: ExpressionBuilder<DB, 'asset'>): AliasedEditAction
 const joinDeduplicationPlugin = new DeduplicateJoinsPlugin();
 /** TODO: This should only be used for search-related queries, not as a general purpose query builder */
 
-export function searchAssetBuilder(kysely: Kysely<DB>, options: AssetSearchBuilderOptions) {
+export function searchAssetBuilderLegacy(kysely: Kysely<DB>, options: AssetSearchBuilderOptions) {
   options.withDeleted ||= !!(options.trashedAfter || options.trashedBefore || options.isOffline);
   const visibility = options.visibility == null ? AssetVisibility.Timeline : options.visibility;
 
@@ -485,6 +510,469 @@ export function searchAssetBuilder(kysely: Kysely<DB>, options: AssetSearchBuild
     .$if(!options.withDeleted, (qb) => qb.where('asset.deletedAt', 'is', null));
 }
 
+const EXIF_FILTER_FIELDS = new Set<keyof SearchFilterBranch>([
+  'city',
+  'state',
+  'country',
+  'make',
+  'model',
+  'lensModel',
+  'description',
+  'rating',
+  'fileSizeInBytes',
+]);
+
+const EXIF_ORDER_FIELDS = new Set<SearchOrderField>([SearchOrderField.FileSizeInBytes, SearchOrderField.Rating]);
+
+function branchNeedsExifJoin(branch: SearchFilterBranch): boolean {
+  for (const key of Object.keys(branch) as (keyof SearchFilterBranch)[]) {
+    if (EXIF_FILTER_FIELDS.has(key)) {
+      return true;
+    }
+  }
+  return false;
+}
+
+function exifJoinRequired(filter: SearchFilter, orderField: SearchOrderField): boolean {
+  return (
+    EXIF_ORDER_FIELDS.has(orderField) ||
+    branchNeedsExifJoin(filter) ||
+    (filter.or?.some((branch) => branchNeedsExifJoin(branch)) ?? false)
+  );
+}
+
+type AssetExpressionBuilder = ExpressionBuilder<DB, 'asset' | 'asset_exif'>;
+
+function existsAlbumLink(eb: AssetExpressionBuilder, present: boolean) {
+  const expression = eb.exists((eb) => eb.selectFrom('album_asset').whereRef('album_asset.assetId', '=', 'asset.id'));
+  return present ? expression : eb.not(expression);
+}
+
+function existsPersonLink(eb: AssetExpressionBuilder, present: boolean) {
+  const expression = eb.exists((eb) =>
+    eb
+      .selectFrom('asset_face')
+      .whereRef('asset_face.assetId', '=', 'asset.id')
+      .where('asset_face.deletedAt', 'is', null)
+      .where('asset_face.isVisible', '=', true),
+  );
+  return present ? expression : eb.not(expression);
+}
+
+function existsTagLink(eb: AssetExpressionBuilder, present: boolean) {
+  const expression = eb.exists((eb) => eb.selectFrom('tag_asset').whereRef('tag_asset.assetId', '=', 'asset.id'));
+  return present ? expression : eb.not(expression);
+}
+
+function existsEncodedVideo(eb: AssetExpressionBuilder, present: boolean) {
+  const expression = eb.exists((eb) =>
+    eb
+      .selectFrom('asset_file')
+      .whereRef('asset_file.assetId', '=', 'asset.id')
+      .where('asset_file.type', '=', AssetFileType.EncodedVideo),
+  );
+  return present ? expression : eb.not(expression);
+}
+
+function existsOcrMatch(eb: AssetExpressionBuilder, matches: string) {
+  const tokens = tokenizeForSearch(matches).join(' ');
+  return eb.exists((eb) =>
+    eb
+      .selectFrom('ocr_search')
+      .whereRef('ocr_search.assetId', '=', 'asset.id')
+      .where(sql<SqlBool>`f_unaccent(ocr_search.text) %>> f_unaccent(${tokens})`),
+  );
+}
+
+const encodedVideoFileBase = (eb: ExpressionBuilder<DB, 'asset' | 'asset_exif'>) =>
+  eb
+    .selectFrom('asset_file')
+    .whereRef('asset_file.assetId', '=', 'asset.id')
+    .where('asset_file.type', '=', AssetFileType.EncodedVideo)
+    .where('asset_file.isEdited', '=', false);
+
+function existsEncodedVideoPath(eb: AssetExpressionBuilder, f: StringFilter) {
+  const out = [];
+  if (f.eq !== undefined) {
+    out.push(eb.exists((eb) => encodedVideoFileBase(eb).where('asset_file.path', '=', f.eq!)));
+  }
+  if (f.ne !== undefined) {
+    out.push(eb.exists((eb) => encodedVideoFileBase(eb).where('asset_file.path', '<>', f.ne!)));
+  }
+  if (f.in !== undefined) {
+    out.push(eb.exists((eb) => encodedVideoFileBase(eb).where('asset_file.path', 'in', f.in!)));
+  }
+  if (f.notIn !== undefined) {
+    out.push(eb.exists((eb) => encodedVideoFileBase(eb).where('asset_file.path', 'not in', f.notIn!)));
+  }
+  return out;
+}
+
+type Membership = 'album' | 'person' | 'tag';
+
+function idsAnyExists(eb: AssetExpressionBuilder, membership: Membership, ids: string[]) {
+  switch (membership) {
+    case 'album': {
+      return eb.exists((eb) =>
+        eb
+          .selectFrom('album_asset')
+          .whereRef('album_asset.assetId', '=', 'asset.id')
+          .where('album_asset.albumId', '=', anyUuid(ids)),
+      );
+    }
+    case 'person': {
+      return eb.exists((eb) =>
+        eb
+          .selectFrom('asset_face')
+          .whereRef('asset_face.assetId', '=', 'asset.id')
+          .where('asset_face.personId', '=', anyUuid(ids))
+          .where('asset_face.deletedAt', 'is', null)
+          .where('asset_face.isVisible', '=', true),
+      );
+    }
+    case 'tag': {
+      return eb.exists((eb) =>
+        eb
+          .selectFrom('tag_asset')
+          .innerJoin('tag_closure', 'tag_asset.tagId', 'tag_closure.id_descendant')
+          .whereRef('tag_asset.assetId', '=', 'asset.id')
+          .where('tag_closure.id_ancestor', '=', anyUuid(ids)),
+      );
+    }
+  }
+}
+
+function idsAllExists(eb: AssetExpressionBuilder, membership: Membership, ids: string[]) {
+  switch (membership) {
+    case 'album': {
+      return eb.exists((eb) =>
+        eb
+          .selectFrom('album_asset')
+          .select('album_asset.assetId')
+          .whereRef('album_asset.assetId', '=', 'asset.id')
+          .where('album_asset.albumId', '=', anyUuid(ids))
+          .groupBy('album_asset.assetId')
+          .having((eb) => eb.fn.count('album_asset.albumId').distinct(), '=', ids.length),
+      );
+    }
+    case 'person': {
+      return eb.exists((eb) =>
+        eb
+          .selectFrom('asset_face')
+          .select('asset_face.assetId')
+          .whereRef('asset_face.assetId', '=', 'asset.id')
+          .where('asset_face.personId', '=', anyUuid(ids))
+          .where('asset_face.deletedAt', 'is', null)
+          .where('asset_face.isVisible', '=', true)
+          .groupBy('asset_face.assetId')
+          .having((eb) => eb.fn.count('asset_face.personId').distinct(), '=', ids.length),
+      );
+    }
+    case 'tag': {
+      return eb.exists((eb) =>
+        eb
+          .selectFrom('tag_asset')
+          .innerJoin('tag_closure', 'tag_asset.tagId', 'tag_closure.id_descendant')
+          .select('tag_asset.assetId')
+          .whereRef('tag_asset.assetId', '=', 'asset.id')
+          .where('tag_closure.id_ancestor', '=', anyUuid(ids))
+          .groupBy('tag_asset.assetId')
+          .having((eb) => eb.fn.count('tag_closure.id_ancestor').distinct(), '>=', ids.length),
+      );
+    }
+  }
+}
+
+function idsPredicates(eb: AssetExpressionBuilder, membership: Membership, filter: IdsFilter = {}) {
+  const predicates: Expression<SqlBool>[] = [];
+  if (filter.any) {
+    predicates.push(idsAnyExists(eb, membership, filter.any));
+  }
+  if (filter.all) {
+    predicates.push(
+      filter.all.length === 1 ? idsAnyExists(eb, membership, filter.all) : idsAllExists(eb, membership, filter.all),
+    );
+  }
+  if (filter.none) {
+    predicates.push(eb.not(idsAnyExists(eb, membership, filter.none)));
+  }
+  return predicates;
+}
+
+function idPredicates(
+  eb: AssetExpressionBuilder,
+  column: 'asset.id' | 'asset.libraryId',
+  filter: IdFilter | IdFilterNullable = {},
+) {
+  const predicates: Expression<SqlBool>[] = [];
+  if (filter.eq === null) {
+    predicates.push(eb(column, 'is', null));
+  } else if (filter.eq !== undefined) {
+    predicates.push(eb(column, '=', asUuid(filter.eq)));
+  }
+  if (filter.ne === null) {
+    predicates.push(eb(column, 'is not', null));
+  } else if (filter.ne !== undefined) {
+    predicates.push(eb(column, '<>', asUuid(filter.ne)));
+  }
+  return predicates;
+}
+
+type EnumColumn = {
+  'asset.type': AssetType;
+  'asset.visibility': AssetVisibility;
+};
+
+type EnumValue = EnumColumn[keyof EnumColumn];
+
+function enumPredicates(
+  eb: AssetExpressionBuilder,
+  column: keyof EnumColumn,
+  filter: { eq?: EnumValue; ne?: EnumValue; in?: EnumValue[]; notIn?: EnumValue[] } = {},
+) {
+  const predicates: Expression<SqlBool>[] = [];
+  if (filter.eq !== undefined) {
+    predicates.push(eb(column, '=', filter.eq));
+  }
+  if (filter.ne !== undefined) {
+    predicates.push(eb(column, '<>', filter.ne));
+  }
+  if (filter.in !== undefined) {
+    predicates.push(eb(column, 'in', filter.in));
+  }
+  if (filter.notIn !== undefined) {
+    predicates.push(eb(column, 'not in', filter.notIn));
+  }
+  return predicates;
+}
+
+type StringColumn =
+  | 'asset_exif.city'
+  | 'asset_exif.state'
+  | 'asset_exif.country'
+  | 'asset_exif.make'
+  | 'asset_exif.model'
+  | 'asset_exif.lensModel'
+  | 'asset_exif.description'
+  | 'asset.originalFileName'
+  | 'asset.originalPath';
+
+function stringEqNeInPredicates(
+  eb: AssetExpressionBuilder,
+  column: StringColumn,
+  filter: StringFilterNullable | StringPatternFilter = {},
+) {
+  const predicates: Expression<SqlBool>[] = [];
+  if (filter.eq === null) {
+    predicates.push(eb(column, 'is', null));
+  } else if (filter.eq !== undefined) {
+    predicates.push(eb(column, '=', filter.eq));
+  }
+  if (filter.ne === null) {
+    predicates.push(eb(column, 'is not', null));
+  } else if (filter.ne !== undefined) {
+    predicates.push(eb(column, '<>', filter.ne));
+  }
+  if (filter.in !== undefined) {
+    predicates.push(eb(column, 'in', filter.in));
+  }
+  if (filter.notIn !== undefined) {
+    predicates.push(eb(column, 'not in', filter.notIn));
+  }
+  return predicates;
+}
+
+function stringPatternPredicates(eb: AssetExpressionBuilder, column: StringColumn, filter: StringPatternFilter = {}) {
+  const predicates: Expression<SqlBool>[] = stringEqNeInPredicates(eb, column, filter);
+  const ref = sql.ref(column);
+  if (filter.like !== undefined) {
+    predicates.push(sql<SqlBool>`f_unaccent(${ref}) ilike ('%' || f_unaccent(${filter.like}) || '%')`);
+  }
+  if (filter.notLike !== undefined) {
+    predicates.push(sql<SqlBool>`f_unaccent(${ref}) not ilike ('%' || f_unaccent(${filter.notLike}) || '%')`);
+  }
+  if (filter.startsWith !== undefined) {
+    predicates.push(sql<SqlBool>`f_unaccent(${ref}) ilike (f_unaccent(${filter.startsWith}) || '%')`);
+  }
+  if (filter.endsWith !== undefined) {
+    predicates.push(sql<SqlBool>`f_unaccent(${ref}) ilike ('%' || f_unaccent(${filter.endsWith}))`);
+  }
+  return predicates;
+}
+
+type NumberColumn = 'asset_exif.rating' | 'asset_exif.fileSizeInByte';
+
+function numberPredicates(
+  eb: AssetExpressionBuilder,
+  column: NumberColumn,
+  filter: NumberFilter | NumberFilterNullable = {},
+) {
+  const predicates: Expression<SqlBool>[] = [];
+  if (filter.eq === null) {
+    predicates.push(eb(column, 'is', null));
+  } else if (filter.eq !== undefined) {
+    predicates.push(eb(column, '=', filter.eq));
+  }
+  if (filter.ne === null) {
+    predicates.push(eb(column, 'is not', null));
+  } else if (filter.ne !== undefined) {
+    predicates.push(eb(column, '<>', filter.ne));
+  }
+  if (filter.lt !== undefined) {
+    predicates.push(eb(column, '<', filter.lt));
+  }
+  if (filter.lte !== undefined) {
+    predicates.push(eb(column, '<=', filter.lte));
+  }
+  if (filter.gt !== undefined) {
+    predicates.push(eb(column, '>', filter.gt));
+  }
+  if (filter.gte !== undefined) {
+    predicates.push(eb(column, '>=', filter.gte));
+  }
+  if (filter.in !== undefined) {
+    predicates.push(eb(column, 'in', filter.in));
+  }
+  if (filter.notIn !== undefined) {
+    predicates.push(eb(column, 'not in', filter.notIn));
+  }
+  return predicates;
+}
+
+type DateColumn = 'asset.fileCreatedAt' | 'asset.createdAt' | 'asset.updatedAt' | 'asset.deletedAt';
+
+function datePredicates(eb: AssetExpressionBuilder, column: DateColumn, filter: DateFilter | DateFilterNullable = {}) {
+  const predicates: Expression<SqlBool>[] = [];
+  if (filter.eq === null) {
+    predicates.push(eb(column, 'is', null));
+  } else if (filter.eq !== undefined) {
+    predicates.push(eb(column, '=', filter.eq));
+  }
+  if (filter.ne === null) {
+    predicates.push(eb(column, 'is not', null));
+  } else if (filter.ne !== undefined) {
+    predicates.push(eb(column, '<>', filter.ne));
+  }
+  if (filter.gt !== undefined) {
+    predicates.push(eb(column, '>', filter.gt));
+  }
+  if (filter.gte !== undefined) {
+    predicates.push(eb(column, '>=', filter.gte));
+  }
+  if (filter.lt !== undefined) {
+    predicates.push(eb(column, '<', filter.lt));
+  }
+  if (filter.lte !== undefined) {
+    predicates.push(eb(column, '<=', filter.lte));
+  }
+  return predicates;
+}
+
+function checksumPredicates(eb: AssetExpressionBuilder, filter: StringFilter = {}) {
+  const predicates: Expression<SqlBool>[] = [];
+  if (filter.eq !== undefined) {
+    predicates.push(eb('asset.checksum', '=', fromChecksum(filter.eq)));
+  }
+  if (filter.ne !== undefined) {
+    predicates.push(eb('asset.checksum', '<>', fromChecksum(filter.ne)));
+  }
+  if (filter.in !== undefined) {
+    predicates.push(eb('asset.checksum', 'in', filter.in.map(fromChecksum)));
+  }
+  if (filter.notIn !== undefined) {
+    predicates.push(eb('asset.checksum', 'not in', filter.notIn.map(fromChecksum)));
+  }
+  return predicates;
+}
+
+function buildBranchPredicates(eb: AssetExpressionBuilder, branch: SearchFilterBranch) {
+  return [
+    ...idPredicates(eb, 'asset.id', branch.id),
+    ...idPredicates(eb, 'asset.libraryId', branch.libraryId),
+    ...enumPredicates(eb, 'asset.type', branch.type),
+    ...enumPredicates(eb, 'asset.visibility', branch.visibility),
+    ...(branch.isFavorite ? [eb('asset.isFavorite', '=', branch.isFavorite.eq)] : []),
+    ...(branch.isOffline ? [eb('asset.isOffline', '=', branch.isOffline.eq)] : []),
+    ...(branch.isMotion ? [eb('asset.livePhotoVideoId', branch.isMotion.eq ? 'is not' : 'is', null)] : []),
+    ...(branch.isEncoded ? [existsEncodedVideo(eb, branch.isEncoded.eq)] : []),
+    ...(branch.hasAlbums ? [existsAlbumLink(eb, branch.hasAlbums.eq)] : []),
+    ...(branch.hasPeople ? [existsPersonLink(eb, branch.hasPeople.eq)] : []),
+    ...(branch.hasTags ? [existsTagLink(eb, branch.hasTags.eq)] : []),
+    ...stringEqNeInPredicates(eb, 'asset_exif.city', branch.city),
+    ...stringEqNeInPredicates(eb, 'asset_exif.state', branch.state),
+    ...stringEqNeInPredicates(eb, 'asset_exif.country', branch.country),
+    ...stringEqNeInPredicates(eb, 'asset_exif.make', branch.make),
+    ...stringEqNeInPredicates(eb, 'asset_exif.model', branch.model),
+    ...stringEqNeInPredicates(eb, 'asset_exif.lensModel', branch.lensModel),
+    ...stringPatternPredicates(eb, 'asset_exif.description', branch.description),
+    ...stringPatternPredicates(eb, 'asset.originalFileName', branch.originalFileName),
+    ...stringPatternPredicates(eb, 'asset.originalPath', branch.originalPath),
+    ...(branch.ocr ? [existsOcrMatch(eb, branch.ocr.matches)] : []),
+    ...numberPredicates(eb, 'asset_exif.rating', branch.rating),
+    ...numberPredicates(eb, 'asset_exif.fileSizeInByte', branch.fileSizeInBytes),
+    ...datePredicates(eb, 'asset.fileCreatedAt', branch.takenAt),
+    ...datePredicates(eb, 'asset.createdAt', branch.createdAt),
+    ...datePredicates(eb, 'asset.updatedAt', branch.updatedAt),
+    ...datePredicates(eb, 'asset.deletedAt', branch.trashedAt),
+    ...idsPredicates(eb, 'album', branch.albumIds),
+    ...idsPredicates(eb, 'person', branch.personIds),
+    ...idsPredicates(eb, 'tag', branch.tagIds),
+    ...checksumPredicates(eb, branch.checksum),
+    ...(branch.encodedVideoPath ? existsEncodedVideoPath(eb, branch.encodedVideoPath) : []),
+  ];
+}
+
+function applySearchOrder<O>(
+  qb: SelectQueryBuilder<DB, 'asset' | 'asset_exif', O>,
+  field: SearchOrderField,
+  direction: AssetOrder,
+) {
+  switch (field) {
+    case SearchOrderField.FileCreatedAt: {
+      return qb.orderBy('asset.fileCreatedAt', direction);
+    }
+    case SearchOrderField.LocalDateTime: {
+      return qb.orderBy('asset.localDateTime', direction);
+    }
+    case SearchOrderField.FileSizeInBytes: {
+      return qb.orderBy('asset_exif.fileSizeInByte', direction);
+    }
+    case SearchOrderField.Rating: {
+      return qb.orderBy('asset_exif.rating', direction);
+    }
+  }
+}
+
+export function searchAssetBuilder(kysely: Kysely<DB>, options: AssetSearchBuilderV3Options) {
+  const filter = options.filter ?? {};
+  const orderField = options.order?.field ?? SearchOrderField.FileCreatedAt;
+  const orderDirection = options.order?.direction ?? AssetOrder.Desc;
+  const needsExifJoin = exifJoinRequired(filter, orderField);
+
+  return kysely
+    .withPlugin(joinDeduplicationPlugin)
+    .selectFrom('asset')
+    .$if(needsExifJoin && !options.withExif, (qb) => qb.innerJoin('asset_exif', 'asset.id', 'asset_exif.assetId'))
+    .$if(!!options.withExif && needsExifJoin, withExifInner)
+    .$if(!!options.withExif && !needsExifJoin, withExif)
+    .$if(!!options.userIds && options.userIds.length > 0, (qb) =>
+      qb.where('asset.ownerId', '=', anyUuid(options.userIds!)),
+    )
+    .$if(!!(options.withFaces || options.withPeople), (qb) => qb.select(withFacesAndPeople))
+    .$if(options.withStacked === false, (qb) => qb.where('asset.stackId', 'is', null))
+    .where((eb) => {
+      const top = buildBranchPredicates(eb, filter);
+      if (filter.or && filter.or.length > 0) {
+        top.push(eb.or(filter.or.map((branch) => eb.and(buildBranchPredicates(eb, branch)))));
+      }
+      return top.length > 0 ? eb.and(top) : eb.val(true);
+    })
+    .$call((qb) =>
+      // cast: `.$if(needsExifJoin, ...)` doesn't carry the join into the type; `exifJoinRequired` guarantees it at runtime.
+      applySearchOrder(qb as SelectQueryBuilder<DB, 'asset' | 'asset_exif', unknown>, orderField, orderDirection),
+    );
+}
+
 export type ReindexVectorIndexOptions = { indexName: string; lists?: number };
 
 type VectorIndexQueryOptions = { table: string; vectorExtension: VectorExtension } & ReindexVectorIndexOptions;