import "reflect-metadata";
import {expect} from "chai";
import {closeTestingConnections, createTestingConnections, reloadTestingDatabases} from "../../../utils/test-utils";
import {Connection} from "../../../../src/connection/Connection";
import {Tag} from "./entity/Tag";
import {Post} from "./entity/Post";
import {Category} from "./entity/Category";
import {Image} from "./entity/Image";
import {User} from "./entity/User";

describe("query builder > joins", () => {
    
    let connections: Connection[];
    before(async () => connections = await createTestingConnections({
        entities: [__dirname + "/entity/*{.js,.ts}"],
    }));
    beforeEach(() => reloadTestingDatabases(connections));
    after(() => closeTestingConnections(connections));

    describe("leftJoinAndSelect", () => {

        it("should load data for all relation types", () => Promise.all(connections.map(async connection => {

            const user = new User();
            user.name = "Alex Messer";
            await connection.manager.save(user);

            const tag = new Tag();
            tag.name = "audi";
            await connection.manager.save(tag);

            const image1 = new Image();
            image1.name = "image1";
            await connection.manager.save(image1);

            const image2 = new Image();
            image2.name = "image2";
            await connection.manager.save(image2);

            const image3 = new Image();
            image3.name = "image3";
            await connection.manager.save(image3);

            const category1 = new Category();
            category1.name = "cars";
            category1.images = [image1, image2];
            await connection.manager.save(category1);

            const category2 = new Category();
            category2.name = "germany";
            await connection.manager.save(category2);

            const category3 = new Category();
            category3.name = "airplanes";
            category3.images = [image3];
            await connection.manager.save(category3);

            const post1 = new Post();
            post1.title = "about BMW";
            post1.categories = [category1, category2];
            post1.tag = tag;
            post1.author = user;
            await connection.manager.save(post1);

            const post2 = new Post();
            post2.title = "about Boeing";
            post2.categories = [category3];
            await connection.manager.save(post2);

            const loadedPosts = await connection.manager
                .createQueryBuilder(Post, "post")
                .leftJoinAndSelect("post.tag", "tag")
                .leftJoinAndSelect("post.author", "author")
                .leftJoinAndSelect("post.categories", "categories")
                .leftJoinAndSelect("categories.images", "images")
                .getMany();

            expect(loadedPosts![0].tag).to.not.be.empty;
            expect(loadedPosts![0].tag.id).to.be.equal(1);
            expect(loadedPosts![0].categories).to.not.be.empty;
            expect(loadedPosts![0].categories.length).to.be.equal(2);
            expect(loadedPosts![0].categories[0].images).to.not.be.empty;
            expect(loadedPosts![0].categories[0].images.length).to.be.equal(2);
            expect(loadedPosts![0].categories[0].images.map(image => image.id)).to.have.members([1, 2]);
            expect(loadedPosts![0].author).to.not.be.empty;
            expect(loadedPosts![0].author.id).to.be.equal(1);
            expect(loadedPosts![1].categories).to.not.be.empty;
            expect(loadedPosts![1].categories.length).to.be.equal(1);
            expect(loadedPosts![1].categories[0].images).to.not.be.empty;
            expect(loadedPosts![1].categories[0].images.length).to.be.equal(1);
            expect(loadedPosts![1].categories[0].images[0].id).to.be.equal(3);

            const loadedPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .leftJoinAndSelect("post.tag", "tag")
                .leftJoinAndSelect("post.author", "author")
                .leftJoinAndSelect("post.categories", "categories")
                .leftJoinAndSelect("categories.images", "images")
                .where("post.id = :id", { id: 1 })
                .getOne();

            expect(loadedPost!.tag).to.not.be.empty;
            expect(loadedPost!.tag instanceof Tag).to.be.true;
            expect(loadedPost!.tag.id).to.be.equal(1);
            expect(loadedPost!.categories).to.not.be.empty;
            expect(loadedPost!.categories.length).to.be.equal(2);
            expect(loadedPost!.categories[0] instanceof Category).to.be.true;
            expect(loadedPost!.categories[0].id).to.be.equal(1);
            expect(loadedPost!.categories[1].id).to.be.equal(2);
            expect(loadedPost!.categories[0].images[0] instanceof Image).to.be.true;
            expect(loadedPost!.categories[0].images).to.not.be.empty;
            expect(loadedPost!.categories[0].images.length).to.be.equal(2);
            expect(loadedPost!.categories[0].images.map(image => image.id)).to.have.members([1, 2]);
            expect(loadedPost!.categories[1].images).to.be.empty;
            expect(loadedPost!.author).to.not.be.empty;
            expect(loadedPost!.author instanceof User).to.be.true;
            expect(loadedPost!.author.id).to.be.equal(1);

        })));

        it("should load data when additional condition used", () => Promise.all(connections.map(async connection => {

            const image1 = new Image();
            image1.name = "image1";
            await connection.manager.save(image1);

            const image2 = new Image();
            image2.name = "image2";
            await connection.manager.save(image2);

            const category1 = new Category();
            category1.name = "cars";
            category1.images = [image1, image2];
            await connection.manager.save(category1);

            const category2 = new Category();
            category2.name = "germany";
            await connection.manager.save(category2);

            const post = new Post();
            post.title = "about BMW";
            post.categories = [category1, category2];
            await connection.manager.save(post);

            const loadedPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .leftJoinAndSelect("post.categories", "categories", "categories.id = :categoryId")
                .leftJoinAndSelect("categories.images", "images", "images.id = :imageId")
                .where("post.id = :id", { id: post.id })
                .setParameters({ categoryId: 1, imageId: 2 })
                .getOne();

            expect(loadedPost!.categories).to.not.be.empty;
            expect(loadedPost!.categories.length).to.be.equal(1);
            expect(loadedPost!.categories[0].id).to.be.equal(1);
            expect(loadedPost!.categories[0].images).to.not.be.empty;
            expect(loadedPost!.categories[0].images.length).to.be.equal(1);
            expect(loadedPost!.categories[0].images[0].id).to.be.equal(2);

        })));

        it("should load data when join tables does not have direct relation", () => Promise.all(connections.map(async connection => {

            const category = new Category();
            category.name = "cars";
            await connection.manager.save(category);

            const post = new Post();
            post.title = "about BMW";
            post.categories = [category];
            await connection.manager.save(post);

            const loadedRawPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .leftJoinAndSelect("post_categories_category", "categoriesJunction", "categoriesJunction.postId = post.id")
                .leftJoinAndSelect(Category, "categories", "categories.id = categoriesJunction.categoryId")
                .where("post.id = :id", { id: post.id })
                .getRawOne();

            expect(loadedRawPost!["categories_id"]).to.be.equal(1);

        })));

    });

    describe("innerJoinAndSelect", () => {

        it("should load only exist data for all relation types", () => Promise.all(connections.map(async connection => {

            const user = new User();
            user.name = "Alex Messer";
            await connection.manager.save(user);

            const tag = new Tag();
            tag.name = "audi";
            await connection.manager.save(tag);

            const image1 = new Image();
            image1.name = "image1";
            await connection.manager.save(image1);

            const image2 = new Image();
            image2.name = "image2";
            await connection.manager.save(image2);

            const category1 = new Category();
            category1.name = "cars";
            category1.images = [image1, image2];
            await connection.manager.save(category1);

            const category2 = new Category();
            category2.name = "germany";
            await connection.manager.save(category2);

            const post = new Post();
            post.title = "about BMW";
            post.categories = [category1, category2];
            post.tag = tag;
            post.author = user;
            await connection.manager.save(post);

            const loadedPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .innerJoinAndSelect("post.tag", "tag")
                .innerJoinAndSelect("post.author", "author")
                .innerJoinAndSelect("post.categories", "categories")
                .innerJoinAndSelect("categories.images", "images")
                .where("post.id = :id", { id: post.id })
                .getOne();

            expect(loadedPost!.tag).to.not.be.empty;
            expect(loadedPost!.tag.id).to.be.equal(1);
            expect(loadedPost!.categories).to.not.be.empty;
            expect(loadedPost!.categories.length).to.be.equal(1);
            expect(loadedPost!.categories[0].images).to.not.be.empty;
            expect(loadedPost!.categories[0].images.length).to.be.equal(2);
            expect(loadedPost!.author).to.not.be.empty;
            expect(loadedPost!.author.id).to.be.equal(1);

        })));

        it("should load data when additional condition used", () => Promise.all(connections.map(async connection => {

            const image1 = new Image();
            image1.name = "image1";
            await connection.manager.save(image1);

            const image2 = new Image();
            image2.name = "image2";
            await connection.manager.save(image2);

            const category1 = new Category();
            category1.name = "cars";
            category1.images = [image1, image2];
            await connection.manager.save(category1);

            const category2 = new Category();
            category2.name = "germany";
            await connection.manager.save(category2);

            const post = new Post();
            post.title = "about BMW";
            post.categories = [category1, category2];
            await connection.manager.save(post);

            const loadedPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .innerJoinAndSelect("post.categories", "categories", "categories.id = :categoryId")
                .innerJoinAndSelect("categories.images", "images", "images.id = :imageId")
                .where("post.id = :id", { id: post.id })
                .setParameters({ categoryId: 1, imageId: 2 })
                .getOne();

            expect(loadedPost!.categories).to.not.be.empty;
            expect(loadedPost!.categories.length).to.be.equal(1);
            expect(loadedPost!.categories[0].id).to.be.equal(1);
            expect(loadedPost!.categories[0].images).to.not.be.empty;
            expect(loadedPost!.categories[0].images.length).to.be.equal(1);
            expect(loadedPost!.categories[0].images[0].id).to.be.equal(2);

        })));

        it("should not return any result when related data does not exist", () => Promise.all(connections.map(async connection => {

            const post = new Post();
            post.title = "about BMW";
            await connection.manager.save(post);

            const loadedPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .innerJoinAndSelect("post.tag", "tag")
                .where("post.id = :id", { id: post.id })
                .getOne();

            expect(loadedPost!).to.be.empty;

        })));

    });

    describe("leftJoinAndMap", () => {

        it("should load and map selected data when entity used as join argument", () => Promise.all(connections.map(async connection => {

            const user = new User();
            user.name = "Alex Messer";
            await connection.manager.save(user);

            const tag = new Tag();
            tag.name = "audi";
            await connection.manager.save(tag);

            const image1 = new Image();
            image1.name = "image1";
            await connection.manager.save(image1);

            const image2 = new Image();
            image2.name = "image2";
            await connection.manager.save(image2);

            const category1 = new Category();
            category1.name = "cars";
            await connection.manager.save(category1);

            const category2 = new Category();
            category2.name = "germany";
            await connection.manager.save(category2);

            const post = new Post();
            post.title = "about BMW";
            post.tag = tag;
            post.author = user;
            await connection.manager.save(post);

            const loadedPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .leftJoinAndMapOne("post.tag", Tag, "tag", "tag.id = :tagId")
                .leftJoinAndMapOne("post.author", User, "user", "user.id = :userId")
                .leftJoinAndMapMany("post.categories", Category, "categories", "categories.id IN (:...categoryIds)")
                .leftJoinAndMapMany("categories.images", Image, "image", "image.id IN (:...imageIds)")
                .where("post.id = :id", { id: post.id })
                .setParameters({ tagId: 1, userId: 1, categoryIds: [1, 2], imageIds: [1, 2] })
                .getOne();

            expect(loadedPost!.tag).to.not.be.empty;
            expect(loadedPost!.tag.id).to.be.equal(1);
            expect(loadedPost!.categories).to.not.be.empty;
            expect(loadedPost!.categories.length).to.be.equal(2);
            expect(loadedPost!.categories[0].images).to.not.be.empty;
            expect(loadedPost!.categories[0].images.length).to.be.equal(2);
            expect(loadedPost!.categories[0].images.map(image => image.id)).to.have.members([1, 2]);
            expect(loadedPost!.author).to.not.be.empty;
            expect(loadedPost!.author.id).to.be.equal(1);

        })));

        it("should load and map selected data when table name used as join argument", () => Promise.all(connections.map(async connection => {

            const user = new User();
            user.name = "Alex Messer";
            await connection.manager.save(user);

            const tag = new Tag();
            tag.name = "audi";
            await connection.manager.save(tag);

            const image1 = new Image();
            image1.name = "image1";
            await connection.manager.save(image1);

            const image2 = new Image();
            image2.name = "image2";
            await connection.manager.save(image2);

            const category1 = new Category();
            category1.name = "cars";
            await connection.manager.save(category1);

            const category2 = new Category();
            category2.name = "germany";
            await connection.manager.save(category2);

            const post = new Post();
            post.title = "about BMW";
            post.tag = tag;
            post.author = user;
            await connection.manager.save(post);

            const loadedPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .leftJoinAndMapOne("post.tag", "tag", "tag", "tag.id = :tagId")
                .leftJoinAndMapOne("post.author", "user", "user", "user.id = :userId")
                .leftJoinAndMapMany("post.categories", "category", "categories", "categories.id IN (:...categoryIds)")
                .leftJoinAndMapMany("categories.images", "image", "image", "image.id IN (:...imageIds)")
                .where("post.id = :id", { id: post.id })
                .setParameters({ tagId: 1, userId: 1, categoryIds: [1, 2], imageIds: [1, 2] })
                .getOne();

            expect(loadedPost!.tag).to.not.be.empty;
            expect(loadedPost!.tag.id).to.be.equal(1);
            expect(loadedPost!.categories).to.not.be.empty;
            expect(loadedPost!.categories.length).to.be.equal(2);
            expect(loadedPost!.categories[0].images).to.not.be.empty;
            expect(loadedPost!.categories[0].images.length).to.be.equal(2);
            expect(loadedPost!.categories[0].images.map(image => image.id)).to.have.members([1, 2]);
            expect(loadedPost!.author).to.not.be.empty;
            expect(loadedPost!.author.id).to.be.equal(1);

        })));

        it("should load and map selected data when data will given from same entity but with different conditions", () => Promise.all(connections.map(async connection => {

            const category1 = new Category();
            category1.name = "cars";
            await connection.manager.save(category1);

            const category2 = new Category();
            category2.name = "germany";
            await connection.manager.save(category2);

            const category3 = new Category();
            category3.name = "bmw";
            await connection.manager.save(category3);

            const post = new Post();
            post.title = "about BMW";
            await connection.manager.save(post);

            const loadedPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .leftJoinAndMapMany("post.categories", Category, "categories", "categories.id IN (:...categoryIds)")
                .leftJoinAndMapMany("post.subcategories", Category, "subcategories", "subcategories.id IN (:...subcategoryIds)")
                .where("post.id = :id", { id: post.id })
                .setParameters({ categoryIds: [1, 2], subcategoryIds: [3] })
                .getOne();

            expect(loadedPost!.categories).to.not.be.empty;
            expect(loadedPost!.categories.length).to.be.equal(2);
            expect(loadedPost!.subcategories).to.not.be.empty;
            expect(loadedPost!.subcategories.length).to.be.equal(1);

        })));

        it("should load and map selected data when data will given from same property but with different conditions", () => Promise.all(connections.map(async connection => {

            const image1 = new Image();
            image1.name = "image1";
            await connection.manager.save(image1);

            const image2 = new Image();
            image2.name = "image2";
            await connection.manager.save(image2);

            const image3 = new Image();
            image3.name = "image3";
            image3.isRemoved = true;
            await connection.manager.save(image3);

            const image4 = new Image();
            image4.name = "image4";
            image4.isRemoved = true;
            await connection.manager.save(image4);

            const category1 = new Category();
            category1.name = "cars";
            category1.images = [image1, image2, image3, image4];
            await connection.manager.save(category1);

            const category2 = new Category();
            category2.name = "germany";
            category2.images = [image1, image2, image3, image4];
            await connection.manager.save(category2);

            const category3 = new Category();
            category3.name = "bmw";
            category3.isRemoved = true;
            category3.images = [image1, image3];
            await connection.manager.save(category3);

            const category4 = new Category();
            category4.name = "citroen";
            category4.isRemoved = true;
            category4.images = [image2, image4];
            await connection.manager.save(category4);

            const post = new Post();
            post.title = "about BMW";
            post.categories = [category1, category2, category3];
            await connection.manager.save(post);

            const post2 = new Post();
            post2.title = "about Citroen";
            post2.categories = [category1, category4];
            await connection.manager.save(post2);

            const loadedPosts = await connection.manager
                .createQueryBuilder(Post, "post")
                .leftJoinAndMapMany("post.removedCategories", "post.categories", "rc", "rc.isRemoved = :isRemoved")
                .leftJoinAndMapMany("rc.removedImages", "rc.images", "removedImages", "removedImages.isRemoved = :isRemoved")
                .leftJoinAndMapMany("post.subcategories", "post.categories", "subcategories", "subcategories.id IN (:...subcategoryIds)")
                .leftJoinAndMapOne("subcategories.titleImage", "subcategories.images", "titleImage", "titleImage.id = :titleImageId")
                .setParameters({ isRemoved: true, subcategoryIds: [1, 2], titleImageId: 1 })
                .getMany();

            expect(loadedPosts![0].removedCategories).to.not.be.empty;
            expect(loadedPosts![0].removedCategories.length).to.be.equal(1);
            expect(loadedPosts![0].removedCategories[0].id).to.be.equal(3);
            expect(loadedPosts![0].removedCategories[0] instanceof Category).to.be.true;
            expect(loadedPosts![0].removedCategories[0].removedImages.length).to.be.equal(1);
            expect(loadedPosts![0].removedCategories[0].removedImages[0] instanceof Image).to.be.true;
            expect(loadedPosts![0].removedCategories[0].removedImages[0].id).to.be.equal(3);
            expect(loadedPosts![0].subcategories).to.not.be.empty;
            expect(loadedPosts![0].subcategories.length).to.be.equal(2);
            expect(loadedPosts![0].subcategories[0].titleImage.id).to.be.equal(1);
            expect(loadedPosts![1].removedCategories).to.not.be.empty;
            expect(loadedPosts![1].removedCategories.length).to.be.equal(1);
            expect(loadedPosts![1].removedCategories[0].id).to.be.equal(4);
            expect(loadedPosts![1].removedCategories[0] instanceof Category).to.be.true;
            expect(loadedPosts![1].removedCategories[0].removedImages.length).to.be.equal(1);
            expect(loadedPosts![1].removedCategories[0].removedImages[0] instanceof Image).to.be.true;
            expect(loadedPosts![1].removedCategories[0].removedImages[0].id).to.be.equal(4);
            expect(loadedPosts![1].subcategories).to.not.be.empty;
            expect(loadedPosts![1].subcategories.length).to.be.equal(1);
            expect(loadedPosts![1].subcategories[0].titleImage.id).to.be.equal(1);

            const loadedPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .leftJoinAndMapMany("post.removedCategories", "post.categories", "rc", "rc.isRemoved = :isRemoved")
                .leftJoinAndMapMany("rc.removedImages", "rc.images", "removedImages", "removedImages.isRemoved = :isRemoved")
                .leftJoinAndMapMany("post.subcategories", "post.categories", "subcategories", "subcategories.id IN (:...subcategoryIds)")
                .leftJoinAndMapOne("subcategories.titleImage", "subcategories.images", "titleImage", "titleImage.id = :titleImageId")
                .setParameters({ isRemoved: true, subcategoryIds: [1, 2], titleImageId: 1 })
                .where("post.id = :id", { id: post.id })
                .getOne();

            expect(loadedPost!.removedCategories).to.not.be.empty;
            expect(loadedPost!.removedCategories.length).to.be.equal(1);
            expect(loadedPost!.removedCategories[0].id).to.be.equal(3);
            expect(loadedPost!.removedCategories[0] instanceof Category).to.be.true;
            expect(loadedPost!.removedCategories[0].removedImages.length).to.be.equal(1);
            expect(loadedPost!.removedCategories[0].removedImages[0] instanceof Image).to.be.true;
            expect(loadedPost!.removedCategories[0].removedImages[0].id).to.be.equal(3);
            expect(loadedPost!.subcategories).to.not.be.empty;
            expect(loadedPost!.subcategories.length).to.be.equal(2);
            expect(loadedPost!.subcategories[0].titleImage.id).to.be.equal(1);

        })));

    });

    describe("innerJoinAndMap", () => {

        it("should load and map selected data when entity used as join argument", () => Promise.all(connections.map(async connection => {

            const user = new User();
            user.name = "Alex Messer";
            await connection.manager.save(user);

            const tag = new Tag();
            tag.name = "audi";
            await connection.manager.save(tag);

            const image1 = new Image();
            image1.name = "image1";
            await connection.manager.save(image1);

            const image2 = new Image();
            image2.name = "image2";
            await connection.manager.save(image2);

            const category1 = new Category();
            category1.name = "cars";
            await connection.manager.save(category1);

            const category2 = new Category();
            category2.name = "germany";
            await connection.manager.save(category2);

            const post = new Post();
            post.title = "about BMW";
            post.tag = tag;
            post.author = user;
            await connection.manager.save(post);

            const loadedPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .innerJoinAndMapOne("post.tag", Tag, "tag", "tag.id = :tagId")
                .innerJoinAndMapOne("post.author", User, "user", "user.id = :userId")
                .innerJoinAndMapMany("post.categories", Category, "categories", "categories.id IN (:...categoryIds)")
                .innerJoinAndMapMany("categories.images", Image, "image", "image.id IN (:...imageIds)")
                .where("post.id = :id", { id: post.id })
                .setParameters({ tagId: 1, userId: 1, categoryIds: [1, 2], imageIds: [1, 2] })
                .getOne();

            expect(loadedPost!.tag).to.not.be.empty;
            expect(loadedPost!.tag.id).to.be.equal(1);
            expect(loadedPost!.categories).to.not.be.empty;
            expect(loadedPost!.categories.length).to.be.equal(2);
            expect(loadedPost!.categories[0].images).to.not.be.empty;
            expect(loadedPost!.categories[0].images.length).to.be.equal(2);
            expect(loadedPost!.categories[0].images.map(image => image.id)).to.have.members([1, 2]);
            expect(loadedPost!.author).to.not.be.empty;
            expect(loadedPost!.author.id).to.be.equal(1);

        })));

        it("should load and map selected data when table name used as join argument", () => Promise.all(connections.map(async connection => {

            const user = new User();
            user.name = "Alex Messer";
            await connection.manager.save(user);

            const tag = new Tag();
            tag.name = "audi";
            await connection.manager.save(tag);

            const image1 = new Image();
            image1.name = "image1";
            await connection.manager.save(image1);

            const image2 = new Image();
            image2.name = "image2";
            await connection.manager.save(image2);

            const category1 = new Category();
            category1.name = "cars";
            await connection.manager.save(category1);

            const category2 = new Category();
            category2.name = "germany";
            await connection.manager.save(category2);

            const post = new Post();
            post.title = "about BMW";
            post.tag = tag;
            post.author = user;
            await connection.manager.save(post);

            const loadedPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .innerJoinAndMapOne("post.tag", "tag", "tag", "tag.id = :tagId")
                .innerJoinAndMapOne("post.author", "user", "user", "user.id = :userId")
                .innerJoinAndMapMany("post.categories", "category", "categories", "categories.id IN (:...categoryIds)")
                .innerJoinAndMapMany("categories.images", "image", "image", "image.id IN (:...imageIds)")
                .where("post.id = :id", { id: post.id })
                .setParameters({ tagId: 1, userId: 1, categoryIds: [1, 2], imageIds: [1, 2] })
                .getOne();

            expect(loadedPost!.tag).to.not.be.empty;
            expect(loadedPost!.tag.id).to.be.equal(1);
            expect(loadedPost!.categories).to.not.be.empty;
            expect(loadedPost!.categories.length).to.be.equal(2);
            expect(loadedPost!.categories[0].images).to.not.be.empty;
            expect(loadedPost!.categories[0].images.length).to.be.equal(2);
            expect(loadedPost!.categories[0].images.map(image => image.id)).to.have.members([1, 2]);
            expect(loadedPost!.author).to.not.be.empty;
            expect(loadedPost!.author.id).to.be.equal(1);

        })));

        it("should load and map selected data when data will given from same entity but with different conditions", () => Promise.all(connections.map(async connection => {

            const category1 = new Category();
            category1.name = "cars";
            await connection.manager.save(category1);

            const category2 = new Category();
            category2.name = "germany";
            await connection.manager.save(category2);

            const category3 = new Category();
            category3.name = "bmw";
            await connection.manager.save(category3);

            const post = new Post();
            post.title = "about BMW";
            await connection.manager.save(post);

            const loadedPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .innerJoinAndMapMany("post.categories", Category, "categories", "categories.id IN (:...categoryIds)")
                .innerJoinAndMapMany("post.subcategories", Category, "subcategories", "subcategories.id IN (:...subcategoryIds)")
                .where("post.id = :id", { id: post.id })
                .setParameters({ categoryIds: [1, 2], subcategoryIds: [3] })
                .getOne();

            expect(loadedPost!.categories).to.not.be.empty;
            expect(loadedPost!.categories.length).to.be.equal(2);
            expect(loadedPost!.subcategories).to.not.be.empty;
            expect(loadedPost!.subcategories.length).to.be.equal(1);

        })));

        it("should load and map selected data when data will given from same property but with different conditions", () => Promise.all(connections.map(async connection => {

            const image1 = new Image();
            image1.name = "image1";
            await connection.manager.save(image1);

            const image2 = new Image();
            image2.name = "image2";
            await connection.manager.save(image2);

            const image3 = new Image();
            image3.name = "image3";
            image3.isRemoved = true;
            await connection.manager.save(image3);

            const image4 = new Image();
            image4.name = "image4";
            image4.isRemoved = true;
            await connection.manager.save(image4);

            const category1 = new Category();
            category1.name = "cars";
            category1.images = [image1, image2, image3, image4];
            await connection.manager.save(category1);

            const category2 = new Category();
            category2.name = "germany";
            category2.images = [image1, image2, image3, image4];
            await connection.manager.save(category2);

            const category3 = new Category();
            category3.name = "bmw";
            category3.isRemoved = true;
            category3.images = [image1, image3];
            await connection.manager.save(category3);

            const category4 = new Category();
            category4.name = "citroen";
            category4.isRemoved = true;
            category4.images = [image2, image4];
            await connection.manager.save(category4);

            const post = new Post();
            post.title = "about BMW";
            post.categories = [category1, category2, category3];
            await connection.manager.save(post);

            const post2 = new Post();
            post2.title = "about Citroen";
            post2.categories = [category1, category4];
            await connection.manager.save(post2);

            const loadedPosts = await connection.manager
                .createQueryBuilder(Post, "post")
                .leftJoinAndMapMany("post.removedCategories", "post.categories", "rc", "rc.isRemoved = :isRemoved")
                .leftJoinAndMapMany("rc.removedImages", "rc.images", "removedImages", "removedImages.isRemoved = :isRemoved")
                .leftJoinAndMapMany("post.subcategories", "post.categories", "subcategories", "subcategories.id IN (:...subcategoryIds)")
                .leftJoinAndMapOne("subcategories.titleImage", "subcategories.images", "titleImage", "titleImage.id = :titleImageId")
                .setParameters({ isRemoved: true, subcategoryIds: [1, 2], titleImageId: 1 })
                .getMany();

            expect(loadedPosts![0].removedCategories).to.not.be.empty;
            expect(loadedPosts![0].removedCategories.length).to.be.equal(1);
            expect(loadedPosts![0].removedCategories[0].id).to.be.equal(3);
            expect(loadedPosts![0].removedCategories[0] instanceof Category).to.be.true;
            expect(loadedPosts![0].removedCategories[0].removedImages.length).to.be.equal(1);
            expect(loadedPosts![0].removedCategories[0].removedImages[0] instanceof Image).to.be.true;
            expect(loadedPosts![0].removedCategories[0].removedImages[0].id).to.be.equal(3);
            expect(loadedPosts![0].subcategories).to.not.be.empty;
            expect(loadedPosts![0].subcategories.length).to.be.equal(2);
            expect(loadedPosts![0].subcategories[0].titleImage.id).to.be.equal(1);
            expect(loadedPosts![1].removedCategories).to.not.be.empty;
            expect(loadedPosts![1].removedCategories.length).to.be.equal(1);
            expect(loadedPosts![1].removedCategories[0].id).to.be.equal(4);
            expect(loadedPosts![1].removedCategories[0] instanceof Category).to.be.true;
            expect(loadedPosts![1].removedCategories[0].removedImages.length).to.be.equal(1);
            expect(loadedPosts![1].removedCategories[0].removedImages[0] instanceof Image).to.be.true;
            expect(loadedPosts![1].removedCategories[0].removedImages[0].id).to.be.equal(4);
            expect(loadedPosts![1].subcategories).to.not.be.empty;
            expect(loadedPosts![1].subcategories.length).to.be.equal(1);
            expect(loadedPosts![1].subcategories[0].titleImage.id).to.be.equal(1);

            const loadedPost = await connection.manager
                .createQueryBuilder(Post, "post")
                .innerJoinAndMapMany("post.removedCategories", "post.categories", "rc", "rc.isRemoved = :isRemoved")
                .innerJoinAndMapMany("rc.removedImages", "rc.images", "removedImages", "removedImages.isRemoved = :isRemoved")
                .innerJoinAndMapMany("post.subcategories", "post.categories", "subcategories", "subcategories.id IN (:...subcategoryIds)")
                .innerJoinAndMapOne("subcategories.titleImage", "subcategories.images", "titleImage", "titleImage.id = :titleImageId")
                .setParameters({ isRemoved: true, subcategoryIds: [1, 2], titleImageId: 1 })
                .where("post.id = :id", { id: post.id })
                .getOne();

            expect(loadedPost!.removedCategories).to.not.be.empty;
            expect(loadedPost!.removedCategories.length).to.be.equal(1);
            expect(loadedPost!.removedCategories[0].id).to.be.equal(3);
            expect(loadedPost!.removedCategories[0] instanceof Category).to.be.true;
            expect(loadedPost!.removedCategories[0].removedImages.length).to.be.equal(1);
            expect(loadedPost!.removedCategories[0].removedImages[0] instanceof Image).to.be.true;
            expect(loadedPost!.removedCategories[0].removedImages[0].id).to.be.equal(3);
            expect(loadedPost!.subcategories).to.not.be.empty;
            expect(loadedPost!.subcategories.length).to.be.equal(2);
            expect(loadedPost!.subcategories[0].titleImage.id).to.be.equal(1);

        })));

        it("should not return any result when related data does not exist", () => Promise.all(connections.map(async connection => {

            const post = new Post();
            post.title = "about BMW";
            await connection.manager.save(post);

            const loadedPost1 = await connection.manager
                .createQueryBuilder(Post, "post")
                .innerJoinAndMapOne("post.author", User, "user", "user.id = :userId")
                .where("post.id = :id", { id: 1 })
                .setParameters({ userId: 1 })
                .getOne();

            expect(loadedPost1!).to.be.empty;

            const loadedPost2 = await connection.manager
                .createQueryBuilder(Post, "post")
                .innerJoinAndMapMany("post.categories", Category, "categories", "categories.id = :categoryId")
                .where("post.id = :id", { id: 1 })
                .setParameters({ categoryId: 1 })
                .getOne();

            expect(loadedPost2!).to.be.empty;

        })));
    });

});