Objection.js alias to relationMapping

Question

I have a working relation mapping with Objection / Knex / Sql server that is causing a problem when the results are paged.

components: {
        relation: BaseModel.HasManyRelation,
        modelClass: Component,
        join: {
            from: 'vehicle.id',
            to: 'component.vehicleID'
        }
    }

When I use withGraphFetched to get related components for every vehicle, the query fails if I include the 'vehicle.id' in the original select.

static getFieldList() {
    return [
        'id', 
        'mark',
        'model'
    ].
}

static getPagedList(page, pagelength) {
     return this.query()
        .select(this.getFieldList())
        .withGraphFetched('components')
        .page(page, pagelength)
}

Now, when paging is done, Objection / Knex runs a second query after the primary one to fetch the total number of rows. Objection adds 'vehicle.id' from the relation mapping to the query, thus causing the query to fail because the column 'id' is now fetched twice for the subquery.

exec sp_executesql @statement=N'select count(*) as [count] from (select [id], [mark], [model], [id] from [vehicle]) as [temp]'

My question is, how can this be avoided? Can I use some alias in the relation mapping? I tried 'vehicle.id as vehicleFK' in the relation mapping but that caused the withGraphFetched to not run at all.

score 0 · Answer 1 · answered Jul 09 '20 at 05:08

there might be two ways to try to solve your issue

get rid of component's id column

const componentsSelectList = ()=>{
        // select columns you need from components without the id column
        return [
            'column1', // columns you need
            'column2'
        ]
    }
    
    static getPagedList(page, pagelength) {
        return this.query()
           .select(this.getFieldList())
           .withGraphFetched('components',{minimize: true})
           .modifyGraph('components',builder => builder.select(componentsSelectList()))
           .page(page, pagelength)
    }

use ref function from objection to reference the id column from which table

const {ref} = require('objection')

...


static getFieldList() {
    return [
        ref('vehicle.id'), 
        'mark',
        'model'
    ].
}

static getPagedList(page, pagelength) {
    return this.query()
       .select(this.getFieldList())
       .withGraphFetched('components')
       .page(page, pagelength)
}

...

I think this solution will work. Thank you for this. But is it not the best solution for my needs. This solution requires the change to be made in the getFieldList() method. The preferred way would be add the alias in the relation mapping. I already have many listeners to the API that expect 'vehicle' to have an identifier named 'id'. So I would much prefer to change the relation mapping. — Jaakko Melolinna, Oct 05 '20 at 13:43
It is still the best solution for this so far. I would recommend it for all 'fresh' solutions where no depencies on names exist and the column names can be named freely. — Jaakko Melolinna, Oct 07 '20 at 15:56

Objection.js alias to relationMapping

1 Answers1