Adding WHERE expression
Adding WHERE expression Adding a WHERE expression is as easy as: Which will produce: You can add AND into an existing WHERE expression: Which will produce the following SQL query: You can add OR into an existing WHERE expression: Which will produce the following SQL query: You can do an IN query with the WHERE expression: Which will produce the following SQL query: You can add a complex WHERE expression into an existing WHERE using Brackets Which will produce the following SQL query: You can add a negated complex WHERE expression into an existing WHERE using NotBrackets Which will produce the following SQL query:
Bind Prams:(to Prevent sql injection)
Using parameters to escape data We used where(“user.name = :name”, { name: “Timber” }). What does { name: “Timber” } stand for? It’s a parameter we used to prevent SQL injection. We could have written: where(“user.name = ‘” + name + “‘), however this is not safe, as it opens the code to SQL injections. The safe way is to use this special syntax: where(“user.name = :name”, { name: “Timber” }), where :name is a parameter name and the value is specified in an object: { name: “Timber” }. is a shortcut for: Note: do not use the same parameter name for different values across the query builder. Values will be overridden if you set them multiple times. You can also supply an array of values, and have them transformed into a list of values in the SQL statement, by using the special expansion syntax: Which becomes:
Query Builder:
What is QueryBuilder QueryBuilder is one of the most powerful features of TypeORM – it allows you to build SQL queries using elegant and convenient syntax, execute them and get automatically transformed entities. Simple example of QueryBuilder: It builds the following SQL query: and returns you an instance of User: Important note when using the QueryBuilder When using the QueryBuilder, you need to provide unique parameters in your WHERE expressions. This will not work: … but this will: Note that we uniquely named :sheepId and :cowId instead of using :id twice for different parameters. How to create and use a QueryBuilder There are several ways how you can create a Query Builder: There are 5 different QueryBuilder types available: You can switch between different types of query builder within any of them, once you do, you will get a new instance of query builder (unlike all other methods).
Custom Repository
It’s common practice assigning a repository instance to a globally exported variable, and use this variable across your app, for example: In order to extend UserRepository functionality you can use .extend method of Repository class:
Filters in Typeorm
Basic options All repository and manager .find* methods accept special options you can use to query data you need without using QueryBuilder: will execute following query: will execute following queries: will execute following query: Querying a column from an embedded entity should be done with respect to the hierarchy in which it was defined. Example: will execute following query: Querying with OR operator: will execute following query: will execute following query: find* methods which return multiple entities (find, findBy, findAndCount, findAndCountBy) also accept following options: will execute following query: ** skip and take should be used together ** If you are using typeorm with MSSQL, and want to use take or limit, you need to use order as well or you will receive the following error: ‘Invalid usage of the option NEXT in the FETCH statement.’ will execute following query: Advanced options TypeORM provides a lot of built-in operators that can be used to create more complex comparisons: will execute following query: will execute following query: will execute following query: will execute following query: will execute following query: will execute following query: will execute following query: will execute following query: will execute following query: will execute following query: will execute following query (Postgres notation): will execute following query: Combining Advanced Options Also you can combine these operators with Not operator: will execute following query:
Detail: Many-to-many relations
Many-to-many relations are a concept in database design and object-relational mapping (ORM) frameworks like TypeORM that involve a relationship between two entities, where each entity can be related to multiple instances of the other entity. Let’s go through the provided information step by step: What Are Many-to-Many Relations? In a many-to-many relationship, two entities (A and B) are related in such a way that each instance of entity A can be associated with multiple instances of entity B, and each instance of entity B can be associated with multiple instances of entity A. An example of this is the relationship between Question and Category entities. Defining Many-to-Many Relations: In TypeORM, you define many-to-many relations using decorators in your entity classes. For example, in your Question entity, you have a categories property defined with the @ManyToMany decorator. Similarly, in the Category entity, you define a questions property with the @ManyToMany decorator. Saving Many-to-Many Relations: With cascading enabled, you can save the many-to-many relation using a single save call. For example, you create instances of both Category and Question, associate the categories with the question, and then save the question. TypeORM will handle the insertion of records in the join table automatically. Deleting Many-to-Many Relations: When using cascading, you can also delete the many-to-many relation with a single save call. To delete a relationship between two records, remove the associated instance from the corresponding field and save the record. Loading Many-to-Many Relations: To load instances with their related many-to-many records, you specify the relation in the find options or use QueryBuilder to join the related records. Eager loading can be used to automatically load related records with the main entity. Bi-directional Relations: A relation can be uni-directional or bi-directional. Uni-directional relations have a relation decorator on one side, while bi-directional relations have decorators on both sides. Bi-directional relations allow you to join relations from both sides using QueryBuilder. Many-to-Many Relations with Custom Properties: If you need to have additional properties in your many-to-many relationship, you create a new entity to represent the relationship itself. This new entity contains references to both entities and any additional properties needed for the relationship. In your provided example, PostToCategory is an entity that represents the many-to-many relationship between Post and Category. It includes additional properties like order and foreign key references to Post and Category. In summary, many-to-many relations are a powerful tool in database design and ORM frameworks to represent complex relationships between entities. They allow you to associate instances of different entities in flexible ways, whether it’s a simple relationship or one with custom properties. Of course! Let’s go through the concepts of many-to-many relations and their implementation using the provided examples: Example: Question and Category Let’s say we’re building a platform where users can ask questions and assign categories to those questions. Each question can belong to multiple categories, and each category can have multiple questions. // Category entity@Entity()export class Category {@PrimaryGeneratedColumn()id: number; } // Question entity@Entity()export class Question {@PrimaryGeneratedColumn()id: number; } Here, we have two entities: Category and Question. Each question can be associated with multiple categories, and each category can have multiple questions. The @ManyToMany decorator establishes the many-to-many relationship. // Creating categoriesconst category1 = new Category();category1.name = “animals”; const category2 = new Category();category2.name = “zoo”; // Creating a question and associating categoriesconst question = new Question();question.title = “dogs”;question.text = “who let the dogs out?”;question.categories = [category1, category2]; // Saving the question will automatically save the categories and the relationshipawait dataSource.manager.save(question); Loading Many-to-Many Relations: // Loading questions with their categoriesconst questionsWithCategories = await questionRepository.find({relations: {categories: true,},}); Bi-directional Relations: // In Category entity@ManyToMany(() => Question, (question) => question.categories)questions: Question[]; // In Question entity@ManyToMany(() => Category, (category) => category.questions)@JoinTable()categories: Category[]; By making both sides of the relationship aware of each other, you can easily load related data from either entity. In this scenario, let’s say you want to assign a specific order to questions within a category. You’d create an intermediary entity to represent this relationship: @Entity()export class QuestionCategory {@PrimaryGeneratedColumn()id: number; } Now you have a QuestionCategory entity that holds the relationship between Question and Category, along with the order of the question within that category. These examples showcase the concepts of many-to-many relationships, including their implementation, saving, loading, and handling custom properties. They allow you to model complex associations between different entities in a flexible and organized manner.
Relations in SQL & Typeorm
Relations in SQL: In the context of databases, a relation refers to the logical association between tables based on common columns. Relations are used to establish connections between tables, enabling you to retrieve and manipulate related data efficiently. There are several types of relationships in SQL: TypeORM: TypeORM is an Object-Relational Mapping (ORM) library for TypeScript and JavaScript that simplifies database interactions by allowing you to work with database tables as classes and objects. It provides a high-level abstraction over SQL databases, enabling developers to focus on their application’s logic rather than dealing with raw SQL queries. In TypeORM, relationships are defined using decorators and configuration within your entity classes. Let’s go through examples for each type of relationship using TypeORM: One-to-One Relationship: import { Entity, PrimaryGeneratedColumn, Column, OneToOne, JoinColumn } from ‘typeorm’;import { Address } from ‘./Address’; @Entity()export class Person {@PrimaryGeneratedColumn()id: number; @Column()name: string; @OneToOne(() => Address)@JoinColumn()address: Address;} One-to-Many Relationship: import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from ‘typeorm’;import { Book } from ‘./Book’; @Entity()export class Author {@PrimaryGeneratedColumn()id: number; @Column()name: string; @OneToMany(() => Book, book => book.author)books: Book[];} Many-to-Many Relationship: import { Entity, PrimaryGeneratedColumn, Column, ManyToMany, JoinTable } from ‘typeorm’;import { Course } from ‘./Course’; @Entity()export class Student {@PrimaryGeneratedColumn()id: number; @Column()name: string; @ManyToMany(() => Course)@JoinTable()courses: Course[];} In these examples, we’ve used TypeORM decorators like @OneToOne, @OneToMany, and @ManyToMany to define relationships between entities. The @JoinColumn and @JoinTable decorators are used to specify how the join should occur in the database. Remember that TypeORM also supports additional options for relationship configuration, such as eager loading, cascading, and more. These are just basic examples to illustrate the concepts. In a real-world scenario, you would also need to configure your database connection and set up entities properly before using them. One-to-One Relationship: Person Table: id name address_id 1 John 1 2 Jane 2 Address Table: id street city 1 123 Main St Anytown 2 456 Elm St Big City One-to-Many Relationship: Author Table: id name 1 Mark 2 Emily Book Table: id title author_id 1 Book A 1 2 Book B 1 3 Book C 2 Many-to-Many Relationship: Student Table: id name 1 Alice 2 Bob Course Table: id name 1 Math 2 Science 3 History Student_Course Table (Junction Table): student_id course_id 1 1 1 2 2 2 2 3 This table representation showcases how the data would be stored in the database tables for each type of relationship. Keep in mind that these examples are simplified for illustration purposes, and real-world scenarios might involve more complex relationships and additional columns.
Single Table Inheritance
Single Table Inheritance TypeORM also supports single table inheritance. Single table inheritance is a pattern when you have multiple classes with their own properties, but in the database they are stored in the same table. This will create a single table called content and all instances of photos, questions and posts will be saved into this table.
Concrete Table Inheritance
Concrete Table Inheritance You can reduce duplication in your code by using entity inheritance patterns. The simplest and the most effective is concrete table inheritance. For example, you have Photo, Question, Post entities: As you can see all those entities have common columns: id, title, description. To reduce duplication and produce a better abstraction we can create a base class called Content for them: All columns (relations, embeds, etc.) from parent entities (parent can extend other entity as well) will be inherited and created in final entities. This example will create 3 tables – photo, question and post.
Embedded Entities (Duplication Reduction)
Embedded entities offer a powerful technique to minimize code duplication within your application by utilizing composition over inheritance. Through the use of embedded columns, you can define a class with its own columns and seamlessly integrate these columns into the database table of the current entity. This approach proves particularly effective when dealing with multiple entities that share common properties, such as first name and last name. Here’s a step-by-step illustration of how to implement and utilize embedded entities: Step 1: Define Shared Columns in an Embedded Class Create a separate class to house the commonly shared columns, such as first name and last name: import { Column } from “typeorm” export class Name {@Column()first: string } Step 2: Implement Embedded Entities in Individual Entities In each entity that needs the shared columns, utilize the embedded class as a column. This integrates the shared columns into the entity’s database table: User Entity: import { Entity, PrimaryGeneratedColumn, Column } from “typeorm”import { Name } from “./Name” @Entity()export class User {@PrimaryGeneratedColumn()id: string } Employee Entity: import { Entity, PrimaryGeneratedColumn, Column } from “typeorm”import { Name } from “./Name” @Entity()export class Employee {@PrimaryGeneratedColumn()id: string } Student Entity: import { Entity, PrimaryGeneratedColumn, Column } from “typeorm”import { Name } from “./Name” @Entity()export class Student {@PrimaryGeneratedColumn()id: string } Step 3: Reduced Code Duplication The shared columns defined in the Name entity will be automatically integrated into each of the individual entity tables, resulting in the following table structures: User Table: id nameFirst nameLast isActive int(11) varchar(255) varchar(255) boolean PRIMARY KEY AUTO_INCREMENT Employee Table: id nameFirst nameLast salary int(11) varchar(255) varchar(255) int(11) PRIMARY KEY AUTO_INCREMENT Student Table: id nameFirst nameLast faculty int(11) varchar(255) varchar(255) varchar(255) PRIMARY KEY AUTO_INCREMENT Please note that in this representation, the headers are displayed at the top of each table, and the corresponding data types are listed below them. The PRIMARY KEY and AUTO_INCREMENT properties are indicated under their respective columns. This format provides a visual overview of the structure of each table, including the relationships between columns and their associated data types.
