Queryraw condition joining

I need to use a procedure and then filter on this from a sqlserver database. I am having some issues constructing a query that is dynamic and safe. Prisma.join is only for joining values, but how do I join conditions? What makes it complicated is that the conditions are dynamic filters. Some some may be present or not. Example
const whereClause = []

if (zipCodeLike && zipCodeLike.length > 0) {
const clauses = zipCodeLike.map((_, index) =>
index === 0 ? ` recentZIPCode LIKE ` : ` OR recentZIPCode LIKE `,
);
whereClause.push(Prisma.sql([...clauses, ""], ...zipCodeLike));
}


if (nameLike && nameLike.length > 0) {
const clauses = nameLike.map((_, index) =>
index === 0 ? ` name LIKE ` : ` OR name LIKE `,
);
whereClause.push(Prisma.sql([...clauses, ""], ...nameLike));
}

//How do I construct the where clause into a queryRaw
// in a way that they have brackets and AND between them? I tried explaining ChatGPT that the Prisma.join function is only for values but it cannot wrap it's processor around it. Perhaps what I am trying to do is impossible (and hence maybe bad)
const whereClause = []

if (zipCodeLike && zipCodeLike.length > 0) {
const clauses = zipCodeLike.map((_, index) =>
index === 0 ? ` recentZIPCode LIKE ` : ` OR recentZIPCode LIKE `,
);
whereClause.push(Prisma.sql([...clauses, ""], ...zipCodeLike));
}


if (nameLike && nameLike.length > 0) {
const clauses = nameLike.map((_, index) =>
index === 0 ? ` name LIKE ` : ` OR name LIKE `,
);
whereClause.push(Prisma.sql([...clauses, ""], ...nameLike));
}

//How do I construct the where clause into a queryRaw
// in a way that they have brackets and AND between them? I tried explaining ChatGPT that the Prisma.join function is only for values but it cannot wrap it's processor around it. Perhaps what I am trying to do is impossible (and hence maybe bad)
1 Reply
NubeBuster
NubeBuster4w ago
const conditions = [];
const params = [];

// Add bookingCount condition if it exists
if (bookingCount !== undefined) {
conditions.push(`bookingCount > ?`);
params.push(`${bookingCount}`);
}

// Add zipCodeLike conditions if they exist
if (Array.isArray(zipCodeLike) && zipCodeLike.length > 0) {
const zipConditions = zipCodeLike
.map(() => `recentZIPCode LIKE ?`)
.join(" OR ");
conditions.push(`(${zipConditions})`);
params.push(...zipCodeLike);
}

// Combine all conditions into a single WHERE clause
let whereClause = "";
if (conditions.length > 0) {
whereClause = `SELECT * FROM #TempResults WHERE ${conditions.join(" AND ")}`;
}
console.log("TTT", whereClause, params);

const query = Prisma.sql([whereClause, ...params]);
const conditions = [];
const params = [];

// Add bookingCount condition if it exists
if (bookingCount !== undefined) {
conditions.push(`bookingCount > ?`);
params.push(`${bookingCount}`);
}

// Add zipCodeLike conditions if they exist
if (Array.isArray(zipCodeLike) && zipCodeLike.length > 0) {
const zipConditions = zipCodeLike
.map(() => `recentZIPCode LIKE ?`)
.join(" OR ");
conditions.push(`(${zipConditions})`);
params.push(...zipCodeLike);
}

// Combine all conditions into a single WHERE clause
let whereClause = "";
if (conditions.length > 0) {
whereClause = `SELECT * FROM #TempResults WHERE ${conditions.join(" AND ")}`;
}
console.log("TTT", whereClause, params);

const query = Prisma.sql([whereClause, ...params]);
TTT SELECT * FROM #TempResults WHERE bookingCount > ? AND (recentZIPCode LIKE ? OR recentZIPCode LIKE ?) [ '2', '22%', '21%' ] ⨯ TypeError: Expected 4 strings to have 3 values What does "Expected 4 strings to have 3 values" even mean...? Bump Is this impossible? Bump Bump