Theo's Typesafe Cult•6mo ago

Queryraw condition joining in Prisma

This question was also posted in the Prisma Discord but it's been a few days without replies. https://discord.com/channels/937751382725886062/1244937417493053440 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)

2 Replies

NubeBusterOP•5mo 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 Bump Bump Bump

NubeBusterOP•5mo ago

I added a playground for you to explore and experiment with this issue https://github.com/Nubebuster/prisma-raw

GitHub

GitHub - Nubebuster/prisma-raw

Contribute to Nubebuster/prisma-raw development by creating an account on GitHub.

Gaming

Programming

Queryraw condition joining in Prisma