Use lucid `query()` with `db.raw()`

Hi, how would I filter with lucid after using db.raw()? i.e. here is one of my database raw

        let { rows: bills } = await db.raw(`
            SELECT
                CONCAT(b.month, '/', b.year) as time,
                b.id,
                pr.name as property_name,
                u.name as unit_name,
                b.renter,
                b.rent,
                (b.total - b.rent) as utilities_bills,
                b.total,
                sum(p.amount) as paid,
                COUNT(p.id) as payment_times,
                (b.total - sum(p.amount)) as dues
            FROM
                bills b
                LEFT JOIN payments p ON p.bill_id = b.id
                JOIN properties pr ON pr.id = b.property_id
                JOIN units u ON u.id = b.unit_id

                ${p_id ? `WHERE b.property_id = '${p_id}'` : ''}
                ${u_id ? `WHERE b.unit_id = '${u_id}'` : ''}
                ${
                    month && year
                        ? `WHERE b.month = '${month}' AND b.year='${year}' `
                        : ''
                }

            GROUP BY
                b.id,
                pr.name,
                u.name
            ORDER BY
                b.id DESC
            LIMIT ${limit}
            OFFSET ${offset}
        `)

Now I can’t use lucid query() anymore because I was using db.raw(), how would I implement this?
Because I need to chaining .where() on query()

Knex.raw query is just a single query returning whatever the underlying lib returns. There is no possibility to chain it as I know.

Have you tried to rewrite you query using query builder methods like select(knex.raw()) and etc?

Full docs just for reference:
http://knexjs.org/#Raw-Expressions

2 Likes