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
}

Answer 3

Good evening) When you use LEFT JOIN u can't join only specific columns because LEFT JOIN join all fields form left table always. But you can select them.

Answer 4

You have a few syntax errors. Also some of your aliases dont make sense but im sure you have intention for that.

SELECT 
DISTINCT 
  distinctAlias.profile_id as ids_profile_id
FROM (
   SELECT
   profile.id AS profile_id
   --...,
   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 "user" "event" ON event.id = profile.id
    LEFT JOIN user_auth auth ON auth.profile_id=profile.id

    -- join on sport event id here - if you have one
    -- LEFT JOIN sport_events ON ....

   WHERE auth.registered = true
   AND auth.blocked = false
) distinctAlias

ORDER BY profile_id -- ASC is the default
LIMIT 15

Answer 5

Instead of doing leftJoinAndSelect() you could use .leftJoin(property, alias).addSelect([alias.fieldToSelect])

Example: Lets say you have table1.field1 to join to table2.field2, assuming you have written the relation in your entities. You could do,

  const data = await this.table1Repository.createQueryBuilder("t1")
               .leftJoin('t1.relationName', 'alias')
               .addSelect(['alias.fieldYouWantToSelect']);