NodeJS 从左联接查询TypeORM中选择特定列

uidvcgyl 于 2022-12-22 发布在 Node.js

关注(0)|答案(5)|浏览(525)

我有一个任务是获取数据库中用户的关键信息。有很多left joins，查询工作相当慢。我试图以某种方式优化它，我决定只选择特定的字段（目前只选择记录的id）。
这是我的查询的一部分。我只需要得到属于一个用户的每个事件的id，然后对数据库中的每个用户做同样的事情。理想情况下，我应该得到一个id数组。
我试着建立一个子查询，但找不到一些例子与解释。一个例子，从官方文件，这是不够的。
当我运行它时，我得到错误

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()

这是生成的SQL代码

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

你能解释一下我做错了什么吗？或者给我发一篇解释的文章？谢谢!

node.js

来源：https://stackoverflow.com/questions/62390886/select-specific-columns-from-left-join-query-typeorm

5条答案

按热度按时间

ngynwnxp1#

我认为在event.user.id上进行join是不可能的，您应该将其拆分为两个leftJoin。还要确保使用leftJoin(..)（然后通过.select(..)或.addSelect(..)指定您想要选择的字段）而不是leftJoinAndSelect(..)，因为它会自动选择所有字段。

赞(0）回复(0）举报 2022-12-22

5ssjco0h2#

如果您想使用TypeOrm连接，可以使用. leftjoin（）代替leftjoinandSelect（）
下面是一个例子，我用node和typeorm做了一个献血api的例子

//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
}

赞(0）回复(0）举报 2022-12-22

rvpgvaaj3#

您可以使用.leftJoin（属性，别名）.addSelect（[别名.fieldToSelect]）来代替执行leftJoinAndSelect（）
示例：假设您有table1.field1要连接到table2.field2，假设您已经在实体中编写了关系。

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

赞(0）回复(0）举报 2022-12-22

8hhllhi24#

晚上好）当你使用LEFT JOIN时，你不能只连接特定的列，因为LEFT JOIN总是连接左表中的所有字段。但是你可以选择它们。

赞(0）回复(0）举报 2022-12-22

vfwfrxfs5#

你有一些语法错误。而且你的一些别名没有意义，但我相信你有这样的意图。

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

赞(0）回复(0）举报 2022-12-22

我来回答

NodeJS 从左联接查询TypeORM中选择特定列

5条答案

相关问题

热门标签

最新问答