How to query across several relations?

[shtse8]

Assume we have:

A hasMany B
B hasMany C
C hasOne D

Now we have A object. And I want to query D with limit 10 using innerJoin. something like:

let ds = a.$relatedQuery('bs.cs.d').limit(10)

But it doesn't work. How can I do it? Thanks.

ATM, here is my best try:

let cs = await a.$relatedQuery('bs')
  .joinRelation('cs.d')
  .eager('cs.d')
  .offset(offset)
  .limit(limit)
  .pluck('cs')
let ds = []
for (let c of cs) {
  ds.push(c[0].d)
}
console.log(ds)

[koskimas]

Assume that particular A has 10 B's and each B has 10 C's and each C has one D then the the query:

let cs = await A.query()
  .findById(a.id)
  .joinRelation('bs.cs.d')

will have 100 rows. Offsetting and limiting will not work as you'd expect (maybe ?).

Why are you joining here? You are not filtering on the joined rows in any way? Would eager be enough? Leaving out joinRelation alltogether should work.

[koskimas]

Maybe this is what you want?

let ds = await A.query()
  .findById(a.id)
  .select('bs:cs:d.*')
  .joinRelation('bs.cs.d')

that should kind of work. It will return instances of A, but with D's fields. I should actually implement a feature that lets you select the model class of the result rows.

[shtse8]

As I am writing methods in the A model, so i would expect to query D directly using A relations. I think ORM should all depend on relation instead of querying from the top model class using static methods. in the current implementation, it's hard to query the cross relations model directly and applying limit and offset there. I have to query all B and the all C and then all D step by step. whould there be a better way in the future to accomplish this?

Here is my try to make a getDs in A model:

class A extend Model {
  getDs ({ offset = 0, limit = 10 }) {
    let bs = await pool.$relatedQuery('bs')
      .joinRelation('cs.d')
      .eager('cs.d')
      .offset(offset)
      .limit(limit)
    let ds = []
    for (let b of bs) {
      for (let c of b.cs) {
        ds.push(c.d)
      }
    }
    return ds
  }
}

But I would expect something like this:

class A extend Model {
  getDs ({ offset = 0, limit = 10 }) {
    let ds = await pool.$relatedQuery('bs')
      .joinRelation('cs.d')
      .eager('cs.d')
      .offset(offset)
      .limit(limit)
      .pluck('cd.d')
    return ds
  }
}

or even simplier:

class A extend Model {
  getDs ({ offset = 0, limit = 10 }) {
    let ds = await this.$relatedQuery('bs.cs.d')
      .offset(offset)
      .limit(limit)
    return ds
  }
}

[koskimas]

What is pool in your examples? $relatedQuery doesn't take relation expressions, but single relations. You didn't read my messages right?

Objection is not an ORM, its a query builder. Most of the time you write SQL. You should use the .debug method to see what SQL gets generated.

Unfortunately I don't understand your question and cannot help you with it. I don' really know what you are trying to achieve. Sorry.

[koskimas]

Since I don't know what you want, here's a couple of suggestions you can try out. Maybe one of them has the results you want.

    // With this one you need to understand what an SQL join means.
    // The result set will be the cartesian product of the relations.
    let ds = await this.$relatedQuery('bs')
      .joinRelation('cs.d')
      .select('cs:d.*')
      // Eager won't work here since it will load relations for
      // all the numB * numC * numD rows.
      .offset(offset)
      .limit(limit);

    // This will limit and offset Bs and not Ds.
    let bs = await this.$loadRelated('bs.cs.d')
      .offset(offset)
      .limit(limit);

  let ds = bs.map(b => b.cs.map(...)))

[shtse8]

@koskimas For my previous comment, pool should be this, just a typo mistake. And for your suggest, the first approach is what I want. I will try that, thanks. Objectionjs is the best tool I have used.

[shtse8]

@koskimas I have tried select, it's working fine to get my expected result, but I noticed that the result json will be parsed using A.$parseDatabaseJson instead of using D.$parseDatabaseJson, so my attributes conversion will be failed. Also, the final result of ds is the list of A model with d attributes instead of D model. Any ideas to fix this? (I understand what you mean using this approach now, any plan to support a feature to let us select a model class of result rows?)

let bs = await pool.$relatedQuery('bs')
  .joinRelation('cs.d')
  .select('')
  .eager('cs(selectNothing).d', {
    selectNothing: (builder) => {
      builder.select('')
    }
  })
  .offset(offset)
  .limit(limit)
let ds = []
for (let b of bs) {
  for (let c of b.cs) {
    ds.push(c.d)
  }
}

this code is working as I expect. I don't understand why you say eager won't work here. I understand what an SQL join means. As I want to get ds, I need to join the relation to ensure d must exist in b. anyway, it means nothing and won't get the d for my need, so I need to use eager to the certain d model. If I don't use eager, the d model cannot be returned. and all data is merged to b model. The above code is my best try and working as I expect, but I can't find any better way to simplier it.

The is the expected SQL:

SELECT `d`.* 
FROM  `B` as `b`
  INNER JOIN `C` as `c`
  ON `c`.`bId` = `b`.`id`
  INNER JOIN `D` as `d`
  ON `d`.`cId` = `c`.`id`
WHERE
  `b`.`aId` = '${this.id}'
LIMIT 0, 10

I need the result is in d instances

[shtse8]

I got your point, the eager here is some wired. the cs are returned but the result set is correct, I need to filter out those c.d is null.

I have tried to modify the sub query builder as follow:

this.$relatedQuery('bs')
  .select('')
  .joinRelation('cs.d')
  .eager('cs(cJoin, selectNothing).d(dOrderBy)', {
    cJoin: (builder) => {
      builder.joinRelation('d')
    },
    selectNothing: (builder) => {
      builder.select('')
    },
    dOrderBy: (builder) => {
      builder.orderBy('createdAt', 'desc')
    }
  })