Typeorm: select additional computed columns

[D4edalus]

assuming i have some table with a column name, that may be null
in a query i would want to have a computed column "hasName", that is true if the name field is not null

i could do that as follows with the query builder

... createQueryBuilder("person")
.addSelect("name IS NOT NULL as hasName")

this is of course not mapped, and therefore only visible in "getRawMany()" .. but it shows what i want to achieve.

assume now i have a query for orders

... createQueryBuilder("order")
.leftJoinAndSelect("order.person", "person")
.addSelect("person.name IS NOT NULL as person.hasName") // does NOT work

question1: can addSelect be mapped to a custom variable, like it can be done with leftJoinAndMapOne?
question2: is there something like addSelectAndMap?

[pleerock]

no there is no addSelectAndMap feature right now but I had plans to implement it. So its a feature request for the future.

[vincentbouillart]

When will this new feature be present ? can i maybe do a contribution, if you explain how you want it maybe i can do it for your guys ?

[voodooattack]

@pleerock Any news on this? I am also interested in this feature. I can contribute if you point me in the right direction.

[pleerock]

not yet sure about design of this feature yet, but I think it will land somewhere in 0.4.0

[ToonvanStrijp]

For everyone that wants a work around: #1822

[vlapo]

@pleerock Any progress in this feature? Some design, feature spec?

[ekifox]

Update...

[Offlein]

There might be a flaw in my reasoning, but I believe this can be accomplished by specifying extra properties on your entity, and then implementing the @AfterLoad() decoration?

Such as:

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  firstName: string;

  @Column()
  lastName: string;

  fullName: string;

  @AfterLoad()
  setComputed() {
    this.fullName = this.firstName + ' ' + this.lastName;
  }
}

Does that accomplish what people are looking for? Apologies if not.

[dan-h-ch]

So this does work, but I'm not sure it is exactly the same as being able to modify query builder with a addSelectAndMap. It feels a bit much to have to update the entity. For example, I'm trying to create a query to fetch days from expiration. I don't really care for that on the entity outside of this one situation.

But in a bind that works for now.

[nikolovjd]

@pleerock Any updates on addSelectAndMap()?

[siarhei-murkou]

What is a temporary solution to merge joined and aggregated fields? I found only manual mapping using getRawAndEntities or using aggregation functions on database layer to receive mapped field in raw. Does anyone know another ways?

[NarHakobyan]

Any news?

[Asarew]

Would a good solution be to also allow selecting based on the given alias:

@Entity()
class SuperDude {
  @Column({ nullable: true, insert: false, update: false, select: false })
  awsomenessFactor: number;
}

query.where('stuff = "supper cool"')
  .addSelect('Count(id)', `${query.alias}_awsomenessFactor`)

and change this: https://github.com/typeorm/typeorm/blob/7808bba416c0b1cf9850194499e8c8d3502657b7/src/query-builder/transformer/RawSqlResultsToEntityTransformer.ts#L138-L142
to:

// if user does not selected the whole entity or he used partial selection and does not select this particular column
// then we don't add this column and its value into the entity
if (!this.expressionMap.selects.find(select => {
    return select.selection === alias.name 
        || select.selection === alias.name + "." + column.propertyPath 
        || (select.aliasName && select.aliasName === alias.name + "_" + column.propertyPath);
})) {
    return;
}

[TheNoim]

WoW. It is always sad to see an issue about a problem you currently have, which has no progress at all. I encountered this way to often with typeorm.

[duprez]

Any update? I need this feature

[Offlein]

@duprez Just making sure you saw my work-around on Feb 16, if this is holding up your dev.

[duprez]

Hi @Offlein

I'm not sure, i think not, because @AfterLoad() it's called always using queryBuilder. I only need calculate a field based on data relations one time, not always.

A basic example:

Zone.createQueryBuilder('zone')
       .select(['zone.id', 'zone.name'])
       .innerJoin('zone.area', 'area')
       .addSelect('IF(area.id, true, false)', 'hasArea')
       .getOne();

getOne doesn't return the calculated columns. For this i use getRawAndEntities(), but i have to merge entities data with raw data and see all the keys of raw data to find the computed fields (i think this it's not practical and maybe inefficient). Other problem is that the computed value is not converted to orm data type because raw data it's the data stored from query.

I think that calculated columns are basic in ORM or mapping raw data to orm model, but i don't see either options.

This is a basic example that can be fixed with javascript, but orm need this features as sequalize

[Offlein]

@duprez Ah, I understand now. That is tricky, yes. :( Thanks for the explainer; I'm afraid I don't have a better suggestion. :(

[daniel100097]

Any progress?

[ricardobr001]

Any news on this feature?

[Asarew]

@ricardobr001 @daniel100097 @duprez you guys can add your +1 on a pull request that I created that adds this functionality here: #4703

[ricardobr001]

Didn't saw that, going to add +1 =)

[fabioespinosa]

I swear i'm gonna make a party whenever this issue gets solved!
Willing to donate if it does @pleerock ! Thanks for the awesome work 🥇

[xtrinch]

This one is long overdue!

[mubasshir]

+1
_(I can't believe it, it's pending since Feb of 2019. It's almost 3 years now and counting)_

[Asarew]

Please see my comment here on this issue: https://github.com/typeorm/typeorm/pull/4703#issuecomment-594433473.

[BriantOliveira]

Any update on this issue?

[stephanoparaskeva]

getMany(), getOne() and find() methods do not work within @AfterLoad(), is this the same issue, and if it is, why is this not working as such?

[pietrzakadrian]

Any update on this issue?

[nilswx]

@pietrzakadrian: I had to use @RelationCount (deprecated) and loadRelationCountAndMap here, which executes extra queries against the database. Still better than nothing.

Please merge #4703, it'll give users something to work with until selectAndMap is implemented.

[mcfinley]

Really looking to see this issue fixed

[backendsuraj]

Issue opened on Feb 2017 and now its Jul 2020 almost 3+ years and still no sign of this getting implemented plus no tentative date on when version 0.3.0 will be out.

Encountered this problem almost everyday as lot of projects require us to make heavy use of calculated columns.

Hope the maintainers at least provide a tentative date for version 0.3.0.

[ovaldi]

sigh...

[carvalhoviniciusluiz]

@pleerock any updates on addSelectAndMap() , news?

[carvalhoviniciusluiz]

solution that worked!

https://github.com/typeorm/typeorm/issues/1822#issuecomment-573492291

[Overfacto]

solution that worked!

#1822 (comment)

@carvalhoviniciusluiz Well, That answer makes strange database structures. I think database entity architecture should be not followed ORM or some calculations.

It is weird making column for temporary mapping. Maybe better using listeners / subscriber with virtual property.

Oh absolutely best is addSelectAndMap relealse..

[duprez]

Hi guys, please check out my pr(6855) about the commented feature (addSelectAndMap) and feel free to give any feedback

[drummerjolev]

+1, any news on getting this solved for Postgres? @pleerock happy to donate