K
Kysely

help

How to translate the WITH keyword in postgres when SELECT does not have FROM?

TGTheo Gravity5/26/2023
I'm trying to translate the following postgres function into pure Kysley:
CREATE OR REPLACE FUNCTION rand.similarity_search(model TEXT, needle TEXT)
RETURNS TABLE (
document_chunk_id INTEGER,
similarity REAL,
text TEXT
) AS $$
BEGIN
RETURN QUERY
WITH query AS (
SELECT pgml.embed(model, needle) AS embedding
)
SELECT document_chunk_embedding.document_chunk_id, pgml.cosine_similarity(document_chunk_embedding.embedding, query.embedding), document_chunk.text AS similarity
FROM rand.document_chunk_embedding
JOIN rand.document_chunk ON document_chunk_embedding.document_chunk_id = document_chunk.id, query
ORDER BY similarity DESC
LIMIT 5;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION rand.similarity_search(model TEXT, needle TEXT)
RETURNS TABLE (
document_chunk_id INTEGER,
similarity REAL,
text TEXT
) AS $$
BEGIN
RETURN QUERY
WITH query AS (
SELECT pgml.embed(model, needle) AS embedding
)
SELECT document_chunk_embedding.document_chunk_id, pgml.cosine_similarity(document_chunk_embedding.embedding, query.embedding), document_chunk.text AS similarity
FROM rand.document_chunk_embedding
JOIN rand.document_chunk ON document_chunk_embedding.document_chunk_id = document_chunk.id, query
ORDER BY similarity DESC
LIMIT 5;
END;
$$ LANGUAGE plpgsql;
I'm able to translate the main select body:
return db.selectFrom('document_chunk_embedding')
.innerJoin('document_chunk', 'document_chunk_embedding.document_chunk_id', 'document_chunk.id')
.select(['document_chunk_embedding.document_chunk_id', 'document_chunk.text as similarity', db.fn('pgml.cosine_similarity', ['document_chunk_embedding.embedding', 'query.embedding'])])
.orderBy('similarity', 'desc')
.limit(5)
.returningAll()
.execute();
return db.selectFrom('document_chunk_embedding')
.innerJoin('document_chunk', 'document_chunk_embedding.document_chunk_id', 'document_chunk.id')
.select(['document_chunk_embedding.document_chunk_id', 'document_chunk.text as similarity', db.fn('pgml.cosine_similarity', ['document_chunk_embedding.embedding', 'query.embedding'])])
.orderBy('similarity', 'desc')
.limit(5)
.returningAll()
.execute();
But I'm not sure how to translate the WITH part:
WITH query AS (
SELECT pgml.embed(model, needle) AS embedding
)
WITH query AS (
SELECT pgml.embed(model, needle) AS embedding
)
TGTheo Gravity5/26/2023
So far I have:
return db
.with('query', (qb) => qb.selectFrom('??').select([db.fn('pgml.embed', [sql.val(model), sql.val(text)]).as('embedding')]))
.selectFrom(['document_chunk_embedding', 'query'])
.innerJoin('document_chunk', 'document_chunk_embedding.document_chunk_id', 'document_chunk.id')
.select(['document_chunk_embedding.document_chunk_id', 'document_chunk.text as similarity', db.fn('pgml.cosine_similarity', ['document_chunk_embedding.embedding', 'query.embedding'])])
.orderBy('similarity', 'desc')
.limit(5)
.returningAll()
.execute();
return db
.with('query', (qb) => qb.selectFrom('??').select([db.fn('pgml.embed', [sql.val(model), sql.val(text)]).as('embedding')]))
.selectFrom(['document_chunk_embedding', 'query'])
.innerJoin('document_chunk', 'document_chunk_embedding.document_chunk_id', 'document_chunk.id')
.select(['document_chunk_embedding.document_chunk_id', 'document_chunk.text as similarity', db.fn('pgml.cosine_similarity', ['document_chunk_embedding.embedding', 'query.embedding'])])
.orderBy('similarity', 'desc')
.limit(5)
.returningAll()
.execute();
I think the problem is that the SELECT in question doesn't have a FROM in the WITH It seems query needs to be at the end of the join and can't be part of SELECT FROM Not sure how to do that Got it to all work, it feels like a huge hack though:
export async function similaritySearch({
db,
text,
model,
}: {
db: Kysely<SbDatabase>;
text: string;
model?: string;
}): Promise<SimilaritySearchRow[]> {
return (
db
.with(
'query',
() =>
// Ignoring since kysely doesn't know how to work with selects without FROM
// You can see what this query outputs to here: https://kyse.link/?p=s&i=hrcwDweUyG01KMCYp34j
// eslint-disable-next-line @typescript-eslint/ban-ts-comment
//@ts-ignore
sql`(SELECT pgml.embed(${sql.val(model)}, ${sql.val(text)}) as embedding)`,
)
.selectFrom('document_chunk_embedding')
// eslint-disable-next-line @typescript-eslint/ban-ts-comment
//@ts-ignore
.innerJoin('document_chunk', 'document_chunk_embedding.document_chunk_id', sql.raw('document_chunk.id, query'))
.select([
'document_chunk_embedding.document_chunk_id',
'document_chunk.text as similarity',
// eslint-disable-next-line @typescript-eslint/ban-ts-comment
//@ts-ignore
db.fn('pgml.cosine_similarity', ['document_chunk_embedding.embedding', 'query.embedding']),
])
.orderBy('similarity', 'desc')
.limit(5)
.execute() as Promise<SimilaritySearchRow[]>
);
}
export async function similaritySearch({
db,
text,
model,
}: {
db: Kysely<SbDatabase>;
text: string;
model?: string;
}): Promise<SimilaritySearchRow[]> {
return (
db
.with(
'query',
() =>
// Ignoring since kysely doesn't know how to work with selects without FROM
// You can see what this query outputs to here: https://kyse.link/?p=s&i=hrcwDweUyG01KMCYp34j
// eslint-disable-next-line @typescript-eslint/ban-ts-comment
//@ts-ignore
sql`(SELECT pgml.embed(${sql.val(model)}, ${sql.val(text)}) as embedding)`,
)
.selectFrom('document_chunk_embedding')
// eslint-disable-next-line @typescript-eslint/ban-ts-comment
//@ts-ignore
.innerJoin('document_chunk', 'document_chunk_embedding.document_chunk_id', sql.raw('document_chunk.id, query'))
.select([
'document_chunk_embedding.document_chunk_id',
'document_chunk.text as similarity',
// eslint-disable-next-line @typescript-eslint/ban-ts-comment
//@ts-ignore
db.fn('pgml.cosine_similarity', ['document_chunk_embedding.embedding', 'query.embedding']),
])
.orderBy('similarity', 'desc')
.limit(5)
.execute() as Promise<SimilaritySearchRow[]>
);
}
IIgal5/26/2023
Hey 👋🏻 I've answered in github issue /w a playground link
TGTheo Gravity5/26/2023
Thanks Igal. I've responded. It's close but not the solution unfortunately
TGTheo Gravity5/27/2023
Linking the github issue thread so it can be traced: https://github.com/kysely-org/kysely/issues/517
GitHub
[Feature] SELECT without FROM · Issue #517 · kysely-org/kysely
I'd like support for the following use-case: WITH query AS ( SELECT pgml.embed(model, needle) AS embedding ) I've read from discord that this case isn't supported. Could you create a .s...

Looking for more? Join the community!

Want results from more Discord servers?
Add your server
Recommended Posts
Using column aliases in `.where()`This is a supported feature in MySQL, but having issues here: https://kyse.link/?p=s&i=xxyMrfnfJ6CjbUsing Postgres function in an insert with a field as a selectI'm trying to translate the following SQL: ```sql INSERT INTO sb.text_document_chunk (text_documentcreate role in migrationIs it possible to create a role in a migration? And to do the grants and things as part of that?How to insert JSONB types in postgres?I've looked at https://kysely-org.github.io/kysely/classes/InsertQueryBuilder.html#values and it's nQuerying two different tables with subset of common columnsI have two tables that have an intersection of common columns that I'm looking to query. For each ofHow to select from a function with parameters?I understand that `kysely` will not natively support stored procedures / functions, and was wonderinIn a transaction, how do you ignore generated field requirements?``` export interface TextDocumentTable { id: Generated<number>; created_at: Generated<Date>; } Cross database joins in MySQLHi all, Is it possible to do joins across databases within the same MySQL instance? i.e ```sql SELEC.where('x', 'is not', null) and correspond type's nullabilityNot sure if this even possible in typescript (although all of the work Kysely already makes it seem What is the suggested way of adding/removing methods to expression builders?Context: I am building a custom dialect for YDB https://github.com/Gaspero/kysely-ydb YDB is slightlRunning database agnostic queries (MySQL)As part of my test suite, I drop/create databases programmatically. Is it possible to have a Kysely Asserting type of .countAll() (MySQL)I've noticed that the return type from the result of aggregation functions like `countAll()` and `suCoalesce return empty arrayHey is there a way to return an empty array rather than `null` when an array column is empty? I alrkysely-codegen for multiple databasesI have two MySQL 'databases' within the same instance. From the documentation, it doesn't look like Using MySQL functions in SELECT statementHi! Just trying to migrate over from Knex. Skimmed over the documentation but still unsure of how tExtract OrderBy TS KeysHey I have the following query ```ts const getBaseQuery = ({ offset, pageSize, slug }: GetBaseQuerinsert into with mix of static and table valuesHow might I execute an insert into that combines JS-side values with a select, like: ``` INSERT INTOMigration error "TypeError: Cannot read properties of undefined (reading 'getExecutor')"I am trying to run a migration using Kysely, and its returning this error: ``` file:///Users/brunocr