using array of ids as a where clause, using magic sql operator

I really need some help here:

const query = sql`
        SELECT 
          ${Statistics.variantId}, 
          ${Statistics.from}, 
          SUM(${Statistics.count}) AS conversions
        FROM 
          ${Statistics}
        WHERE 
          ${Statistics.variantId} IN (
            ${variantIds.join(',')}
          ) 
        GROUP BY 
          ${Statistics.variantId}, 
          ${Statistics.from}
        ORDER BY 
          ${Statistics.from} ASC;
      `;

const query = sql`
        SELECT 
          ${Statistics.variantId}, 
          ${Statistics.from}, 
          SUM(${Statistics.count}) AS conversions
        FROM 
          ${Statistics}
        WHERE 
          ${Statistics.variantId} IN (
            ${variantIds.join(',')}
          ) 
        GROUP BY 
          ${Statistics.variantId}, 
          ${Statistics.from}
        ORDER BY 
          ${Statistics.from} ASC;
      `;

I'm getting an error on variantIds.join(',') saying invalid input syntax for type bigint: "1920,1921" these variant ids come from a different database so i can't use a subquery like i was using before. Thank you

Solution:

so just doing WHERE x IN (${sql.raw(variantIds.join(','))}) will for now at least not make your site explode

Jump to solution

5 Replies

Artamiel•6mo ago

can you make sure the variantIds is a array of number?

crawl•6mo ago

If you don’t need the data escaped you can use sql.raw here ran into a similar issue where an array of values was interpreted completely wrong with the sql tagged template

Solution

crawl•6mo ago

so just doing WHERE x IN (${sql.raw(variantIds.join(','))}) will for now at least not make your site explode

crawl•6mo ago

but you should def verify and make sure this isnt some user fed data that could be injected here the reason why a simple array join doesnt seem to work here is because it would make it a parameterized query and convert [1,2,3,4] to a single parameter of "1,2,3,4" the " seem to break the query afterwards

rubberduckiesOP•6mo ago

@crawl trying it now it works!!! Thank you so much

Gaming

Programming

using array of ids as a where clause, using magic sql operator