withGraphJoined doesn't return raw selected columns

[pigpudle]

I found that if I use withGraphJoined + modifiers with raw columns, the objection doesn't return raw columns.

Consider example:

Price is 'BelongsToOneRelation' (with deletedAt=null filter) from Product model.

This query works fine:

const { raw } = require('objection');

const result = await Product.findOne({
    priceId: 1
})
.withGraphJoined('[price(priceDetails)]')
.modifiers({
    priceDetails: (query) => {
                query.select(
                    'id',
                    'token' // <-- this column is returned in result.price.token
                );
            },
});
console.log(result);

This query doesn't return 'tokenLast4' column:

const { raw } = require('objection');

const result = await Product.findOne({
    priceId: 1
})
.withGraphJoined('[price(priceDetails)]')
.modifiers({
    priceDetails: (query) => {
                query.select(
                    'id',
                    raw('RIGHT("token" , 4) AS "tokenLast4"'), // <-- this column isn't returned in the result.price.tokenLast4. result.price only has 'id' in it
                );
            },
});
console.log(result);

I checked the resulting SQL query: it returns 'tokenLast4' in the subquery, but this column isn't selected in the main query like this:

select
# products fields selected here
"price"."id" as "price:id" # <--- there also should be "price"."tokenLast4" as "price:tokenLast4", but it's not selected in the main query
from "products"
left join (
    select "id",
    RIGHT("token" , 4) AS "tokenLast4" # <-- 'tokenLast4' is selected in the subquery
    from "prices"
    where "deletedAt" is null
) as "price" on "price"."id" = "products"."priceId"

I also tried to select this raw column with withGraphFetched and modifyGraph and the result is the same.
I haven't found any limitations pointed in the docs about this case.

[rubenpad]

Hi @pigpudle - I have noticed that adding the alias using the as function will return the column from the subquery. e.g:

const { raw } = require('objection');

const result = await Product.findOne({ priceId: 1 })
  .withGraphJoined('[price(priceDetails)]')
  .modifiers({
    priceDetails: (query) => {
                query.select('id', raw('RIGHT("token", 4)').as('tokenLast4'));
    }});

This will generate the next sql:

select
# products fields selected here
"price"."id" as "price:id",
"price"."tokenLast4" as "price:tokenLast4"
from "products"
left join (
    select "id",
    RIGHT("token" , 4) AS "tokenLast4"
    from "prices"
    where "deletedAt" is null
) as "price" on "price"."id" = "products"."priceId"

[pigpudle]

@rubenpad Hi! Thank you! Yeah, it works

But today I've also found the same issue but with non-raw subqueries inside modifiers.
Subqueries like this work perfectly:

const result = await Product.findOne({ priceId: 1 })
  .withGraphJoined('[price(priceDetails)]')
  .modifiers({
    priceDetails: (query) => {
                query.select(
                'id',
                raw(`   // <-- raw() variant works fine!
                    select amount from "priceVersions"
                    where "priceId" = "prices"."id"
                    limit 1
                  `).as("amount")  // <- Successfully returned
                );
    }});
result.price.amount // <- Successfully returned

But if I try the same subquery but with model , it doesn't return the amount field:

const result = await Product.findOne({ priceId: 1 })
  .withGraphJoined('[price(priceDetails)]')
  .modifiers({
    priceDetails: (query) => {
                query.select(
                'id',
                PriceVersions.query()  // <- The variant with model doesn't work
                    .select('amount')
                   .findOne({ priceId: ref('prices.id') })
                   .as("amount") // <- Not returned from the parent query
               );
    }});
result.price.amount // <- Is undefined

Although it's recommended in the official docs (https://vincit.github.io/objection.js/recipes/subqueries.html) and it works fine outside of modifiers, it doesn't work inside the modifiers.

When looking at the generated query string I can see the same problem as in this issue description -> that amount column isn't selected in the parent query. And using .as() doesn't help. @rubenpad do you know how to fix this?

[rubenpad]

Hi @pigpudle - I think this is not supported right now for the modifiers.

Any specific reason you want to use the model for the select? You could use other approaches. Once I get some time, I'll provide some examples.