Prisma•2mo ago

queryRaw returns empty array

I am currently trying to implement vector search with Prisma and MariaDB. The problem I am facing is, that I have to use rawQuery since vectors are not supported by prisma and when I execute my raw Query through prisma.$queyRaw, I get an empty array back.. The exact same query run on DBeaver returns a result though. I am really confused here. What is going on?? My code:

6 Replies

Prisma AI Help•2mo ago

You chose to debug with a human. They'll tinker with your query soon. If you get curious meanwhile, hop into #ask-ai for a quick spin!

MatixOP•2mo ago

            const queryEmbeddingString = vectorToString(embeddedMeal.embedding);
            // 3. Use a raw SQL query with MariaDB's vector functions
            console.log('Querying with embedding string:', queryEmbeddingString.substring(0, 100) + '...');
            console.log('Thresholds:', { NOT_SIMILAR_THRESHOLD, RELATED_THRESHOLD });

            const result = await prisma.$queryRaw<{ mealId: number; imageHash: string; distance: number }[]>`
                SELECT
                    *
                FROM
                    (
                        SELECT
                            m.id as mealId,
                            m.title as title,
                            m.hash as mealHash,
                            mi.imageHash as imageHash,
                            VEC_DISTANCE_COSINE (m.embedding, Vec_FromText (${queryEmbeddingString})) as distance
                        FROM
                            Meal m
                            INNER JOIN MealToMealImage mtmi ON m.id = mtmi.mealId
                            INNER JOIN MealImage mi ON mtmi.imageId = mi.id
                        WHERE
                            m.embedding IS NOT NULL
                    ) AS subquery
                WHERE
                    distance > ${RELATED_THRESHOLD}
                    AND distance < ${NOT_SIMILAR_THRESHOLD}
                ORDER BY
                    distance ASC
                LIMIT
                    1;
            `;

            console.log('Query result type:', typeof result);
            console.log('Query result:', result);
            console.log('Similar meals result for meal ID', embeddedMeal.id, ':', result);

            const queryEmbeddingString = vectorToString(embeddedMeal.embedding);
            // 3. Use a raw SQL query with MariaDB's vector functions
            console.log('Querying with embedding string:', queryEmbeddingString.substring(0, 100) + '...');
            console.log('Thresholds:', { NOT_SIMILAR_THRESHOLD, RELATED_THRESHOLD });

            const result = await prisma.$queryRaw<{ mealId: number; imageHash: string; distance: number }[]>`
                SELECT
                    *
                FROM
                    (
                        SELECT
                            m.id as mealId,
                            m.title as title,
                            m.hash as mealHash,
                            mi.imageHash as imageHash,
                            VEC_DISTANCE_COSINE (m.embedding, Vec_FromText (${queryEmbeddingString})) as distance
                        FROM
                            Meal m
                            INNER JOIN MealToMealImage mtmi ON m.id = mtmi.mealId
                            INNER JOIN MealImage mi ON mtmi.imageId = mi.id
                        WHERE
                            m.embedding IS NOT NULL
                    ) AS subquery
                WHERE
                    distance > ${RELATED_THRESHOLD}
                    AND distance < ${NOT_SIMILAR_THRESHOLD}
                ORDER BY
                    distance ASC
                LIMIT
                    1;
            `;

            console.log('Query result type:', typeof result);
            console.log('Query result:', result);
            console.log('Similar meals result for meal ID', embeddedMeal.id, ':', result);

export function vectorToString(vector: number[]): string {
    return `[${vector.join(',')}]`;
}

export function vectorToString(vector: number[]): string {
    return `[${vector.join(',')}]`;
}

Exact same query in DBeaver returns result against same database:

                SELECT
                    *
                FROM
                    (
                        SELECT
                            m.id as mealId,
                            m.title as title,
                            m.hash as mealHash,
                            mi.imageHash as imageHash,
                            VEC_DISTANCE_COSINE (m.embedding, Vec_FromText ("[-0.00860489,0.0063545234,0...........]")) as distance
                        FROM
                            default.Meal m
                            INNER JOIN default.MealToMealImage mtmi ON m.id = mtmi.mealId
                            INNER JOIN default.MealImage mi ON mtmi.imageId = mi.id
                        WHERE
                            m.embedding IS NOT NULL
                    ) AS subquery
                WHERE
                    distance > 0
                    AND distance < 2
                ORDER BY
                    distance ASC
                LIMIT
                    1;

                SELECT
                    *
                FROM
                    (
                        SELECT
                            m.id as mealId,
                            m.title as title,
                            m.hash as mealHash,
                            mi.imageHash as imageHash,
                            VEC_DISTANCE_COSINE (m.embedding, Vec_FromText ("[-0.00860489,0.0063545234,0...........]")) as distance
                        FROM
                            default.Meal m
                            INNER JOIN default.MealToMealImage mtmi ON m.id = mtmi.mealId
                            INNER JOIN default.MealImage mi ON mtmi.imageId = mi.id
                        WHERE
                            m.embedding IS NOT NULL
                    ) AS subquery
                WHERE
                    distance > 0
                    AND distance < 2
                ORDER BY
                    distance ASC
                LIMIT
                    1;

Nurul•4w ago

Hmm, are you using the latest prisma version? Also do you get the same bheaviour with queryRawUnsafe?

MatixOP•4w ago

No way!! Thanks for your reply. Let me check that for you For queryRawUnsafe I get the same result. I am on the latest version of prisma client and prisma mariadb-adapter

TSG B2B Global Partner•4w ago

Autopro-html.txt

MatixOP•4w ago

What?

Gaming

Programming

queryRaw returns empty array

Did you find this page helpful?