Builders

JoinQueryBuilder

With safe-typeorm, you can write SQL query much safely and conveniently.

If you take a mistake when writing an SQL query, the error would be occured in the compilation level. Therefore, you don't need to suffer by runtime error by mistaken SQL query. Also, if you're writing wrong SQL query, the IDE (like VSCode) will warn you with the red underlined emphasizing, to tell you there can be an SQL error.

Also, safe-typeorm supports type hinting with auto-completion when you're writing the SQL query. Therefore, you can write SQL query much faster than before. Of course, the fast-written SQL query would be ensured its safety by the compiler and IDE.

Below is entire code of above gif image:

import * as orm from "typeorm";
import safe from "safe-typeorm";

export function demo_join_query_builder(
    group: BbsGroup,
    exclude: string[],
): orm.SelectQueryBuilder<BbsQuestionArticle> {
    const question = safe.createJoinQueryBuilder(BbsQuestionArticle);

    // JOIN
    const article = question.innerJoin("base");
    const content = article.innerJoin("__mv_last").innerJoin("content");
    const category = article.innerJoin("category");

    // SELECT
    article.addSelect("id").addSelect("writer").addSelect("created_at");
    content.addSelect("title").addSelect("created_at", "updated_at");
    article.leftJoin("answer").leftJoin("base", "AA", (answer) => {
        answer.addSelect("writer", "answer_writer");
        answer.addSelect("created_at", "answer_created_at");
        answer
            .leftJoin("__mv_last", "AL")
            .leftJoin("content", "AC")
            .addSelect(
                ["title", (str) => `COALESCE(${str}, 'NONE)`],
                "answer_title",
            );
    });
    content.addOrderBy("created_at", "DESC");

    // WHERE
    article.andWhere("group", group);
    category.andWhere("code", "NOT IN", exclude);
    return question.statement();
}

By the way, if you want to construct SQL query by only one statement, without defining any surplus variable, I can say that "It is is also possible, but a little bit unsafe".

Below is the code constructing SQL query at once. As you can see, declaring SELECT and WHERE statements are not perfect type safe. It is possible to list up another tables' columns, which have never participated in the JOIN statement.

import safe from "safe-typeorm";
import * as orm from "typeorm";

export function demo_join_query_builder_onetime(
    group: BbsGroup,
    exclude: string[],
): orm.SelectQueryBuilder<BbsQuestionArticle> {
    const builder = safe.createJoinQueryBuilder(
        BbsQuestionArticle,
        (question) => {
            question.innerJoin("base", (article) => {
                article.innerJoin("group");
                article.innerJoin("category");
                article.innerJoin("__mv_last").innerJoin("content");
            });
            question
                .leftJoin("answer")
                .leftJoin("base", "AA")
                .leftJoin("__mv_last", "AL")
                .leftJoin("content", "AC");
        },
    );
    return builder
        .statement()
        .andWhere(...BbsArticle.getWhereArguments("group", group))
        .andWhere(...BbsCategory.getWhereArguments("code", "NOT IN", exclude))
        .select([
            BbsArticle.getColumn("id"),
            BbsGroup.getColumn("name", "group"),
            BbsCategory.getColumn("name", "category"),
            BbsArticle.getColumn("writer"),
            BbsArticleContent.getColumn("title"),
            BbsArticle.getColumn("created_at"),
            BbsArticleContent.getColumn("created_at", "updated_at"),

            BbsArticle.getColumn("AA.writer", "answer_writer"),
            BbsArticleContent.getColumn(
                ["AA.title", str => `COALESCE(${str}, 'NONE')`], 
                "answer_title"
            ),
            BbsArticle.getColumn("AA.created_at", "answer_created_at"),
        ]);
}

AppJoinBuilder

With the AppJoinBuilder class, you can implement application level joining very easily.

Also, grammer of the AppJoinBuilder is almost same with the JoinQueryBuilder. Therefore, you can swap JoinQueryBuilder and AppJoinBuilder very simply without any cost. Thus, you can just select one of them suitable for your case.

Below is entire code of above gif image:

import safe from "safe-typeorm";

import { BbsReviewArticle } from "../models/bbs/BbsReviewArticle";

export async function test_app_join_builder_initialize(
    reviews: BbsReviewArticle[],
): Promise<void> {
    const builder = safe.createAppJoinBuilder(BbsReviewArticle, (review) => {
        review.join("base", (article) => {
            article.join("group");
            article.join("category").join("parent");
            article.join("contents", (content) => {
                content.join("reviewContent");
                content.join("files");
            });
            article.join("comments").join("files");
            article.join("tags");
        });
    });
    await builder.execute(reviews);
}

By the way, if you've determined to using only the AppJoinBuilder, you can configure it much safely.

With the AppJoinBuilder.initialize() method, you have to configure every relationship accessors, and it prevents any type of ommission.

import safe from "safe-typeorm";

export async function test_app_join_builder_initialize(
    groups: BbsGroup[],
): Promise<void> {
    const builder = safe.AppJoinBuilder.initialize(BbsGroup, {
        articles: safe.AppJoinBuilder.initialize(BbsArticle, {
            group: undefined,
            review: safe.AppJoinBuilder.initialize(BbsReviewArticle, {
                base: undefined,
            }),
            category: "join" as const,
            contents: safe.AppJoinBuilder.initialize(BbsArticleContent, {
                files: "join" as const,
                article: undefined,
                reviewContent: undefined,
            }),
            comments: safe.AppJoinBuilder.initialize(BbsComment, {
                article: undefined,
                files: "join",
            }),
            tags: "join",
            __mv_last: undefined,
            question: undefined,
            answer: undefined,
            scraps: undefined,
        }),
    });
    await builder.execute(groups);
}

JsonSelectBuilder

In safe-typeorm, when you want to load DB records and convert them to a JSON data, you don't need to write any SELECT or JOIN query. You also do not need to consider any performance tuning. Just write down the ORM -> JSON transform plan, then safe-typeorm will do everything.

The JsonSelectBuilder is the class doing everything. It will analyze your JSON transform plan, and compose the JSON transform method automatically with the exact JSON type what you want. Furthermore, the JsonSelectBuilder finds the best (applicataion level) joining plan by itself, when being constructed.

Below code is an example converting ORM model class instances to JSON data with the JsonSelectBuilder. As you can see, there's no special script in the below code, but only the transform plan is. As I've mentioned, JsonSelectBuilder will construct the exact JSON type by analyzing your transform plan. Also, the performance tuning would be done automatically.

Therefore, just enjoy the JsonSelectBuilder without any worry.

import safe from "safe-typeorm";

export async function demo_app_join_builder(
    groups: BbsGroup[],
): Promise<IBbsGroup[]> {
    const builder = new safe.JsonSelectBuilder(BbsGroup, {
        articles: new safe.JsonSelectBuilder(BbsArticle, {
            group: safe.DEFAULT,
            category: new safe.JsonSelectBuilder(BbsCategory, {
                parent: "recursive" as const,
            }),
            tags: new safe.JsonSelectBuilder(
                BbsArticleTag,
                {},
                (tag) => tag.value, // OUTPUT CONVERSION BY MAPPING
            ),
            contents: new safe.JsonSelectBuilder(BbsArticleContent, {
                files: "join" as const,
            }),
        }),
    });
    return builder.getMany(groups);
}