Hello! I have queries with nested queries and with long where statements. And package

Something like that: <div class="highlight highlight-source-js notranslate positio

You can try rewrite your sql like this: <div class="highlight highlight-source-sql

Query string in request body about clickhouse HOT 5 CLOSED

xcHades commented on August 15, 2024

Query string in request body

from clickhouse.

Comments (5)

TimonKK commented on August 15, 2024

Hi,
Can you share your code?

from clickhouse.

xcHades commented on August 15, 2024

Something like that:

for (let i = 0; i < array.length; i++) {
        queries.push(`SELECT ${array[i].Id} AS Id,
        toInt64(coalesce(
        (
            SELECT count()
            FROM table1
            WHERE (Column1 = ${array[i].Field1}) AND (Column2 = ${array[i].Field2})
        ), 0) + coalesce(
        (
            SELECT count()
            FROM table2
            WHERE (Column1 = ${array[i].Field1}) AND (Column2 = ${array[i].Field2})
        ), 0))`);
    }
    ch.query(queries.join(' UNION ALL '), callback);

It's quite far from best practices, but it's the fastest and simplest way to extract required data. So I hope to solve that problem without changing DB structure.

from clickhouse.

TimonKK commented on August 15, 2024

You can try rewrite your sql like this:

SELECT
    countIf(
       (Column1 = 11) AND (Column2 = 12) 
    ) AS 1_id,
    countIf(
       (Column1 = 21) AND (Column2 = 22) 
    ) AS 2_id,
   ...
FROM (
    SELECT
        *
    FROM table1
    UNION ALL
    SELECT
        *
    FROM table2
)