Objection.js: Filter fields retrieved by eager?

[mike-kelly]

I have Card, Tag and Project models.
Tag has a ManyToMany relationship with Card, Card has a ManyToMany relationship with Tag and Project.

I want to retrieve tags which are related to cards which in turn are related to a single project.
So I did this:

      .query()
      .eager('cards')
      .joinRelation('[cards.[projects]]')
      .where('cards:projects.id', req.query.projectId)
      .groupBy('tags.id')
      .then((tags) => {
        res.json(tags);
      })
      .catch((error) => {
        console.log(error);
        res.send('An error occured');
      });

However I don't want to retrieve all the Cards' properties, only the card id. So I added a filter to my Tag Model's relationMapping:

class Tag extends BaseModel {

  static get tableName() {
    return 'tags';
  }

  static get jsonSchema() {
    return {
      type: 'object',
      required: ['title'],

      properties: {
        id: {type: 'integer'},
        color: {type: 'string', minLength: 4, maxLength: 7},
        bgColor: {type: 'string', minLength: 4, maxLength: 7},
        tCreated: {type: 'string'},
        tModified: {type: 'string'}
      }
    };
  }

  static get relationMappings() {
    const Card = require('./card');

    return {
      cards: {
        relation: BaseModel.ManyToManyRelation,
        modelClass: Card,
        filter: query => query.select('cards.id'),
        join: {
          from: 'tags.id',
          through: {
            from: 'cards_tags.tag_id',
            to: 'cards_tags.card_id'
          },
          to: 'cards.id'
        }
      }
    };
  };
}

However, the query I displayed returns all properties of Card. If I change the filter to filter: query => query.select('id'), I get an error about id being ambiguous.

How can I limit eager('cards') so that it just returns card.id for each card?

(It would also be useful to include a field cardCount with each tag result, but I couldn't manage that in the same query - is that possible?)

[mike-kelly]

I succeeded in getting the cardCount as follows:

      .query()
      .joinRelation('[cards.[projects]]')
      .where('cards:projects.id', req.query.projectId)
      .andWhere('tags.id', req.params.id)
      .select(Tag.raw('count(cards_join.card_id) as cards_count, tags.*'))
      .groupBy('tags.id')
      .then((tag) => {
        res.json(tag)
      })
      .catch((error) => {
        console.log(error);
        res.send('An error occured');
      });

I'm still interested in filtering properties of eager relations though.

[koskimas]

Hmm, weird. The relationMappings filter should work like that. I'll investigate. You can always use modifyEager to select:

    Tag
      .query()
      .eager('cards')
      .modifyEager('cards', cardsBuilder => cardsBuilder.select('id'))
      .joinRelation('cards.projects')
      .where('cards:projects.id', req.query.projectId)
      .groupBy('tags.id')
      .then((tags) => {
        res.json(tags);
      })
      .catch((error) => {
        console.log(error);
        res.send('An error occured');
      });

You can also create a namedFilter for Tag model

class Tag extends Model {
  static get namedFilters() {
    return {
      idOnly: builder => builder.select('id')
    }
  }
}

and then use that in the eager expression:

    Tag
      .query()
      .eager('cards(idOnly)')
      .joinRelation('cards.projects')
      .where('cards:projects.id', req.query.projectId)
      .groupBy('tags.id')
      .then((tags) => {
        res.json(tags);
      })
      .catch((error) => {
        console.log(error);
        res.send('An error occured');
      });

You can select the count also using this:

      .select('tags.*')
      .count('cards_join.card_id as cards_count')

or using a subquery:

      .select('tags.*', Tags.relatedQuery('cards').count().as('cards_count'))

[koskimas]

By the way, your query returns all cards of the tags that pass the filters and not only the cards for which card.projects.id == req.query.projectId. If you want only those, you can remove the joinRelation and use joinEager('cards.projects') instead of the regular eager('cards'). joinEager uses the same joins as joinRelation but in addition parses the flat result set into a tree structure.

[koskimas]

If you can live with the tag.cards array only containing the cards that match card.projects.id == req.query.projectId, I think you could get all in one query for example using this:

Tag
      .query()
      .joinEager('cards.projects')
      .where('cards:projects.id', req.query.projectId)
      .where('tags.id', req.params.id)
      .then(tags => {
        // The cardCount is now simply tag.cards.length.
        // If you want, you can create a virtualAttribute `cardsCount`
        // that returns `cards.length` when `cards` is present.
        res.json(tags);
      })
      .catch((error) => {
        console.log(error);
        res.send('An error occured');
      });

[mike-kelly]

Thanks, that's very useful. joinEager is what I needed as I only wanted the cards which matched the project id specified.
.joinEager('cards.projects') resulted in pulling down projects as well as cards. Using the namedFilter option on those models seems to be the best solution to just grabbing the ids. Using two separate modifyEager lines, one for cards, and one for projects, didn't seem to work.

[koskimas]

Well, of course joinEager('cards.projects') also pulls the projects. Why wouldn't it?

Using two separate modifyEager lines, one for cards, and one for projects, didn't seem to work.

What do you mean? "didn't seem to work" is a bit difficult to debug on my end. Did you do it like this:

.modifyEager('cards', builder => builder.select('id'))
.modifyEager('cards.projects', builder => builder.select('id'))

[mike-kelly]

Well, of course joinEager('cards.projects') also pulls the projects. Why wouldn't it?

No reason. However the original issue was about making a join while limiting the fields retrieved, so it was worth clarifying for other readers that the query given requires additional measures to achieve the original goal.

Did you do it like this:

No, I did this .modifyEager('projects', builder => builder.select('id')) for projects.