dynamic where from array syntax?

[scottwillman]

Try as I might, I haven't yet gotten the hang of the string interpolation syntax here. I'm trying to dynamically insert some where statements for some JSON data. Here's a minimal example of what I'm trying, perhaps someone can spot my mistake. Please see the attempt (1 of many) at inserting the filters dynamically ${sql`${filters.join(' ')}`}. FWIW this query works in a DataGrip console.

const filters = [
  `AND (p.details ->> 'model_year')::NUMERIC < 2015`, 
  `AND (p.details ->> 'make')::TEXT = 'FORD'`
];

const products = await sql`
    SELECT 
      p.id, 
      to_json(p.details) product_data, 
      json_agg(ass.details) images
    FROM tbl_products p
      LEFT JOIN tbl_product_assets AS ass
        ON ass.product_id = p.id
    WHERE 
      p.client_id = ${user.clientId}
      AND p.deleted_at IS NULL
      ${sql`${filters.join(' ')}`}
    GROUP BY 
      p.id 
  `;

Error
PostgresError: syntax error at or near "$2"

Thank you!!!

[scottwillman]

That's excellent! Thanks for helping me get the rest of the way on this. Your first idea worked for me perfectly. I have a web page with a table displaying unknown data. The user needs to be able to filter by any of the columns, so the keys are completely dynamic. Hence, the filters array in my example will actually be built from query params of user selections. Again, this is perfect and exactly what I needed. Thank you!

[e3dio]

Your case is common including for me so I made more helper functions for chaining together any combo and nesting of AND/OR statements:

// helpers
const and = arr => arr.reduce((acc, x) => sql`${acc} AND ${x}`);
const or = arr => arr.reduce((acc, x) => sql`(${acc} OR ${x})`);

// example
const filters = and([
   sql`column1 = 1`,
   sql`column2 = 2`,
   or([
      sql`column3 = 3`,
      sql`column3 = 4`,
      and([
         sql`column4 = 4`,
         sql`column5 = 5`
      ])
   ])
]);

const result = await sql`SELECT * FROM table WHERE ${filters}`;

[e3dio]

And helper to map dynamic params object to sql statements:

const toSql = obj => Object.keys(obj).map(x => sql`${sql(x)} = ${obj[x]}`);

const params = {
   year: 2015,
   make: 'FORD'
};

const filters = and(toSql(params));

const result = await sql`SELECT * FROM table WHERE ${filters}`;

[scottwillman]

These are great! Thanks!

[kingfishx23]

AMAZING.

[meeomeoo]

hello @e3dio ,

Sorry but I have some case. I have a sub query and I can not build the query with that.
let subQuery = '';
arr.forEach((value, key) => {
subQuery += and ${key} = '${value}';
});

case 1:
const sqlQuery = await querySELECT * FROM tablename where 1 = 1 ${subQuery}
=> error: ${subQuery} will transfer to $1.

case2:
const sqlQuery = await query SELECT * FROM tablename where 1 = 1 ${querysubQuery}
=> error: sql will be: SELECT * FROM tablename where 1 = 1 " and ${key1} = '${value}' ....."

Could you help to to review them?

Thank you @e3dio , @scottwillman !

[e3dio]

I'm assuming your case is you have an array of Values and you are using the Index for the column, and you have a different main query filter, that would be this:

const subQuery = and(arr.map((value, key) => sql`${sql(key)} = ${value}`));

sql`SELECT * FROM table WHERE 1 = 1 AND ${subQuery}`;

[meeomeoo]

Thank you @e3dio!
But I still not sure about the and function in the sql!
Could you please to share the and function?
where can we define it?

Thank you!

[e3dio]

If you look very carefully, you will find it in my 1st post in this discussion

[meeomeoo]

yes, Thank you!