How to query on nested foreign keys in KnexJS + bookshelf

Question

I use knexJS and bookshelf ORM for my nodejs application. The problem is, when a record ID is given, I need to query for getting list of its "great-grandchildren" records. There are thousands of hundreds records in the destination table, so page number and size are also supposed to pass as params and I need to sort it out via query. Doing filter after getting all records is not a solution.

Let me explain the relationships between models.

// MenuItem model
module.exports = (Model, models) => {
  return Model.extend({
    tableName: 'menu_items',
    menu_day() {
      return this.belongsTo(models.MenuDay);
    },
  });
};

This is MenuItem model I am gonna get list for. As you see, it's related to MenuDay model via foreign key.

// MenuDay model
module.exports = (Model, models) => {
  return Model.extend({
    tableName: 'menu_days',
    menu_items() {
      return this.hasMany(models.MenuItem);
    },
    menu() {
      return this.belongsTo(models.Menu);
    }
  });
};

MenuDay model is related to Menu model via foreign key.

// Menu model
module.exports = (Model, models) => {
  return Model.extend({
    tableName: 'menus',
    menu_days() {
      return this.hasMany(models.MenuDay);
    },
    restaurant() {
      return this.belongsTo(models.Restaurant);
    }
  });
};

Menu model is related to Restaurant and a restaurant ID is just given as an input for this position.

// Restaurant model
module.exports = (Model, models) => {
  return Model.extend({
    tableName: 'restaurants',
    menus() {
      return this.hasMany(models.Menu);
    }
  });
};

I tried tremendous solutions for sorting this out but lost a couple of days of mine in this problem. Whatever it is, any help would be highly appreciated! You will save my life.

Answer 1

I could sort this out myself using Knex only, not Bookshelf.

knex.raw(`
 SELECT * FROM menu_items WHERE menu_day_id IN (
  SELECT id FROM menu_days WHERE menu_id IN (
    SELECT id FROM menus WHERE restaurant_id = ?
  )
 ) LIMIT ? OFFSET ?
`, [restaurantId, limit, (page - 1) * limit])
.then(res => {
  const menuItems = res.rows;
});