strapi + knex to execute complex Queries

Question

How do I get the knex object to execute custom or complex queries within my strapi service?

My Strapi version has the strapi-hook-knex and strapi-hook-bookshelf installed but when I run qb.raw it is an undefined object.

This is to run queries like this:

qb.select(knex.raw('.... ?? )', '...'))

Acetylide

Answer 1

You will find it in strapi.connections.default

default if you don't change your connection name. If you did you will have to replace default by your connection name.

Yeorgi

It doesn't work with strapi v4.0.7, on my v3.6.5 it was working fine – Fausta

Answer 2

Took me awhile to pin down exact syntax for this. Here it is in case helpful to others. Could not have gotten there without Jim LAURIE's answer.

module.exports = {
  async findCustom(ctx) {
    const rawBuilder = strapi.connections.default.raw(
      "select field1 from mytable where field1 = 'x'"
    );
    const resp = await rawBuilder.then();
    return resp.rows;
  }
}

Clareclarence

Answer 3

in strapi V4 use this:

await strapi.db.connection.select("*").from("demos")

Renfred

Answer 4

With Strapi v4 use:

strapi.db.connection.raw 

instead of

strapi.connections.default.raw

Montagnard

Answer 5

console.log(strapi.db.connection.raw("SELECT COUNT(*) FROM public.mytable").then(r => { console.log(r) }, error => {console.log(error)}))

Forjudge

Answer 6

async findCustom(ctx) {
  const rawBuilder = (await strapi.db.connection.select().count().from("users_links").where({group_id:5}));

  try {
    ctx.body = rawBuilder;
  } catch (err) {
    ctx.body = err;
  }
}

Medicaid