TypeORM findManyToOne queries results in excessive and redundant query execution

Question

TypeORM findManyToOne queries results in excessive and redundant query execution

I am currently working with a database table design structured as follows:

Table Appointments:

id| start_time| patientId |.. and other fields |

And another table known as the Patient table:

id| name | last_name | .. along with other fields |

In my appointment entity, I have defined the following:

@OneToMany(() => AppointmentEntity, (appt) => appt.patient)
appointments: Relation<AppointmentEntity>[];

The goal is to retrieve appointment details along with the patient's first name using only one query when given an appointment id. This is how I achieved it:

 async getAppt(apptId: any) {
    return this.apptRepo.findOne({
      relations: ['patient'],
      where: { id: apptId },
      select: {
        id: true,
        start_time: true
        patient: {
          name: true,
        },
      },
    });
  }

Although this method provides me with the desired outcome, I noticed that it executes two unnecessary database queries instead of just one. The following queries are triggered each time getAppt is called:

query: SELECT DISTINCT "distinctAlias"."AppointmentEntity_id" AS "ids_AppointmentEntity_id" FROM (SELECT "AppointmentEntity"."id" AS "AppointmentEntity_id", "AppointmentEntity"."start_time" AS "AppointmentEntity_start_time", "AppointmentEntity__AppointmentEntity_patient"."name" AS "AppointmentEntity__AppointmentEntity_patient_name" FROM "appointments" "AppointmentEntity" LEFT JOIN "patients" "AppointmentEntity__AppointmentEntity_patient" ON "AppointmentEntity__AppointmentEntity_patient"."id"="AppointmentEntity"."patientId" WHERE ("AppointmentEntity"."id" = $1)) "distinctAlias" ORDER BY "AppointmentEntity_id" ASC LIMIT 1 -- PARAMETERS: ["appt_id_xxx"]
query: SELECT "AppointmentEntity"."id" AS "AppointmentEntity_id", "AppointmentEntity"."start_time" AS "AppointmentEntity_start_time", "AppointmentEntity__AppointmentEntity_patient"."name" AS "AppointmentEntity__AppointmentEntity_patient_name" FROM "appointments" "AppointmentEntity" LEFT JOIN "patients" "AppointmentEntity__AppointmentEntity_patient" ON "AppointmentEntity__AppointmentEntity_patient"."id"="AppointmentEntity"."patientId" WHERE ( ("AppointmentEntity"."id" = $1) ) AND ( "AppointmentEntity"."id" IN ($2) ) -- PARAMETERS: ["appt_id_xxx","appt_id_xxx"

What I truly intended was for the query execution to be streamlined into just one query, similar to the example below:

select b.id, b.start_time, p.name  from appointments b
inner join patients p on p.id = b."patientId" 
where b.id = 'appt_id_xxx';

I prefer not to include aliases "b" and "p", but I require a more efficient approach like the above. The inclusion of distinctAlias and the duplicate queries are perplexing. Any insights on achieving a single query (or equivalent) would be greatly appreciated. Thank you!

javascript typescript typeorm

Answer 1

Answer №1

To understand aliasing in the context of TypeORM, you can refer to the example provided in the library documentation:

Explaining Aliases

In TypeORM, when we use createQueryBuilder("user"), "user" is essentially a SQL alias. Aliases are used extensively, except when dealing with selected data.

createQueryBuilder("user") is equivalent to:
createQueryBuilder().select("user").from(User, "user")
This results in the following SQL query:
SELECT ... FROM users user
Here, "users" is the table name and "user" is the alias assigned to it. This alias is then utilized to access the table as needed:
createQueryBuilder()
    .select("user")
    .from(User, "user")
    .where("user.name = :name", { name: "Timber" })
Generating the SQL query:
SELECT ... FROM users user WHERE user.name = 'Timber'
Multiple aliases can be used within a single query builder. Each select statement can have its own alias, allowing for selection from and joining of multiple tables with their respective aliases. These aliases facilitate accessing the selected data efficiently.

The createQueryBuilder function in TypeORM enables the creation of custom queries.

Based on the library's documentation, it seems that they heavily depend on the usage of left-join. They provide a supporting function called leftJoinAndSelect in conjunction with createQueryBuilder.

For a comprehensive guide, I recommend exploring the documentation on How to create and use a QueryBuilder.

Answer 2

To understand aliasing in the context of TypeORM, you can refer to the example provided in the library documentation:

Explaining Aliases

In TypeORM, when we use createQueryBuilder("user"), "user" is essentially a SQL alias. Aliases are used extensively, except when dealing with selected data.

createQueryBuilder("user") is equivalent to:
createQueryBuilder().select("user").from(User, "user")
This results in the following SQL query:
SELECT ... FROM users user
Here, "users" is the table name and "user" is the alias assigned to it. This alias is then utilized to access the table as needed:
createQueryBuilder()
    .select("user")
    .from(User, "user")
    .where("user.name = :name", { name: "Timber" })
Generating the SQL query:
SELECT ... FROM users user WHERE user.name = 'Timber'
Multiple aliases can be used within a single query builder. Each select statement can have its own alias, allowing for selection from and joining of multiple tables with their respective aliases. These aliases facilitate accessing the selected data efficiently.

The createQueryBuilder function in TypeORM enables the creation of custom queries.

Based on the library's documentation, it seems that they heavily depend on the usage of left-join. They provide a supporting function called leftJoinAndSelect in conjunction with createQueryBuilder.

For a comprehensive guide, I recommend exploring the documentation on How to create and use a QueryBuilder.

Answer 3

Answer №2

When it comes to the concept of eager loading, it appears that configuring it in the query is currently not an option (based on this issue: https://github.com/typeorm/typeorm/issues/7142).

However, you can enable eager loading at the model level by incorporating it into the relationship definition.

@OneToMany(() => AppointmentEntity, (appt) => appt.patient, { eager: true })
appointments: Relation<AppointmentEntity>[];

Just keep in mind that this will result in always eagerly loading the data.

To explore further details, refer to this documentation: https://github.com/typeorm/typeorm/blob/master/docs/eager-and-lazy-relations.md

Answer 4

When it comes to the concept of eager loading, it appears that configuring it in the query is currently not an option (based on this issue: https://github.com/typeorm/typeorm/issues/7142).

However, you can enable eager loading at the model level by incorporating it into the relationship definition.

@OneToMany(() => AppointmentEntity, (appt) => appt.patient, { eager: true })
appointments: Relation<AppointmentEntity>[];

Just keep in mind that this will result in always eagerly loading the data.

To explore further details, refer to this documentation: https://github.com/typeorm/typeorm/blob/master/docs/eager-and-lazy-relations.md

Answer 5

Answer №3

If you are using TypeORM and have the @ManyToOne() property along with the createQueryBuilder to make database requests, it is important to utilize the leftJoinAndSelect() property.

Additionally, when using any properties like findOne or findOneBy, or all find* properties, ensure to set eager: true.

For example:

@OneToMany(() => AppointmentEntity, (appt) => appt.patient, { eager: true })appointments: Relation<AppointmentEntity>[];

I trust that this information will be beneficial to you!

Answer 6

If you are using TypeORM and have the @ManyToOne() property along with the createQueryBuilder to make database requests, it is important to utilize the leftJoinAndSelect() property.

Additionally, when using any properties like findOne or findOneBy, or all find* properties, ensure to set eager: true.

For example:

@OneToMany(() => AppointmentEntity, (appt) => appt.patient, { eager: true })appointments: Relation<AppointmentEntity>[];

I trust that this information will be beneficial to you!

TypeORM findManyToOne queries results in excessive and redundant query execution

Answer №1

Explaining Aliases

Answer №2

Answer №3

Similar questions

Exploring the world of Ember through engaging click events

Creating data types from the name of the route in vue-router route[x]

Steps for downloading a file attached to a gridview

Perform subtraction operation between two arrays of objects in Javascript

What's the best way to convert a Union type into a Tuple with the same number of elements

What is the best way to set a value to null in JavaScript?

Exploring the depths of time travel in Redux without the aid of developer

Can you explain the use of parentheses in a typescript type when defining a key?

Cached images do not trigger the OnLoad event

Using AJAX to handle 404 errors in Slim PHP

Constant variable class

Removing data using axios in a React project

Error in content policy for CSS in Stripe Checkout

How to Generate a Unique URL in Angular 7 Using Typescript

Interactive material design drop-down menu

Is Jquery getting imported correctly, but AJAX is failing to work?

What causes TypeScript to flag spread arguments within callback wrappers?

Is it necessary to reboot the application server after every modification when working with ExpressJS/Node?

Retrieving an image from a JSON file based on its corresponding URL

Issues with Bootstrap Table Column Widths not Adjusting Properly