J'ai pour tâche d'obtenir des informations clés sur les utilisateurs de la base de données. Il existe de nombreuses left joins et la requête fonctionne assez lentement. J'essaye de l'optimiser d'une manière ou d'une autre et j'ai décidé de ne sélectionner que des champs spécifiques (uniquement l'identifiant d'un enregistrement pour le moment).
Voici une partie de ma requête. Je dois obtenir uniquement l'identifiant de chaque événement qui appartient à un utilisateur et faire de même pour chaque utilisateur de la base de données. Idéalement, je devrais obtenir un tableau d'identifiants.
J'ai essayé de créer une sous-requête mais je n'ai pas trouvé d'exemple avec une explication. Un exemple tiré de la documentation officielle ne me suffit pas.
Quand je l'exécute, j'obtiens une erreur
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
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()
Ceci est un code SQL généré
syntax error at or near "SELECT" QueryFailedError: syntax error at or near "SELECT"
Pouvez-vous m'expliquer ce que je fais mal ou m'envoyer un article avec des explications? Merci!
3 Réponses :
Je pense qu'il n'est pas possible de rejoindre event.user.id vous devez le diviser en deux leftJoins. Assurez-vous également d'utiliser leftJoin(..) (puis spécifiez les champs que vous voulez sélectionner via .select(..) ou .addSelect(..) ) au lieu de leftJoinAndSelect(..) car cela sélectionne automatiquement tous les champs.
Cette réponse est presque excellente, mais l'inconvénient est que cela nécessite un .getRawOne ou .getRawMany qui ne fournit pas d'entité analysée mais plutôt un simple JSON. Ou au moins, ne fonctionne pas pour moi.
Je pense que si vous le leftJoin(..) en deux leftJoin(..) TypeORM ne devrait toujours avoir aucun problème pour convertir les lignes renvoyées en entités! Je ne pense pas que getRaw..(..) soit nécessaire!
Vous avez quelques erreurs de syntaxe. Certains de vos alias n'ont pas de sens, mais je suis sûr que vous en avez l'intention.
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
Bonsoir) Lorsque vous utilisez LEFT JOIN, vous ne pouvez pas joindre uniquement des colonnes spécifiques car LEFT JOIN joint toujours tous les champs du tableau de gauche. Mais vous pouvez les sélectionner.