Raw query

[ctaity-treinta]

Hi, what is the best way to execute a raw query and obtain the seame interface when use select().
I would like to do:

const xxxx: type = await knex.rawSelect(rawSelect).first();

const xxxx: type[] = await  knex.rawSelect(rawSelect);

const xxxx = await  knex.rawSelect(rawSelect).pluck;

and not obtain the raw result of thje driver.

Maybe i can write a plugin an extend the interface?????

function rawSelect<TRecord extends {}>(
  this: Knex.QueryBuilder,
): any {
  return "";
}

Thanks

[gabolera]

What is rawSelect @ctaity-treinta this is a your custom function?

You can try use just raw Docs

this not solve?

[ctaity-treinta]

What is rawSelect @ctaity-treinta this is a your custom function?

You can try use just raw Docs

this not solve?

No, because the raw response dependes on the driver, mysql and mysql2 or postgrelsql return different responses. Mysql returns a RawPacket, mysql2 i thinkg returns an array and Potregsql returns a QueryResult. And the result is not compatible with pluck o first functions.

The idea is to have the same type result when using select.

I cant doit with an extension, but i couldn't find how execute the query an parse inside the knex client.

[rluvaton]

Let's get back for a second, what is your use case for the regular select that you need the raw one?

[ctaity-treinta]

My ideal use case, is to have the same interafce of const xxxx = await knex("mytable").select().limit(10); when use raw query, like
const aaaa = await knex.rawQuery(sql);

In first case is homogeneous, dont matter if you use mysql, mysql2 , postgresql o whatever, Knex always return the same type of result, and can use first(), pluck(), etc, in the second use case Knex return de raw response of the driver, when you switch for example from mysql to mysql2 the type of the reposne changes.

[rluvaton]

can you give me an example for that specific SQL query that you can't start with the regular select + knex.raw()

[ctaity-treinta]

yes of course

`
SELECT SUM((IFNULL(td.sale_price,0) - IFNULL(td.cost, 0)) * td.quantity) as gains FROM transactions AS t INNER JOIN transaction_details AS td ON td.transaction_id = t.id WHERE t.store_id = "da7528ff-13c5-49f1-8f9f-5628f1ffcc14" AND (t.date / 1000) BETWEEN "1572953791" AND "1693948991" AND t.transaction_type_id in (1,4) AND t.transaction_status_id = 1 AND t.deleted_at is null AND (t.origin_id IS NULL OR t.origin_id != 3) AND td.deleted_at is null AND NOT EXISTS (SELECT transaction_id FROM payments WHERE transaction_id = t.id AND deleted_at is null);�

SELECT COUNT(t.id) as count FROM transactions as t WHERE t.store_id = "da7528ff-13c5-49f1-8f9f-5628f1ffcc14" AND t.transaction_type_id = 1 AND t.deleted_at is null AND (t.origin_id IS NULL OR t.origin_id != 3) AND EXISTS (SELECT td.id, p.id FROM transaction_details as td JOIN payments as p on td.transaction_id = p.transaction_id WHERE td.transaction_id = t.id AND p.deleted_at is null AND (p.date / 1000) BETWEEN "1572953791" AND "1693948991" AND td.deleted_at is null);

SELECT SUM(t.value) AS sales, COUNT(t.id) AS count FROM transactions AS t WHERE store_id = "da7528ff-13c5-49f1-8f9f-5628f1ffcc14" AND transaction_type_id in (1,4) AND (date / 1000) BETWEEN "1572953791" AND "1693948991" AND deleted_at is null AND transaction_status_id = 1 AND (t.origin_id IS NULL OR t.origin_id != 3) AND EXISTS (SELECT transaction_id FROM transaction_details WHERE transaction_id = t.id AND deleted_at is null) AND NOT EXISTS (SELECT transaction_id FROM payments WHERE transaction_id = t.id AND deleted_at is null);
`

we obtain these queries from another service and execute with knex with mysql driver with following code:

  async selectUsingSlave<T>(rawSql: string): Promise<T[]> {
    const [data] = await this.slave.raw<[T[]]>(rawSql);
    return data;
  }

in this case raw returns an array with [ results, metadata ] , if you change the driver to mysql2 o postgresql the type of the results change too.

I would like to have a method always return the sametype without importance of the driver, like

  async selectUsingSlave<T>(rawSql: string): Promise<T[]> {
    const results = await this.slave.selectRaw<T[]>(rawSql);
    return data;
  }

or

  selectUsingSlave<T>(rawSql: string): Promise<T[]> {
       return this.slave.selectRaw<T[]>(rawSql);
  }

For example, this is the type and output of mysql driver, the Class is RowDataPacket