massive.sql

WITH "target_embedding" AS (
    SELECT
        avg("embedding"."vector1024") AS "vector1024"
    FROM
        "document"
        INNER JOIN "block" ON "block"."document_id" = "document"."id"
        LEFT JOIN "block_appetite" ON (
            "block"."type" = 'APPETITE'
            AND "block_appetite"."id" = "block"."block_appetite_id"
        )
        LEFT JOIN "block_problem" ON (
            "block"."type" = 'PROBLEM'
            AND "block_problem"."id" = "block"."block_problem_id"
        )
        LEFT JOIN "block_solution" ON (
            "block"."type" = 'SOLUTION'
            AND "block_solution"."id" = "block"."block_solution_id"
        )
        LEFT JOIN "block_text" ON (
            "block"."type" = 'TEXT'
            AND "block_text"."id" = "block"."block_text_id"
        )
        INNER JOIN "content" ON "content"."id" = CASE
            WHEN "block"."type" = 'APPETITE' THEN "block_appetite"."content_id"
            WHEN "block"."type" = 'PROBLEM' THEN "block_problem"."content_id"
            WHEN "block"."type" = 'SOLUTION' THEN "block_solution"."content_id"
            WHEN "block"."type" = 'TEXT' THEN "block_text"."content_id"
            ELSE NULL
        END
        INNER JOIN "content_chunk" ON "content_chunk"."content_id" = "content"."id"
        AND "content_chunk"."content_hash" = "content"."hash"
        INNER JOIN "embedding" ON "embedding"."hash" = "content_chunk"."hash"
    WHERE
        "document"."id" = '01J7PXG8E8HEQN81B8YM83VXKQ'
        AND "document"."workspace_id" = '01J7PXG8C9YFBEHX4Q6N1E47JB'
        AND "embedding"."model" = 'TEST'
        AND "embedding"."dimension" = 'DIM_1024'
),
"note_embedding" AS (
    SELECT
        "note"."id" AS "note_id",
        "embedding".*
    FROM
        "note"
        INNER JOIN "content" ON "note"."content_id" = "content"."id"
        INNER JOIN "content_chunk" ON "content_chunk"."content_id" = "content"."id"
        AND "content_chunk"."content_hash" = "content"."hash"
        INNER JOIN "embedding" ON "embedding"."hash" = "content_chunk"."hash"
    WHERE
        "note"."workspace_id" = '01J7PXG8C9YFBEHX4Q6N1E47JB'
        AND "embedding"."model" = 'TEST'
        AND "embedding"."dimension" = 'DIM_1024'
),
"similar_embedding" AS (
    SELECT
        "note_embedding"."hash",
        "note_embedding"."vector1024" <=> "target_embedding"."vector1024" AS "distance"
    FROM
        "target_embedding",
        "note_embedding"
    ORDER BY
        "note_embedding"."vector1024" <=> "target_embedding"."vector1024"
),
"similar_note" AS (
    SELECT
        "note"."id" AS "note_id",
        "similar_embedding"."distance" AS "distance"
    FROM
        "similar_embedding"
        INNER JOIN "content_chunk" ON "content_chunk"."hash" = "similar_embedding"."hash"
        INNER JOIN "content" ON "content"."id" = "content_chunk"."content_id"
        AND "content"."hash" = "content_chunk"."content_hash"
        INNER JOIN "note" ON "note"."content_id" = "content"."id"
    WHERE
        "note"."workspace_id" = '01J7PXG8C9YFBEHX4Q6N1E47JB'
)
SELECT
    "similar_note".*
FROM
    "similar_note"
LIMIT
    '5'