Select specific columns from jeft join query, TypeORM

Question

I have a task to get key information about users in database. There are many left joins and the query works quite slow. I'm trying to optimize it somehow and I decided to select only specific fields (only id of a record for the moment).

Here is part of my query. I need to get only id of each event which belongs to a user and do the same for each user in database. Ideally I should get array of ids.

I tried to build a sub query but couldn't find some example with explanation. An example from official docs it's not enough to me.

When I run it I'm getting error

syntax error at or near "SELECT"
QueryFailedError: syntax error at or near "SELECT"

this.createQueryBuilder('profile')
            .leftJoinAndSelect('profile.avatarPhoto', 'avatarPhoto')
            .leftJoinAndSelect('profile.coverPhoto', 'coverPhoto')
            .leftJoinAndSelect('profile.primaryCountry', 'country')
            .leftJoinAndSelect('profile.primaryCity', 'city')
            .leftJoinAndSelect('profile.images', 'image')
            .leftJoinAndSelect('profile.practicedSports', 'practicedSport')
            .leftJoinAndSelect(
                (subQuery) =>
                    subQuery
                        .subQuery()
                        .createQueryBuilder()
                        .select(['id'])
                        .leftJoin('user', 'user')
                        .from(SportEvent, 'event'),
                'event',
                'event.user.id = profile.id',
            )
            // .leftJoinAndSelect('profile.sportServices', 'service')
            // .leftJoinAndSelect('profile.lessons', 'lesson')
            // .leftJoinAndSelect('profile.activityRequests', 'request')
            .leftJoin('profile.userAuth', 'auth')
            .where('auth.registered = true')
            .andWhere('auth.blocked = false')
            .take(params.pageSize)
            .skip(itemsNumber)
            .getMany()

This is generated SQL code

SELECT DISTINCT "distinctAlias"."profile_id" as "ids_profile_id" 
FROM (SELECT "profile"."id" AS "profile_id", "profile"."email" AS "profile_email", 
"profile"."first_name" AS "profile_first_name", 
"profile"."middle_name" AS "profile_middle_name", 
"profile"."last_name" AS "profile_last_name", 
"profile"."about_user" AS "profile_about_user", 
"profile"."interests" AS "profile_interests", 
"profile"."birthday" AS "profile_birthday", 
"profile"."gender" AS "profile_gender", 
"profile"."sport_level" AS "profile_sport_level", 
"profile"."phone_number" AS "profile_phone_number", 
"profile"."contacts" AS "profile_contacts", 
"profile"."payment_methods" AS "profile_payment_methods", 
"profile"."settings" AS "profile_settings", 
"profile"."options" AS "profile_options", 
"profile"."updated_at" AS "profile_updated_at", 
"profile"."created_at" AS "profile_created_at", 
"profile"."avatar_photo_id" AS "profile_avatar_photo_id", 
"profile"."cover_photo_id" AS "profile_cover_photo_id", 
"profile"."cover_video_id" AS "profile_cover_video_id", 
"profile"."default_album_id" AS "profile_default_album_id", 
"profile"."primary_country_id" AS "profile_primary_country_id", 
"profile"."primary_city_id" AS "profile_primary_city_id", 
"profile"."primary_language_id" AS "profile_primary_language_id", 
"avatarPhoto"."id" AS "avatarPhoto_id", 
"avatarPhoto"."name" AS "avatarPhoto_name", 
"avatarPhoto"."image_paths" AS "avatarPhoto_image_paths", 
"avatarPhoto"."updated_at" AS "avatarPhoto_updated_at", 
"avatarPhoto"."created_at" AS "avatarPhoto_created_at", 
"avatarPhoto"."album_id" AS "avatarPhoto_album_id", 
"avatarPhoto"."user_id" AS "avatarPhoto_user_id", 
"coverPhoto"."id" AS "coverPhoto_id", 
"coverPhoto"."name" AS "coverPhoto_name", 
"coverPhoto"."image_paths" AS "coverPhoto_image_paths", 
"coverPhoto"."updated_at" AS "coverPhoto_updated_at", 
"coverPhoto"."created_at" AS "coverPhoto_created_at", 
"coverPhoto"."album_id" AS "coverPhoto_album_id", 
"coverPhoto"."user_id" AS "coverPhoto_user_id", 
"country"."id" AS "country_id", 
"country"."name" AS "country_name", 
"country"."alpha_2" AS "country_alpha_2", 
"country"."alpha_3" AS "country_alpha_3", 
"country"."calling_code" AS "country_calling_code", 
"country"."enabled" AS "country_enabled", 
"country"."top" AS "country_top", "city"."id" AS "city_id", 
"city"."name" AS "city_name", 
"city"."coordinates" AS "city_coordinates", 
"city"."top" AS "city_top", 
"city"."country_id" AS "city_country_id", 
"image"."id" AS "image_id", 
"image"."name" AS "image_name", 
"image"."image_paths" AS "image_image_paths", 
"image"."updated_at" AS "image_updated_at", 
"image"."created_at" AS "image_created_at", 
"image"."album_id" AS "image_album_id", 
"image"."user_id" AS "image_user_id", 
"practicedSport"."id" AS "practicedSport_id", 
"practicedSport"."start_date" AS "practicedSport_start_date", 
"practicedSport"."sport_id" AS "practicedSport_sport_id", 
"practicedSport"."user_id" AS "practicedSport_user_id", 
"event".* 
FROM "user_profiles" "profile" LEFT JOIN "media_images" "avatarPhoto" 
ON "avatarPhoto"."id"="profile"."avatar_photo_id"  LEFT JOIN "media_images" "coverPhoto" 
ON "coverPhoto"."id"="profile"."cover_photo_id"  LEFT JOIN "data_countries" "country" 
ON "country"."id"="profile"."primary_country_id"  LEFT JOIN "data_cities" "city" 
ON "city"."id"="profile"."primary_city_id"  LEFT JOIN "media_images" "image" 
ON "image"."user_id"="profile"."id"  LEFT JOIN "user_practiced_sports" "practicedSport" 
ON "practicedSport"."user_id"="profile"."id"  
LEFT JOIN SELECT id FROM "sport_events" "event" 
LEFT JOIN "user" "user" "event" 
ON event.user.id = "profile"."id"  
LEFT JOIN "user_auth" "auth" 
ON "auth"."profile_id"="profile"."id" 
WHERE "auth"."registered" = true 
AND auth.blocked = false) "distinctAlias" 
ORDER BY "profile_id" ASC LIMIT 15

Could you explain what I'm doing wrong or send me an article with explanation? Thanks!

Answer 1

I think that it is not possible to join on event.user.id you should split it up into two leftJoins. Also make sure to use leftJoin(..) (and then specify the fields you want to select via .select(..) or .addSelect(..)) instead of leftJoinAndSelect(..) as this selects all the fields automatically.

Answer 2

if you want to use join using TypeOrm you can use .leftjoin() instead of leftjoinandSelect()

Here is the example, i have made an example of blood donation api using node and typeorm

//BLOODDONATION.ts

import { Column, Entity,PrimaryColumn } from "typeorm";

@Entity('blood_donation')
export class BloodDonation
{
    
    @PrimaryColumn()
    id:number;

    @Column()
    donor_id:number;

    @Column()
    doctor_id:number;

    @Column()
    quantity:number;

    @OneToOne(() => Donor)
    @JoinColumn({name:'id'})
    donor: Donor;

}

//DONOR.ts

import { Column, Entity, PrimaryColumn } from "typeorm";


@Entity('donor')
export class Donor
{
    
    @PrimaryColumn()
    id:number;

    @Column()
    name:string;

    @Column()
    dob:Date;
    
    @Column()
    date_of_reg:Date;
    
    @Column()
    blood_group:string;

    @Column()
    address:string;

    @Column()
    city:string;

    @Column()
    pincode:string;

}

//BloodDonationController.ts


import { BaseEntity, getRepository } from "typeorm";
import { Request, Response } from 'express';
import { BloodDonation } from "../entities/blood_donation";

class BloodDonationController extends BaseEntity{

    
    static Data = async (req:Request, res:Response)=>
    {
       
        const Header = req.headers;
      
        if(!(Header))
        {
            res.status(400).send();
        }

        const userRepo = getRepository(BloodDonation);

        let object:any = BloodDonation;

        try
        {
             
            object = await userRepo.createQueryBuilder('blood_donation')
            .leftJoin('blood_donation.donor','donor')
            .select(['blood_donation.quantity','donor.name'])
            .getMany();

                res.status(200).json(
            { 
                response:{
                    object
                }, 
                success: true
            });
                return;
         
        }
        catch(error)
        {
            res.status(401).send(error);
        }
    }

}

export default BloodDonationController;

**My Response**


{
    "response": {
        "object": [
            {
                "quantity": 1.2,
                "donor": {
                    "name": "Abhishek"
                }
            },
            {
                "quantity": 1.5,
                "donor": {
                    "name": "Vishwas"
                }
            },
            {
                "quantity": 1.5,
                "donor": {
                    "name": "Gaurav"
                }
            },
            {
                "quantity": 1.8,
                "donor": null
            }
        ]
    },
    "success": true
}