Mapping has many relations into array of items either in postgres or objection.js

Question

I have a postgres database and am using Knex with Objection.js. This can either be postgres related or objection.js/knex related problem.

I have two models, Account and Stat. Account has field "accountId" and Stat also has field "accountId". One account can have multiple stats.

Account:
╔════════════╦═════════════╗
║ accountId  ║ title       ║
╠════════════╬═════════════╣
║ a          ║ Account 1   ║
║ b          ║ Account 2   ║
╚════════════╩═════════════╝

Stat:
╔════════════╦════════════╦═════════════╗
║ accountId  ║ subs       ║ month       ║
╠════════════╬════════════╬═════════════╣
║ a          ║ 313        ║ 2019/01     ║
║ b          ║ 30         ║ 2019/01     ║
║ a          ║ 909        ║ 2019/02     ║
║ a          ║ 100        ║ 2019/03     ║
║ b          ║ 3          ║ 2019/02     ║
╚════════════╩════════════╩═════════════╝

I would like to fetch a single account with it's stats [so that the stats could be limited to certain months for example].

I have Objection.js query:

Account.query()
        .select(['Accounts.*', 'stats.*'])
        .where('Accounts.accountId', accountId)
        .joinRelation('stats')

which prints sql like so:

select "Accounts".*, "stats".* from "Accounts" inner join "Stats" as "stats" on "stats"."accountlId" = "Accounts"."accountId" where "Accounts"."accountId" = 'a'

which results in:

[{
    accountId: a,
    title: 'Account 1',
    subs: 313,
    month: '2019/01',
},{
    accountId: a,
    title: 'Account 1',
    subs: 909,
    month: '2019/02',
},{
    accountId: a,
    title: 'Account 1',
    subs: 100,
    month: '2019/03',
}]

whereas I would like to have the results like so:

{
    accountId: a,
    title: 'Account 1',
    stats: [{
        subs: 313,
        month: '2019/01'
    },{
        subs: 909,
        month: '2019/02'
    },{
        subs: 100,
        month: '2019/03'
    }]
}

I tried using postgres array_agg but I'm not sure if I understood the principal correctly. Is such thing possible from the postgres level or should Objection.js handle this somehow or is the only option to do the mapping manually in javascript after the query?

Artsu · Answer 1 · 2019-01-13T09:39:24.063

0

Banging my head for hours and after posting this, I found one solution using the Objection.js eager method:

Account.query()
    .eager('stats')
    .modifyEager('stats', builder => {
      /*builder.where('month', '2019/01')*/
    })
    .select()
    .where('Accounts.accountId', accountId)
    .first()

The small issue here is that it actually performs two separate queries. I guess there is no way of making this with single query on Objection.js? But is there some way for making use of postgres for this work?

edited Jan 13 '19 at 09:39

answered Jan 12 '19 at 18:37

Artsu

23
6

In your case I suppose you could use `.joinEager` instead of `.eager` http://vincit.github.io/objection.js/#joineager it uses joins instead of multiple queries. Usually multiple queries are faster especially when there is one to many relations fetched eagerly. – Mikael Lepistö Jan 14 '19 at 13:41

Mapping has many relations into array of items either in postgres or objection.js

1 Answers1