Skip to content

OldSpaling/typeorm-linq

Repository files navigation

typeorm-linq

typeorm-linq is a library that support linq base on typeorm SelectQueryBuilder. SqlServer,Postgres and MySql is supported. Oracle is on-going.

Install

  • install typeorm
npm install typeorm --save
  • install typeorm-linq
npm install typeorm-linq --save

Usage

 //init config when code start
 init({dbType:'postgres'});
 //query db by QueryBuilder
 const query =await new LinqInferQueryBuilder<SchoolEntity>(dataSource)
      .create(SchoolEntity, 'sc')
      .innerJoinAndSelect(
        ClassesEntity,
        'cla',
        ({ sc, cla }) => sc.id == cla.schoolId,
      )
      .leftJoinAndSelect(
        StudentEntity,
        'stu',
        ({ stu, cla }) => stu.classesId == cla.id,
      )
      .leftJoinAndSelect(
        TeacherEntity,
        'te',
        ({ cla, te }) => cla.headTeacherId == te.id,
      )
      .where(
        ({ cla, stu }) =>
          (!stu.isMale && cla.name == '8') || stu.isMonitor == true,
      )
      .andWhere(({ sc }) => sc.id == schoolFilter.id, {
        schoolFilterid: schoolFilter.id, //just remove '.' as name
      })
      .orderBy(({ sc }) => sc.name, 'DESC')
      .select(({ sc, cla, stu, te }) => {
        schoolName: sc.name;
        className: cla.name;
        headTeacher: te.firstName + ' ' + te.lastName;
        stuName: stu.firstName + ' ' + stu.lastName;
      })
      .getRawMany<{
        schoolName: string;
        className: string;
        headTeacher: string;
        stuName: string;
      }>();

Feature

  • Type Inference typeorm-linq can dynamic infer function type. eg:cla and stu is a entity alias,but in where function,the types of cla and stu are ClassesEntity and StudentEntity
    //
    where(
      ({ cla, stu }) => (!stu.isMale && cla.name == '8') || stu.isMonitor == true,
    );
  • Cache:Arrow Function will be complied sql string when it is invoked for the first time.The default cache store is ExpressionCacheMemoryStore.
    • ExpressionCacheMemoryStore(default)
    • ExpressionCacheFileStore
    • ExpressionCacheStore(custom store base class)
  • Support one to many for the relation of alias and entity
    queryBuilder
      .create(ClassesEntity, 'cla1')
      .innerJoinAndSelect(
        ClassesEntity,
        'cla2',
        ({ cla1, cla2 }) => sc.id == cla.parentId,
      );
  • Support join with relations
const query =
  (await new LinqInferQueryBuilder()) <
  StudentEntity >
  dataSource
    .create(StudentEntity, 'student')
    .innerJoinAndSelect(
      ClassesEntity,
      'classes1',
      null,
      ({ student }) => student.classes,
      null,
    )
    .getMany();
  • Support variable
    • Object:the max deep level is 2.eg:a.b
      //param label just remove '.' and connect to camelCase after 1.1.0
      const school = { id: 2 };
      queryBuilder
        .create(SchoolEntity, 'sc')
        .where(({ sc }) => sc.id == school.id, {
          schoolId: school.id,
        });
    • Basic Type
      const id = 2;
      queryBuilder.create(SchoolEntity, 'sc').where(({ sc }) => sc.id == id, {
        id,
      });
    • Array:must be end with Array
      const idArray = [2, 3];
      queryBuilder
        .create(SchoolEntity, 'sc')
        .where(({ sc }) => idArray.includes(sc.id), {
          id,
        });
    • constant
      queryBuilder.create(SchoolEntity, 'sc').where(({ sc }) => sc.id == 5);
  • Support JS Syntax
    • Array.includes must be end with Array
      const idArray = [2, 3];
      queryBuilder
        .create(SchoolEntity, 'sc')
        .where(({ sc }) => idArray.includes(sc.id), {
          idArray,
        });
    • string.includes/string.startsWith/string.endsWith
    const test = 'test';
    queryBuilder
      .create(SchoolEntity, 'sc')
      .where(({ sc }) => sc.name.includes(test), {
        test: OperatorConvertMapping.currentConvert.like(test),
      });
    • (), =====!==!=||&&
      const test = 'test';
      const test2 = 'test2';
      const idsArray = [2, 4, 5, 7];
      queryBuilder
        .create(SchoolEntity, 'sc')
        .where(
          ({ sc }) =>
            sc.name.includes(test) ||
            (sc.name.includes(test2) && idArray.includes(sc.id)),
          {
            test,
            test2,
            idsArray,
          },
        );
    • constant
      //case1
      queryBuilder.create(SchoolEntity, 'sc').where(({ sc }) => sc.id == 5);
      //case2
      queryBuilder
        .create(SchoolEntity, 'sc')
        .where(({ sc }) => sc.isDeleted == true);
    • unary operator
      queryBuilder.create(SchoolEntity, 'sc').where(({ sc }) => !sc.isDeleted);
    • + operator
      queryBuilder.create(TeacherEntity, 'te').select(({ te }) => {
        name: te.firstName + ' ' + te.lastName;
      });
    • >,<,>=,<=
      queryBuilder
        .create(TeacherEntity, 'te')
        .where(({ cla, stu }) => ExpressionAggregateFunc.len(cla.name) >= 3);
    • Aggregate Function:ExpressionAggregateFunc
      //subQuery
      ExpressionAggregateFunc.subQuery(
        'case when a.name is null then "test" else a.name',
      );
      //len
      queryBuilder
        .create(SchoolEntity, 'sc')
        .where(({ sc }) => ExpressionAggregateFunc.len(sc.name));
    • complex use
      queryBuilder.create(TeacherEntity, 'te').select(({ te }) => {
        name: ExpressionAggregateFunc.len(te.firstName + ' ' + te.lastName);
      });

typeorm other feature

  • transaction support

    queryBuilder.transaction(
      TeacherEntity,
      'te',
      async (manager, queryBuilder) => {
        //if you need ,you can use queryBuilder to select some data.
        //business code
      },
    );
  • from

    new LinqInferQueryBuilder() <
      SchoolEntity >
      dataSource
        .create()
        .from((qb: LinqInferQueryBuilder<TeacherEntity>) => {
          //TeacherEntity as the return type,it isn't necessary
          const subQuery = qb
            .from(TeacherEntity, 'subTech')
            .where(({ subTech }) => subTech.id == 1);
          return subQuery;
        }, 'from1')
        .where(({ from1 }) => from1.firstName == 'test');
    • select/addSelect
    (await new LinqInferQueryBuilder()) <
      StudentEntity >
      dataSource
        .create(StudentEntity, 'student1')
        .leftJoinAndSelect(
          ClassesEntity,
          'classes1',
          null,
          ({ student1 }) => student1.classes,
          null,
        )
        .select([
          ({ student1 }) => student1.id,
          ({ student1 }) => student1.firstName,
          ({ classes1 }) => classes1.id,
          ({ classes1 }) => classes1.name,
        ])
        .addSelect([
          ({ student1 }) => student1.address,
          ({ classes1 }) => classes1.schoolId,
        ])
        .getMany();

OnGoing

  • support Oracle

Besides

The Reason of choosing Object Type as Arrow Function Params instead of Tuple is below: Tuple just can infer type,but cannot infer variable name.

About

No description, website, or topics provided.

Resources

License

Stars

Watchers

Forks

Packages

No packages published