CODEWITHSUNDEEP
javascriptnode.jsnestjstypeorm
Joseph
Joseph

Reputation: 6269

Select columns from sub-relation not working in typeorm

I have an Order and User Entities

Order

@Entity('orders')
export class Order {
    @PrimaryGeneratedColumn()
    id: number;

    @Column('enum', {enum: OrderStatus, default: OrderStatus.NEW})
    status: OrderStatus
    
    @Column('float')
    amount: number

    @Column('float')
    fees: number

    @ManyToOne(() => User, (user) => user.orders)
    user: User;
    
    @OneToMany(() => OrderToProduct, orderToProduct => orderToProduct.order, {
        cascade: true,
    })
    products: OrderToProduct[]

    @CreateDateColumn()
    createdAt: Date = new Date();

    @UpdateDateColumn()
    updatedAt: Date;
}

User

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

    @Column({length: 255})
    name: string;
    
    @Column({length: 255, unique: true})
    email: string;

    @Column({length: 255})
    password: string;   

    @CreateDateColumn()
    createdAt: Date = new Date();
    
    @OneToMany(() => Order, (order) => order.user)
    orders: Order[];

    @UpdateDateColumn()
    updatedAt: Date;

    @OneToMany(() => Product, (product) => product.user)
    products: Product[]

}

All I want is to retrieve the orders with their user name only what I try is like this

this.orderRepository.find({
    relations: {
        user: {
            id: true,
            name: true
        }
    },
    where: {
        user: {
            id: user.id
        }
    }
})

but it returns with an error

EntityPropertyNotFoundError: Property "id" was not found in "User". Make sure your query is correct.

But if i try to get all the object of user like this it works fine

relations: {
   user: true
},

Upvotes: 5

Views: 8266

Answers (2)

Juan Andres Rodriguez Arenas
Juan Andres Rodriguez Arenas

Reputation: 1

The error is that our queries have fields with the same name as the "id" so you have to add to the select these fields:

this.userRepository.findOne({ 
  where: {  id: res.locals.user  },
  select: { 
    firstName: true,
    lastName: true,
    email: true,
    phone: true,
    createdAt: true,
    updatedAt: true,
    role: { name: true },
  }, 
  relations: { role: true }, 
})
.then(user => res.status(200).json(user))
.catch(( error: MysqlError ) => res.status(500).json([{ message: error.message }]))

[
    {
        "message": "ER_BAD_FIELD_ERROR: Unknown column 'distinctAlias.User_id' in 'field list'"
    }
]

And the correct way to write it is:

this.userRepository.findOne({ 
  where: { id: res.locals.user },
  select: { 
    id: true,
    firstName: true,
    lastName: true,
    email: true,
    phone: true,
    createdAt: true,
    updatedAt: true,
    role: { id: true, name: true },
  }, 
  relations: { role: true }, 
})
.then(user => res.status(200).json(user))
.catch(( error:MysqlError ) => res.status(500).json([{ message: error.message }]))

{
    "id": 1,
    "email": "[email protected]",
    "phone": "3016224924",
    "firstName": "Juan Andres",
    "lastName": "Rodriguez Arenas",
    "createdAt": "2023-01-04T03:49:57.092Z",
    "updatedAt": "2023-01-04T03:49:57.092Z",
    "role": {
        "id": 1,
        "name": "Administrator"
    }
}

Upvotes: 0

linusw
linusw

Reputation: 1310

typeorm expects id and name to be related entities. since they are regular primitive columns, they should be in select option.

relations: {
    user:true
},
where: {
    user: {
        id: user.id
    }
},
select: {
  user: {
    id:true,
    name: true
  }
// your other columns from order entity.
}

Upvotes: 8

Related Questions