Search code examples
postgresqltypeormnestjs-swagger

TypeOrm Joins without using QueryBuilder


I have 3 tables

  1. attendance
  2. user
  3. attendance_verification.

attendance->ManyToOne->user

attendance->OneToOne->attendanceVerification

now I want to query attendance for a specific user and want to embed complete user and attendanceVerification data in attendance object and return it. This is what I have tried but it is giving me error Invalid syntax near where.

I have tried

 const listOfAttendance = await this.attendanceRepository.find({
      where: {
        punchInDateTime: Between(dateFrom, dateTo),
      },
      order: {
        id: 'ASC',
      },
      join: {
        alias: 'attendance',
        leftJoinAndSelect: {
          user: 'attendance.user',
          outlet: 'attendance.outlet',
          punchAck: 'attendance.punchAck',
          verification: 'attendance.attendanceVerification',
        },
      },
    })

Query being parsed by TypeORM

        SELECT
  "attendance"."id" AS "attendance_id",
  "attendance"."punchInImage" AS "attendance_punchInImage",
  "attendance"."punchInDateTime" AS "attendance_punchInDateTime",
  "attendance"."punchInComment" AS "attendance_punchInComment",
  "attendance"."punchOutDateTime" AS "attendance_punchOutDateTime",
  "attendance"."punchOutComment" AS "attendance_punchOutComment",
  "attendance"."punchOutImage" AS "attendance_punchOutImage",
  "attendance"."status" AS "attendance_status",
  "attendance"."approvalStatus" AS "attendance_approvalStatus",
  "attendance"."userId" AS "attendance_userId",
  "attendance"."outletId" AS "attendance_outletId",
  "attendance"."createdAt" AS "attendance_createdAt",
  "user"."id" AS "user_id",
  "user"."firstName" AS "user_firstName",
  "user"."lastName" AS "user_lastName",
  "user"."userName" AS "user_userName",
  "user"."contact" AS "user_contact",
  "user"."gender" AS "user_gender",
  "user"."dob" AS "user_dob",
  "user"."country" AS "user_country",
  "user"."state" AS "user_state",
  "user"."postalCode" AS "user_postalCode",
  "user"."isAdmin" AS "user_isAdmin",
  "user"."isEmployee" AS "user_isEmployee",
  "user"."byEmail" AS "user_byEmail",
  "user"."byContact" AS "user_byContact",
  "user"."address" AS "user_address",
  "user"."email" AS "user_email",
  "user"."orgId" AS "user_orgId",
  "user"."password" AS "user_password",
  "user"."salt" AS "user_salt",
  "user"."createdBy" AS "user_createdBy",
  "user"."status" AS "user_status",
  "user"."apiAccessKey" AS "user_apiAccessKey",
  "user"."createdAt" AS "user_createdAt",
  "user"."metaId" AS "user_metaId",
  "outlet"."id" AS "outlet_id",
  "outlet"."name" AS "outlet_name",
  "outlet"."comment" AS "outlet_comment",
  "outlet"."location" AS "outlet_location",
  "outlet"."status" AS "outlet_status",
  "outlet"."orgId" AS "outlet_orgId",
  "outlet"."validated" AS "outlet_validated",
  "punchAck"."id" AS "punchAck_id",
  "punchAck"."isPunchedIn" AS "punchAck_isPunchedIn",
  "punchAck"."isAck" AS "punchAck_isAck",
  "punchAck"."userId" AS "punchAck_userId",
  "punchAck"."dateTime" AS "punchAck_dateTime",
  "punchAck"."rejectComment" AS "punchAck_rejectComment",
  "punchAck"."attendanceId" AS "punchAck_attendanceId",
  "verification"."id" AS "verification_id",
  "verification"."status" AS "verification_status",
  "verification"."punchIn" AS "verification_punchIn",
  "verification"."punchOut" AS "verification_punchOut",
  "verification"."supervisorId" AS "verification_supervisorId",
  "verification"."attendanceId" AS "verification_attendanceId",
  "verification"."comment" AS "verification_comment",
  "verification"."createdAt" AS "verification_createdAt"
FROM
  "tenant"."attendance" "attendance"
  LEFT JOIN "tenant"."users" "user" ON "user"."id" = "attendance"."userId"
  LEFT JOIN "tenant"."outlet" "outlet" ON "outlet"."id" = "attendance"."outletId"
  LEFT JOIN "tenant"."punch_ack" "punchAck" ON "punchAck"."attendanceId" = "attendance"."id"
  LEFT JOIN "tenant"."attendance_verification" "verification" ON
WHERE
  "attendance"."punchInDateTime" BETWEEN $ 1
  AND $ 2
ORDER BY
  "attendance"."id" ASC
        PARAMETERS:[
  "2021-02-28T19:00:00.000Z",
  "2021-03-31T18:59:59.000Z"
]

AttendanceEntity (Exluded some fields to make code clean)

import { ApiPropertyOptional } from '@nestjs/swagger'
import {
  BaseEntity,
  Column,
  Entity,
  ManyToOne,
  OneToMany,
  OneToOne,
  PrimaryGeneratedColumn,
} from 'typeorm'
import { AttendanceVerification } from '@modules/attendance/attendance-verification/attendance_verification.entity'
import { Break } from '@modules/attendance/break/break.entity'
import { PunchAck } from '@modules/attendance/punch-ack/punch_ack.entity'
import { Outlet } from '@modules/outlet/outlet.entity'
import { User } from '@modules/users/users.entity'

@Entity('attendance')
export class Attendance extends BaseEntity {
  @ApiPropertyOptional()
  @PrimaryGeneratedColumn()
  id: number

  @ApiPropertyOptional()
  @Column()
  punchInImage: string

  @ApiPropertyOptional()
  @Column({ type: 'timestamp' })
  punchInDateTime: Date

  @ApiPropertyOptional()
  @Column()
  approvalStatus: string

  @ApiPropertyOptional()
  @Column()
  userId: number

  @ApiPropertyOptional()
  @Column()
  outletId: number

  @ManyToOne(type => User, user => user.attendance)
  user: User

  @OneToOne(type => Outlet, outlet => outlet.attendance)
  outlet: Outlet

  @OneToMany(type => Break, breaks => breaks.attendance)
  breaks: Break[]

  @OneToMany(type => PunchAck, punchAck => punchAck.attendance)
  punchAck: PunchAck[]

  @OneToOne(
    type => AttendanceVerification,
    attendanceVerification => attendanceVerification.attendance
  )
  attendanceVerification: AttendanceVerification
}

All solutions I find on internet uses queryBuilder. I don't want to use that lengthy method.


Solution

  • From typeorm docs: https://typeorm.io/#/find-options You can use something like:

    userRepository.find({
        join: {
            alias: "user",
            leftJoinAndSelect: {
                profile: "user.profile",
                photo: "user.photos",
                video: "user.videos"
            }
        }
    });
    

    Or your example may look like:

    const returnedResult = await this.attendanceRepository.findOne({
          where: { userId: id, status: 'PUNCHED IN' },
          join: {
             alias: "attendance",
             leftJoinAndSelect: {
                user: "attendance.user",
                outlet: "attendance.outlet",
            }
          },
        });
    

    From my personal experience if you'd need to write complex queries you have to work with QueryBuilder. It looks awkward on the first glance only.