custom select query throw error on pagination

[farshadfahimi]

I have a query that use distinct with pagination
but the query of pagination miss distinct and the result of total is not currect

-- pagination query
SELECT COUNT(*) AS `total` FROM `products` LEFT JOIN `products_categories_xref` AS `x` ON `x`.`products_id` = `products`.`id` LEFT JOIN (
            SELECT  products_id, COUNT(*) AS c FROM orders_products
            INNER JOIN orders ON orders.id = orders_products.orders_id
            WHERE orders.created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
            GROUP BY products_id
            ORDER BY c DESC
            ) AS d ON d.products_id = products.id WHERE `is_published` = ? AND (`dist_status` != ? AND d.c > 0 OR (products.created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH) OR `stock_count` > ?)) AND (`products`.`categories_id` IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) OR `x`.`categories_id` IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AND EXISTS (SELECT * FROM `colors` WHERE (`slug` IN (?)) AND (`colors`.`id` = `products`.`colors_id`)) AND EXISTS (SELECT * FROM `products_categories` INNER JOIN `products_categories_xref` ON `products_categories`.`id` = `products_categories_xref`.`categories_id` WHERE (`slug` IN (?)) AND (`products`.`id` = `products_categories_xref`.`products_id`)) AND EXISTS (SELECT * FROM `brands` WHERE (`slug` IN (?)) AND (`brands`.`id` = `products`.`brands_id`)) AND `products`.`deleted_at` IS NULL 

-- my real query
SELECT DISTINCT `products`.*, `d`.`c` AS `sold` FROM `products` LEFT JOIN `products_categories_xref` AS `x` ON `x`.`products_id` = `products`.`id` LEFT JOIN (
            SELECT  products_id, COUNT(*) AS c FROM orders_products
            INNER JOIN orders ON orders.id = orders_products.orders_id
            WHERE orders.created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
            GROUP BY products_id
            ORDER BY c DESC
            ) AS d ON d.products_id = products.id WHERE `is_published` = ? AND (`dist_status` != ? AND d.c > 0 OR (products.created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH) OR `stock_count` > ?)) AND (`products`.`categories_id` IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) OR `x`.`categories_id` IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AND EXISTS (SELECT * FROM `colors` WHERE (`slug` IN (?)) AND (`colors`.`id` = `products`.`colors_id`)) AND EXISTS (SELECT * FROM `products_categories` INNER JOIN `products_categories_xref` ON `products_categories`.`id` = `products_categories_xref`.`categories_id` WHERE (`slug` IN (?)) AND (`products`.`id` = `products_categories_xref`.`products_id`)) AND EXISTS (SELECT * FROM `brands` WHERE (`slug` IN (?)) AND (`brands`.`id` = `products`.`brands_id`)) AND `products`.`deleted_at` IS NULL ORDER BY products.stock_count - products.reserved DESC, `sold` DESC LIMIT

[thetutlage]

Can you please share the TypeScript code you have written?

[farshadfahimi]

const query = Product.query()
      .preload('productPrice')
      .preload('productOffer')
      .preload('defaultMedia')
      .preload('rates', (builder) => {
        builder.withScopes((scopes) => scopes.published())
      })
      .select('products.*', 'd.c as sold')
      .joinRaw(
        `left join (
        SELECT  products_id, count(*) AS c FROM orders_products
        INNER JOIN orders ON orders.id = orders_products.orders_id
        WHERE orders.created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
        group by products_id
        order by c desc
        ) AS d on d.products_id = products.id`
      )
      .where('is_published', 1)
      .orderByRaw('IF(stock_count - reserved > 0, true, false) desc')
      .where((query) => {
        query
          .where('dist_status', '!=', 'DISCONTINUED')
          .whereRaw('d.c > 0')
          .orWhere((query) => {
            query
              .whereRaw('products.created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH)')
              .orWhere('stock_count', '>', 0)
          })
      })
      .where((query) => {
        query
          .whereIn('products.categories_id', category.relatedIds)
          .orWhereHas('productCategories', (categoryQuery) => {
            categoryQuery.whereIn('products_categories.id', category.relatedIds)
          })
      })

await query.paginate(request.input('page', 1), 10)

[farshadfahimi]

@thetutlage

right now test some other problem on the pagination.
when I have complicated query with some raw queries in select I couldn't use where in pagination

I show you an small example of this
I have a simple query like below

const name = null
const query = VerifyRequest.query().select(
      '*',
      Database.raw(`concat_ws(' ', first_name, last_name) as name`)
    )

if (name) {
query.where('name', 'like', "%name%")
}

await query.paginate(15)

in the above example if the name is null the query result of calculate pagination is something like below
select count(*) as totalfromverify_requests`

but if I have a name the query result should be some think like below
select count(*) as totalfromverify_requests where ((name like '%test%'))
and throw and an error for this

code: 'ER_BAD_FIELD_ERROR',
  errno: 1054,
  sqlMessage: "Unknown column 'name' in 'where clause'",
  sqlState: '42S22',
  index: 0,
  sql: "select count(*) as `total` from `verify_requests` where  ((`name` like '%test%'))"