Excluding results from Objection/Knex query based on withGraphFetched results

Question

I have two models in Objection - "brands" and "offers".

Brand:

const { Model } = require('objection')

class Brand extends Model {
  static get tableName() {
    return 'brands'
  }

  ...

  static get relationMappings() {
    const Offer = require('./offer-model')

    return {
      offer: {
        relation: Model.HasManyRelation,
        modelClass: Offer,
        join: { from: 'brands.id', to: 'offers.brand_id' }
      }
    }
  }
}

Offer:

const { Model } = require('objection')

class Offer extends Model {
  static get tableName() {
    return 'offers'
  }
}

A brand has many offers, but I want to get brands which have at least 1 offer using withGraphFetched, excluding brands which have no offers. Here's what I have so far:

const brandModel = this.objection.models.brand
const query = brandModel.query().withGraphFetched('offer')
query.page(page, page_size)
const offers = await query

This returns the "joined" data, but also returns brands which don't have offers. For example:

[{
  id:1,
  name: 'brand 1',
  offers: [{offerId: 1, offerName: 'offer 1'}]
},{
  id:2,
  name: 'brand 2',
  offers: []
}]

In the above data, I don't want the brand with ID 2 to be in the result set.

I am using Objection/Knex to paginate the results, so I can't just exclude the brands with empty object arrays after the query has been executed.

I can achieve this using raw queries, but that means I can't use the Objection dynamic attributes and a few other key parts of Objection.

Thanks!

Inbreathe

Answer 1

You can just tack a whereExists onto the query; something like

const query = brandModel.query()
    .withGraphFetched('offer')
    .whereExists(
        (qb) => qb.select('id').from('offers')
             .where('offers.brand_id', knex.ref('brands.id'))
    );

Even though the whereExists bit is directly Knex, the query still goes through your models so stuff you've defined there should still apply (maybe unless you're doing something very wild that directly affects the columns used inside the whereExists)

Hower