Select specific columns from left 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!

Schutt · Accepted Answer · 2020-11-11 10:04:10Z

16

+200

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.

answered Nov 11, 2020 at 10:04

Schutt

1,1348 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel Goldberg Over a year ago

This answer is nearly great, but the downside is this requires a .getRawOne or .getRawMany which doesn't provide a parsed entity but rather a simple JSON. Or atleast, not working for me.

Schutt Over a year ago

I think that if you split it into two leftJoin(..) TypeORM should still have no problem to convert the returned rows to entities! I don't think that getRaw..(..) is necessary!

Ali Hussnain - alichampion Over a year ago

Please improve your answer by attaching an example.

Ankit Sharma · Accepted Answer · 2022-04-26 05:13:25Z

12

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']);

answered Apr 26, 2022 at 5:13

Ankit Sharma

1462 silver badges4 bronze badges

Comments

Nitigya Kumar · Accepted Answer · 2022-03-30 11:11:25Z

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
}

Pavel · Accepted Answer · 2020-11-14 17:13:21Z

1

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.

answered Nov 14, 2020 at 17:13

Pavel

1651 silver badge2 bronze badges

Comments

Kevin Potgieter · Accepted Answer · 2020-11-12 09:00:48Z

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

Collectives™ on Stack Overflow

Select specific columns from left join query, TypeORM

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related