Can't filter by associations property on relation (filtered join)

[mishuagopian]

Hi @dresende @dxg , I'm getting some trouble to get the query I want to trigger. My models are simple and are the following:

  const pullrequest = db.define('pullrequest', {
    id             :     { type: 'number', required: true },
    number         :     { type: 'number' },
    created_at     :     { type: 'text' },
    finished_at    :     { type: 'text' },
    state          :     { type: 'text' },
    merged         :     { type: 'boolean' },
    owner_id       :     { type: 'number' },
    owner_name     :     { type: 'text' }
  });
  const review = db.define('review', {
    id              :     { type: 'number', required: true },
    reviewer_id     :     { type: 'number' },
    reviewer_name   :     { type: 'text' },
    picked_up_at    :     { type: 'text' },
    submitted_at    :     { type: 'text' },
    state           :     { type: 'text' }
  });
  review.hasOne('pullrequest', pullrequest, { reverse: 'reviews' });

The thing is I can't simply get pull requests that have no reviews. The query I want to trigger can look like the following:

SELECT *
    FROM pullrequest LEFT OUTER JOIN review
    ON pullrequest.id=review.pullrequest_id
    WHERE review.pullrequest_id IS NULL;

I tried to achieve this by doing the following:models.pullrequest.find({ reviews: [] }); but I only got: SELECT "id", "number", "created_at", "finished_at", "state", "merged", "owner_id", "owner_name", "repository_id" FROM "pullrequest".

This is just one example of something I can't retrieve (or don't know how to do it), but another can be to get those pull requests which have any review which state is some specific text.

That's it and thanks in advance!