Join relation with subquery

[xxzefgh]

Issue type:

[x] question

Database system/driver:

[x] postgres

TypeORM version:

[x] 0.2.0

Steps to reproduce or a small repository showing the problem:

I have the following relation Movie -> Actors:

import {
    Entity,
    Column,
    ManyToMany,
    JoinTable,
} from "typeorm"
import { Actor } from "@app/entity"

@Entity("movies")
export class Movie {
    @Column({ type: "text" })
    title: string

    @ManyToMany(type => Actor, actor => actor.movies)
    @JoinTable({
        name: "movies_actors",
        joinColumn: { name: "movieId" },
        inverseJoinColumn: { name: "actorId" }
    })
    actors: Actor[]
}

with query.leftJoinAndSelect("movie.actors", "actor") I get following query:

SELECT
  "movie"."id" AS "movie_id",
  "movie"."title" AS "movie_title",
  "actors"."id" AS "actors_id",
  "actors"."name" AS "actors_name"
FROM "movies" "movie"
LEFT JOIN "movies_actors" "movie_actors"
  ON "movie_actors"."movieId" = "movie"."id"
LEFT JOIN "actors" "actors"
  ON "actors"."id" = "movie_actors"."actorId"

which works fine. If I want to apply limit/offset to actors, I have to generate query like this:

SELECT
  "movie"."id" AS "movie_id",
  "movie"."title" AS "movie_title",
  "actors"."id" AS "actors_id",
  "actors"."name" AS "actors_name"
FROM "movies" "movie"
LEFT JOIN (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY "movieId") AS "seqId"
    FROM "movies_actors"
  ) "movies_actors"
  WHERE "movies_actors"."seqId" BETWEEN 0 AND 5
) "movie_actors"
  ON "movie_actors"."movieId" = "movie"."id"
LEFT JOIN "actors" "actors"
  ON "actors"."id" = "movie_actors"."actorId"

I'm not sure how can I do this with query builder, if I use subquery in join, it seems I have to manually replicate what TypeORM does with respect to relationship type, join table column names, ON statements etc. in subquery. Is there any other way of doing it, where I have control over select statement inside join, but everything else stays the same?

[pleerock]

no, there are no other ways =(

[xxzefgh]

Thanks for the reply, do you plan exposing something like joinRaw API similar to Knex?

[pleerock]

we probably can do that, what is your use case? Something other then left or inner join?

[xxzefgh]

LATERAL keyword in postgres (not sure about others) is very helpful with subqueries, where you want to access alias from outer scope. It's not specifically for joins, but you do something like JOIN LATERAL ( SELECT ... ). it will simplify my example above to:

...
LEFT JOIN LATERAL (
  SELECT * FROM "movies_actors"
  WHERE "movieId" = "movie"."id"
  LIMIT 5
) "movie_actors"
...

Besides that, It's kinda useful to inject raw SQL for trying things out, debugging, etc.

[xxzefgh]

After trying out different things, it seems that I can't make subquery in join be treated as a relation. Am I missing something?

[pleerock]

subquery in join be treated as a relation

because subquery isnt a relation. When you join post.photos orm simply substitute you a query automatically so you can write less sql. When you use subquery, well... you use another query and there is nothing to substitute

[xxzefgh]

I know it's not, but no matter how query was built, final result set can be treated as such. I've done it with Hibernate, where I'd write entire query with raw SQL and still get correctly mapped entity and it's relations.

For example, I think this query should map photos to users:

createQueryBuilder("user")
    .leftJoinAndMapMany(
        "user.photos",
        qb => qb.from(Photo, "photo"),
        "photo",
        "photo.userId = user.id")
    .geMany();

otherwise, what is the purpose of subquery in leftJoinAndMapMany if it can't be mapped?

[pleerock]

yeah that shall work however for map many "user.photos" is just a property name, it does not care if its a relation or something else. And btw most of times its really better to execute a separate queries and map the data in terms of both performance and code, so I was thinking to deprecate leftJoinAndMapMany method, maybe it will be in the future.

[xxzefgh]

@pleerock I've abstracted over join and result transformer implementations, so I can do what I intended. Only thing that I can't do is lateral joins, can you add support for that?

[pleerock]

I'll left this open. Maybe in the future we will add that

[xxzefgh]

I don't know how you would like API for this to look like, but it's a very simple change
xxzefgh@b970865
I'm using patch-package to add this feature to my project, so I'm fine if you don't want to add it.

[gustavoarielms]

I have the same question. Is this going to be applied in the near future?
Thanks!

[arsanjea]

Is this going to be solved? It would be neat to have and would solve many issues in my code.
Thanks!

[v1d3rm3]

yeah that shall work however for map many "user.photos" is just a property name, it does not care if its a relation or something else. And btw most of times its really better to execute a separate queries and map the data in terms of both performance and code, so I was thinking to deprecate leftJoinAndMapMany method, maybe it will be in the future.

Could you give a little example in this case @pleerock, how i need to execute a separate query to get same result?

[MorisFinkel]

Hope this is what you looking for

@Entity({ name: 'treatment_plan' })
export class Treatment extends BaseEntity {
  @ApiProperty()
  @PrimaryGeneratedColumn()
  id: number

  @ManyToMany(
    type => Patient,
    patient => patient.treatments,
  )
  @JoinTable({
    name: 'treatments_to_patients',
    joinColumn: { name: 'patientId' },
    inverseJoinColumn: { name: 'treatmentId' },
  })
  patients: Patient[]
}

@Entity({ name: 'patients' })
export class Patient extends BaseEntity {
  @ApiProperty()
  @PrimaryGeneratedColumn()
  id: number

  @ManyToMany(
    type => Treatment,
    treatment => treatment.patients,
  )
  treatments: Treatment[]

getTreatment = async (treatmentId): Promise<Treatment> => {
    const query = this.repository.createQueryBuilder();
    query.select('t').from(Treatment, 't')
      .leftJoin('treatments_to_patients', 'ttp', 'ttp.treatmentId = t.id')
      .leftJoinAndMapMany('t.patients', Patient, 'p', 'p.id = ttp.patientId')
      .where('t.id = :treatmentId', {treatmentId});
    const result = await query.getOne()
    console.log(result)
    return result
  }

Also on one of the entities, you can use options { eager: true } but this is not the best approach due to it will try to load all relations when you will request a list

[ahartschen]

yeah that shall work however for map many "user.photos" is just a property name, it does not care if its a relation or something else. And btw most of times its really better to execute a separate queries and map the data in terms of both performance and code, so I was thinking to deprecate leftJoinAndMapMany method, maybe it will be in the future.

Do you have a source on this? Not doubting you, but I'd just like to read more about it.

[onderonur]

yeah that shall work however for map many "user.photos" is just a property name, it does not care if its a relation or something else. And btw most of times its really better to execute a separate queries and map the data in terms of both performance and code, so I was thinking to deprecate leftJoinAndMapMany method, maybe it will be in the future.

So you recommend to execute separate queries and I've read in another issue that you usually load your entities first and execute queries for things like relation counts etc and map that data to properties of your entities. Does this mean multiple db hits to collect the desired data? Or are you talking about creating subqueries and using addSelect etc?

It might be a really silly question. But many people I know just create giant queries with a lot of joins and execute them once. I can't say that is always performant but when there are more than one db hits, many people goes crazy. I mostly work as a front-end developer and don't have too solid knowledge about this kind of stuff. Can you clarify this a little bit more? (Maybe with some examples)

Thanks!

[GaryChangCN]

Anything new ？

[millievn]

Anything new ？

[chanphiromsok]

This One Work For Me

findAlCustomerHaveMembership(option: PaginateParam) {
    const queryBuilder = this.customerRepo
      .createQueryBuilder('cs')
      .select([
        'cs.id AS id',
        'cs.avatar AS avatar',
        'cs.firstname AS firstname',
        'cs.lastname AS lastname',
        'cs.gender AS gender',
        'cs.email AS email',
        'cs.phone AS phone',

        `COALESCE(cbm.name,'NONE') AS membership_type`, // get from subQuery
        'cbm.customer_id',// get from subQuery
        'cbm.is_expired',  // get from subQuery
        'cbm.start_date', // get from subQuery
        'cbm.end_date',// get from subQuery
        'cbm.customer_id', // get from subQuery
      ])
      .leftJoin(
        (sunQuery) =>
          sunQuery
            .select([
              'cbm.customer_id AS customer_id',
              'cbm.end_date AS end_date',
              'cbm.start_date AS start_date',
              'cbm.validity AS validity',
              'mb.name AS name',
            ])
            .addSelect(
              `CASE
            WHEN cbm.end_date >= now() THEN 'Active'
            ELSE 'Inactive'
          END AS is_expired`,
            )
            .from(SaleMembershipEntity, 'cbm')
            .innerJoin('cbm.membership', 'mb', 'mb.id=cbm.customer_id') // JOIN more table that relate with SaleMembershipEntity          

  .orderBy('cbm.end_date', 'DESC')
            .limit(1),
        'cbm',  // alian SaleMembershipEntity to cbm
        'cbm.customer_id=cs.id',
      );

    return from(paginateRaw(queryBuilder, option)).pipe(
      map((customer) => {
        return customer;
      }),
    );
  }