How can I use raw query in model scope?

[iyashpal]

I am trying to add rawQueries within my model scopes but don't know how. I didn't find any documentation related to this. Here is the code snippets that I am trying to achive.

public static reviewColumns = scope((query: Builder) => 
  query.as(name)
   query.raw(
     'SELECT SUM(rating) AS reviews_star FROM reviews WHERE type_id=products.id'
   ).raw(
     'SELECT COUNT(*) AS reviews_count FROM reviews WHERE type_id=products.id'
   ))

public static reviewColumns = scope((query: Builder) => 
  query.as(name)
   query.rawQuery(
     'SELECT SUM(rating) AS reviews_star FROM reviews WHERE type_id=products.id'
   ).rawQuery(
     'SELECT COUNT(*) AS reviews_count FROM reviews WHERE type_id=products.id'
   ))

Thank you for the help.

[thetutlage]

Not sure what you are trying to achieve here. But, model query scopes are meant to modify an existing query. However, the usage of query.raw creates a new query altogether. So it is not possible to do what you are trying to do.

[iyashpal]

Okay, is it possible this way?

await Order.query().raw('SELECT COUNT(*) AS reviews_count FROM reviews WHERE type_id=products.id')

OR

await Order.query().rawQuery('SELECT COUNT(*) AS reviews_count FROM reviews WHERE type_id=products.id')

[thetutlage]

Why not simply run Database.rawQuery('SELECT COUNT(*) AS reviews_count FROM reviews WHERE type_id=products.id')?

[iyashpal]

Actually I am preparing response this way.

      const order = await Order.query()
        // Load products if it is requested
        .match([
          request.input('with', []).includes('order.products'),
          query => query.preload('products', (builder) => builder.match([
            // Load product media if it is requested in query string.
            request.input('with', []).includes('order.products.media'),
            query => query.preload('media'),
          ]).match([
            // Load product ingredients if it is requested in query string.
            request.input('with', []).includes('order.products.ingredients'),
            query => query.preload('ingredients'),
          ])),
        ])

        // Load order coupon if requested
        .match([request.input('with', []).includes('order.user'), query => query.preload('user')])

        // Load order coupon if requested
        .match([request.input('with', []).includes('order.coupon'), query => query.preload('coupon')])

        // Load order reviews if requested.
        .match([request.input('with', []).includes('order.reviews'), query => query.preload('reviews')])

        // Load order coupon if requested
        .match([request.input('with', []).includes('order.address'), query => query.preload('address')])

        // Load order ingredients if requested
        .match([request.input('with', []).includes('order.ingredients'), query => query.preload('ingredients')])

        .where('id', params.id).firstOrFail()

[iyashpal]

is it possible to embed Database.rawQuery('SELECT COUNT(*) AS reviews_count FROM reviews WHERE type_id=products.id') in above script?