import { JoinType , QueryType , WhereType } from "./querybuilder.ts" ; import { testQueryBuilder } from "./testutils.ts" ; import { Q , QueryOperator } from "./q.ts" ; testQueryBuilder ( "basic `where`" , ( query ) => query . where ( "email" , "a@b.com" ) , { wheres : [ { type : WhereType . Default , column : "email" , expression : Q . eq ( "a@b.com" ) } ] } ) ; testQueryBuilder ( "`where` with custom expression" , ( query ) => query . where ( "age" , Q . gt ( 16 ) ) , { wheres : [ { type : WhereType . Default , column : "age" , expression : Q . gt ( 16 ) } ] } ) ; testQueryBuilder ( "multiple `where` clauses" , ( query ) => query . where ( "email" , Q . eq ( "a@b.com" ) ) . where ( "age" , Q . gt ( 16 ) ) . where ( "is_active" , Q . eq ( true ) ) . where ( "birthday" , Q . eq ( new Date ( "7 July, 2020" ) ) ) , { wheres : [ { type : WhereType . Default , column : "email" , expression : Q . eq ( "a@b.com" ) } , { type : WhereType . Default , column : "age" , expression : Q . gt ( 16 ) } , { type : WhereType . Default , column : "is_active" , expression : Q . eq ( true ) } , { type : WhereType . Default , column : "birthday" , expression : { operator : QueryOperator . Equal , value : new Date ( "7 July, 2020" ) } } ] } ) ; testQueryBuilder ( "`or`" , ( query ) => query . or ( "email" , Q . eq ( "a@b.com" ) ) , { wheres : [ { type : WhereType . Or , column : "email" , expression : Q . eq ( "a@b.com" ) } ] } ) ; testQueryBuilder ( "`not`" , ( query ) => query . not ( "email" , Q . eq ( "a@b.com" ) ) , { wheres : [ { type : WhereType . Not , column : "email" , expression : Q . eq ( "a@b.com" ) } ] } ) ; testQueryBuilder ( "`or` and `not`" , ( query ) => query . where ( "email" , Q . eq ( "a@b.com" ) ) . or ( "name" , Q . like ( "%john%" ) ) . not ( "age" , Q . gt ( 16 ) ) , { wheres : [ { type : WhereType . Default , column : "email" , expression : Q . eq ( "a@b.com" ) } , { type : WhereType . Or , column : "name" , expression : Q . like ( "%john%" ) } , { type : WhereType . Not , column : "age" , expression : Q . gt ( 16 ) } ] } ) ; testQueryBuilder ( "`distinct` should enable distinct select" , ( query ) => query . distinct ( ) , { isDistinct : true } ) ; testQueryBuilder ( "`count` should count records with given conditions" , ( query ) => query . count ( "name" ) , { counts : [ { columns : [ "name" ] , distinct : false } ] } ) ; testQueryBuilder ( "`count` should count with alias" , ( query ) => query . count ( "name" , "count" ) , { counts : [ { columns : [ "name" ] , as : "count" , distinct : false } ] } ) ; testQueryBuilder ( "`countDistinct` should count with distinct" , ( query ) => query . countDistinct ( "name" ) , { counts : [ { columns : [ "name" ] , distinct : true } ] } ) ; testQueryBuilder ( "`countDistinct` should count with distinct and alias" , ( query ) => query . countDistinct ( "name" , "count" ) , { counts : [ { columns : [ "name" ] , as : "count" , distinct : true } ] } ) ; testQueryBuilder ( "`limit` should set the record limit" , ( query ) => query . limit ( 10 ) , { limit : 10 } ) ; testQueryBuilder ( "`offset` should set the number of records to skip" , ( query ) => query . offset ( 5 ) , { offset : 5 } ) ; testQueryBuilder ( "`limit` and `offset`" , ( query ) => query . limit ( 7 ) . offset ( 14 ) , { limit : 7 , offset : 14 } ) ; testQueryBuilder ( "`first` should be the shortcut for limit(1)" , ( query ) => query . first ( ) , { limit : 1 } ) ; testQueryBuilder ( "`select` should add the list of all selected columns" , ( query ) => query . select ( "email" ) , { columns : [ "email" ] } ) ; testQueryBuilder ( "`select` multiple columns" , ( query ) => query . select ( "email" ) . select ( "age" ) . select ( "is_active" ) , { columns : [ "email" , "age" , "is_active" ] } ) ; testQueryBuilder ( "`select` multiple columns with a single method" , ( query ) => query . select ( "email" , "age" , "is_active" ) , { columns : [ "email" , "age" , "is_active" ] } ) ; testQueryBuilder ( "`select` multiple columns with aliases" , ( query ) => query . select ( [ "email" , "my_email" ] , [ "age" , "my_age" ] ) , { columns : [ [ "email" , "my_email" ] , [ "age" , "my_age" ] ] } ) ; testQueryBuilder ( "`groupBy` should add the list of all grouped columns" , ( query ) => query . groupBy ( "email" ) , { groupBy : [ "email" ] } ) ; testQueryBuilder ( "`groupBy` multiple columns" , ( query ) => query . groupBy ( "email" ) . groupBy ( "age" ) . groupBy ( "is_active" ) , { groupBy : [ "email" , "age" , "is_active" ] } ) ; testQueryBuilder ( "`groupBy` multiple columns with a single method" , ( query ) => query . groupBy ( "email" , "age" , "is_active" ) , { groupBy : [ "email" , "age" , "is_active" ] } ) ; testQueryBuilder ( "basic `having`" , ( query ) => query . having ( "email" , "a@b.com" ) , { havings : [ { column : "email" , expression : Q . eq ( "a@b.com" ) , type : WhereType . Default } ] } ) ; testQueryBuilder ( "`having` with custom expression" , ( query ) => query . having ( "age" , Q . gt ( 16 ) ) , { havings : [ { column : "age" , expression : Q . gt ( 16 ) , type : WhereType . Default } ] } ) ; testQueryBuilder ( "multiple `having` clauses" , ( query ) => query . having ( "email" , Q . eq ( "a@b.com" ) ) . having ( "age" , Q . gt ( 16 ) ) . having ( "is_active" , Q . eq ( true ) ) . having ( "birthday" , Q . eq ( new Date ( "7 July, 2020" ) ) ) , { havings : [ { column : "email" , expression : Q . eq ( "a@b.com" ) , type : WhereType . Default } , { column : "age" , expression : Q . gt ( 16 ) , type : WhereType . Default } , { column : "is_active" , expression : Q . eq ( true ) , type : WhereType . Default } , { column : "birthday" , expression : Q . eq ( new Date ( "7 July, 2020" ) ) , type : WhereType . Default } ] } ) ; testQueryBuilder ( "basic `innerJoin`" , ( query ) => query . innerJoin ( "companies" , "users.company_id" , "companies.id" ) , { joins : [ { type : JoinType . Inner , table : "companies" , columnA : "users.company_id" , columnB : "companies.id" } ] } ) ; testQueryBuilder ( "basic `fullJoin`" , ( query ) => query . fullJoin ( "companies" , "users.company_id" , "companies.id" ) , { joins : [ { type : JoinType . Full , table : "companies" , columnA : "users.company_id" , columnB : "companies.id" } ] } ) ; testQueryBuilder ( "basic `leftJoin`" , ( query ) => query . leftJoin ( "companies" , "users.company_id" , "companies.id" ) , { joins : [ { type : JoinType . Left , table : "companies" , columnA : "users.company_id" , columnB : "companies.id" } ] } ) ; testQueryBuilder ( "basic `rightJoin`" , ( query ) => query . rightJoin ( "companies" , "users.company_id" , "companies.id" ) , { joins : [ { type : JoinType . Right , table : "companies" , columnA : "users.company_id" , columnB : "companies.id" } ] } ) ; testQueryBuilder ( "`order` should order the result" , ( query ) => query . order ( "age" ) , { orders : [ { column : "age" , order : "ASC" } ] } ) ; testQueryBuilder ( "`order` should order the result with custom direction" , ( query ) => query . order ( "age" , "DESC" ) , { orders : [ { column : "age" , order : "DESC" } ] } ) ; testQueryBuilder ( "multiple `order`" , ( query ) => query . order ( "age" , "DESC" ) . order ( "created_at" ) , { orders : [ { column : "age" , order : "DESC" } , { column : "created_at" , order : "ASC" } ] } ) ; testQueryBuilder ( "basic `delete`" , ( query ) => query . where ( "email" , Q . eq ( "a@b.com" ) ) . delete ( ) , { wheres : [ { type : WhereType . Default , column : "email" , expression : Q . eq ( "a@b.com" ) } ] , type : QueryType . Delete } ) ; testQueryBuilder ( "basic `update`" , ( query ) => query . where ( "email" , Q . eq ( "a@b.com" ) ) . update ( { name : "John" , age : 16 , is_active : true , birthday : new Date ( "7 July, 2020" ) } ) , { wheres : [ { type : WhereType . Default , column : "email" , expression : Q . eq ( "a@b.com" ) } ] , values : { name : "John" , age : 16 , is_active : true , birthday : new Date ( "7 July, 2020" ) } , type : QueryType . Update } ) ; testQueryBuilder ( "basic `insert`" , ( query ) => query . insert ( { name : "John" , age : 16 , is_active : true , birthday : new Date ( "7 July, 2020" ) } ) , { values : { name : "John" , age : 16 , is_active : true , birthday : new Date ( "7 July, 2020" ) } , type : QueryType . Insert } ) ; testQueryBuilder ( "`insert` with multiple values" , ( query ) => query . insert ( [ { name : "John" , age : 16 , is_active : true , birthday : new Date ( "7 July, 2020" ) } , { name : "Doe" , age : 17 , is_active : false , birthday : new Date ( "8 July, 2020" ) } ] ) , { values : [ { name : "John" , age : 16 , is_active : true , birthday : new Date ( "7 July, 2020" ) } , { name : "Doe" , age : 17 , is_active : false , birthday : new Date ( "8 July, 2020" ) } ] , type : QueryType . Insert } ) ; testQueryBuilder ( "`insert` with returning all columns" , ( query ) => query . insert ( { email : "a@b.com" } ) . returning ( "*" ) , { values : { email : "a@b.com" } , returning : [ "*" ] , type : QueryType . Insert } ) ; testQueryBuilder ( "`insert` with returning several columns" , ( query ) => query . insert ( { email : "a@b.com" } ) . returning ( "email" , "age" ) , { values : { email : "a@b.com" } , returning : [ "email" , "age" ] , type : QueryType . Insert } ) ; testQueryBuilder ( "basic `replace`" , ( query ) => query . replace ( { name : "John" , age : 16 , is_active : true , birthday : new Date ( "7 July, 2020" ) } ) , { values : { name : "John" , age : 16 , is_active : true , birthday : new Date ( "7 July, 2020" ) } , type : QueryType . Replace } ) ; testQueryBuilder ( "`replace` with returning all columns" , ( query ) => query . replace ( { email : "a@b.com" } ) . returning ( "*" ) , { values : { email : "a@b.com" } , returning : [ "*" ] , type : QueryType . Replace } ) ; testQueryBuilder ( "`replace` with returning several columns" , ( query ) => query . replace ( { email : "a@b.com" } ) . returning ( "email" , "age" ) , { values : { email : "a@b.com" } , returning : [ "email" , "age" ] , type : QueryType . Replace } ) ;