Collection query with relation filter not return parent records with no matching child records

[osher]

Collection query with relation filter not return parent records with no matching child records

• Related Issues: none

Introduction

I have a question. I tried in the IRC but got no response, so... I hope it's doable.

Consider a relations between Library and Book where:

Library.prototype.books = function () { this.hasMany(Book) }

Consider a Book item to have a .status string attribute. Consider a .libraries() as collection of Library, present on some other root entity.

Issue Description

I need to query Library with it's books {withRelated: ['books']}, Where:

• I need only libraries that have books of a given statusFilter,
• I need library.books contain only books that answer the statusFilter

I got to run libraries().fetch({withRelated: { books: (q) => q.where({status}) }) but I still get libraries with no books inside

how can this query be optimized so the query won't return libraries with no books that answer to book.status == statusFilter ?

Steps to reproduce issue

root.libraries().fetch({
    withRelated:[{
      books: (query) => {
          if  (active) {
              query
              .where({status: filterStatus})
          }
      }
    }]
  })

Expected behaviour

I would like libraries with no books not to be returned at all by the DB.

Actual behaviour

I get libraries with no books and have to filter them client-side. (sql client, that is... it's done on the API server, and would be better if the query will handle it directly in the DB)

For now, I patch it with a client-side filter, like

  //TBD: this is a work around. 
  //  Need to find how, when statusFilter is provided -
  //  - to have the query return only libraries with books 
 //     that match statusFilter
  .then((libraries) => 
    active 
      ? libraries.toJSON().filter( library => library.books.length ) 
      : libraries
  );

This is ...gently put - less than ideal - Especially with big lists.

[ricardograca]

Currently not possible right now using only Bookshelf. Possibly one of the biggest omissions in my opinion. Related issues: #202, #597, #655, #833, #834 and #1129.

You can work around it with custom queries by accessing the knex query builder though. Check the related issues for possible solutions.

[johncpalmer]

I would also really like the ability to do this. Would make my life better, by a lot.

[throrin19]

any news about this ? I have the same problem and it's very important to make that. Sequelize do it. Why bookshelf not ?