Objection.js: Specific select fields on eager loading

[purepear]

  .query()
  .eager('pets').
  .pick(['id', 'name']);

AFAIK pick works after result is fetched from the database.. so if i have some big text/json/binary fields that i don't need it will still get them from db then parse it and then pick will eliminate them

How do i ignore them in the eager SELECT query? Are filters the only way?

  .query()
  .eager('pets(petsFilter)',{
    petsFilter: function(builder){
      builder.select('id', 'name')
    }
  });

[koskimas]

Yes, pick is done in the javascript side. The filters are the only way to control the select clause of individual eager queries. If you never want to fetch some columns for a relation, you can specify a filter for the relation in relationMappings:

class Person extends Model {
  static relationMappings = {
    pets: {
      relation: Model.OneToManyRelation,
      modelClass: Animal,
      filter: query => query.select('id', 'ownerId', 'name'),
      join: {
        from: 'Person.id',
        to: 'Animal.ownerId'
      }
    }
  }
}

[purepear]

oh.. i forgot i can put the filter there.. makes sense. Thanks @koskimas

[nurkkala]

Is the filter property in the relationMappings object supposed to _override_ the query, supplement it, what? When I add a filter like that above, the column names are simply added to the SELECT clause and don't eliminate data I'm trying _not_ to fetch.

Thanks!

[koskimas]

The relation query is simply passed to the filter function. The select method works as it does in knex: it adds to the previous selects. If there is no other explicit selects (which is the default) adding a filter like that should only select the given columns. If you explicitly call select for the query, the columns are accumulated.

[nurkkala]

My relation mapping looks like this (with the filter in place):

tags: {
                relation: db.Model.ManyToManyRelation,
                modelClass: __dirname + '/UserTag',
                filter: query => query.select('tag'),
                join: {
                    from: 'journalEntry.id',
                    through: {
                        from: 'journalEntryTag.journalEntryId',
                        to: 'journalEntryTag.userTagId'
                    },
                    to: 'userTag.id'
                }
            }

The resulting SQL query looks like this:

select "userTag".*, "tag", "journalEntryTag"."journalEntryId" 
as "objectiontmpjoin0" from "userTag" 
inner join "journalEntryTag" on "journalEntryTag"."userTagId" = "userTag"."id" 
where "journalEntryTag"."journalEntryId" in (?)

The tag is in the select list (as expected), but there's also the (default?) userTag.* specifier that I would like to override. My query (in a Hapi server method) is as follows:

        JournalEntry.query()
            .where('id', id)
            .eager('tags')
            .first()
            .then(result => next(null, result))
            .catch(err => next(err, null));

I have also tried using findById with identical results. Any thoughts?

By the way, I have _really_ enjoyed using Objection. The fact that it comes from my ancestral homeland is just a bonus. 🇫🇮

[peteyycz]

I have the same issue as @nurkkala, when using through queries, all of the fields are selected. Do you know any workarounds for that?

[kapouer]

Hmmm this works:

All.Block.query().select(blockCols).first()
  .whereSite(data.site).where('block.type', 'notfound')
  .eager('children(jsonColumns).^', {
    jsonColumns: query => query.select(blockCols)
  });

however i only had troubles trying to use relationMapping.filter as a default replacement.

[tuomari]

If you want to replace the selected fields, you can use:

      filter: query => query.clearSelect().select('id', 'ownerId', 'name'),

[CryptoKiddies]

Is there a difference between choosing columns with select as opposed to pick? I'm using Objection on top of postgres.

[alukach]

@GeeeCoin As the second comment says, pick is done client-side while select is done by the database.