setType($type); $this->database->select("account", "user_name", [ "email" => "foo@bar.com", "user_id" => 200, "user_id[>]" => 200, "user_id[>=]" => 200, "user_id[!]" => 200, "age[<>]" => [200, 500], "age[><]" => [200, 500], "income[>]" => Medoo::raw("COUNT()"), "remote_id" => Medoo::raw("UUID()"), "location" => null, "is_selected" => true ]); $this->assertQuery( << 200 AND "user_id" >= 200 AND "user_id" != 200 AND ("age" BETWEEN 200 AND 500) AND ("age" NOT BETWEEN 200 AND 500) AND "income" > COUNT("average") AND "remote_id" = UUID() AND "location" IS NULL AND "is_selected" = 1 EOD, $this->database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testBetweenDateTimeWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "birthday[<>]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d", mktime(0, 0, 0, 1, 1, 2045))] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testNotBetweenDateTimeWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "birthday[><]" => [date("Y-m-d", mktime(0, 0, 0, 1, 1, 2015)), date("Y-m-d", mktime(0, 0, 0, 1, 1, 2045))] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testBetweenStringWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "location[<>]" => ['New York', 'Santo'] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testBetweenRawWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "birthday[<>]" => [ Medoo::raw("to_date(:from, 'YYYY-MM-DD')", [":from" => '2015/05/15']), Medoo::raw("to_date(:to, 'YYYY-MM-DD')", [":to" => '2025/05/15']) ] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testGreaterDateTimeWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "birthday[>]" => date("Y-m-d", mktime(0, 0, 0, 1, 1, 2045)) ]); $this->assertQuery( << '2045-01-01' EOD, $this->database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testArrayIntValuesWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "user_id" => [2, 123, 234, 54] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testArrayStringValuesWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "email" => ["foo@bar.com", "cat@dog.com", "admin@medoo.in"] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testRawArrayValuesWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ 'id' => [ Medoo::raw('LOWER("FOO")'), Medoo::raw('LOWER("BAR")') ] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testRawNotInArrayValuesWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ 'id[!]' => [ Medoo::raw('LOWER("FOO")'), Medoo::raw('LOWER("BAR")') ] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testNegativeWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "AND" => [ "user_name[!]" => "foo", "user_id[!]" => 1024, "email[!]" => ["foo@bar.com", "admin@medoo.in"], "city[!]" => null, "promoted[!]" => true, "location[!]" => Medoo::raw('LOWER("New York")') ] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testBasicAndRelativityWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "AND" => [ "user_id[>]" => 200, "gender" => "female" ] ]); $this->assertQuery( << 200 AND "gender" = 'female') EOD, $this->database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testBasicSingleRelativityWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "user_id[>]" => 200, "gender" => "female" ]); $this->assertQuery( << 200 AND "gender" = 'female' EOD, $this->database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testBasicOrRelativityWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "OR" => [ "user_id[>]" => 200, "age[<>]" => [18, 25], "gender" => "female" ] ]); $this->assertQuery( << 200 OR ("age" BETWEEN 18 AND 25) OR "gender" = 'female') EOD, $this->database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testCompoundRelativityWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "AND" => [ "OR" => [ "user_name" => "foo", "email" => "foo@bar.com" ], "password" => "12345" ] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testCompoundDuplicatedKeysWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "AND #comment" => [ "OR #first comment" => [ "user_name" => "foo", "email" => "foo@bar.com" ], "OR #sencond comment" => [ "user_name" => "bar", "email" => "bar@foo.com" ] ] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testColumnsRelationshipWhere($type) { $this->setType($type); $this->database->select("post", [ "[>]account" => "user_id", ], [ "post.content" ], [ "post.restrict[<]account.age", "post.type[=]account.type" ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testBasicLikeWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "city[~]" => "lon", "name[~]" => "some-name" ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testGroupedLikeWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "city[~]" => ["lon", "foo", "bar"] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testNegativeLikeWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "city[!~]" => "lon" ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testNonEscapeLikeWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "city[~]" => "some_where", "county[~]" => "[a-f]stan" ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testEscapeLikeWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "city[~]" => "some\_where" ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testCompoundLikeWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "content[~]" => ["AND" => ["lon", "on"]], "city[~]" => ["OR" => ["lon", "on"]] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testWildcardLikeWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "city[~]" => "%stan", "company[~]" => "Goo%", "location[~]" => "Londo_", "name[~]" => "[BCR]at", "nickname[~]" => "[!BCR]at" ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testMultipleLikeWhere($type) { $this->setType($type); $words = [ "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", "eleven", "twelve" ]; $this->database->select("account", ["title"], ["title[~]" => $words]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testBasicOrderWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "ORDER" => "user_id" ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testMultipleOrderWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "ORDER" => [ // Order by column with sorting by customized order. "user_id" => [43, 12, 57, 98, 144, 1], // Order by column. "register_date", // Order by column with descending sorting. "profile_id" => "DESC", // Order by column with ascending sorting. "date" => "ASC" ] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testOrderWithRawWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ "ORDER" => Medoo::raw(", ") ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() */ public function testFullTextSearchWhere() { $this->setType("mysql"); $this->database->select("account", "user_name", [ "MATCH" => [ "columns" => ["content", "title"], "keyword" => "foo", "mode" => "natural" ] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testRegularExpressionWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ 'user_name[REGEXP]' => '[a-z0-9]*' ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testRawWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ 'datetime' => Medoo::raw('NOW()') ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testLimitWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ 'LIMIT' => 100 ]); $this->assertQuery([ 'default' => << << <<database->queryString); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testLimitOffsetWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ 'LIMIT' => [20, 100] ]); $this->assertQuery([ 'default' => << << <<database->queryString); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testGroupWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ 'GROUP' => 'type', ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testGroupWithArrayWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ 'GROUP' => [ 'type', 'age', 'gender' ] ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testGroupWithRawWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ 'GROUP' => Medoo::raw(", ") ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testHavingWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ 'HAVING' => [ 'user_id[>]' => 500 ] ]); $this->assertQuery( << 500 EOD, $this->database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testHavingWithRawWhere($type) { $this->setType($type); $this->database->select("account", "user_name", [ 'HAVING' => Medoo::raw(' = LOWER("NEW YORK")') ]); $this->assertQuery( <<database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testHavingWithAggregateRawWhere($type) { $this->setType($type); $this->database->select("account", [ "total" => Medoo::raw('SUM()') ], [ 'HAVING' => Medoo::raw('SUM() > 1000') ]); $this->assertQuery( << 1000 EOD, $this->database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testRawWhereClause($type) { $this->setType($type); $this->database->select( "account", "user_name", Medoo::raw("WHERE => 10") ); $this->assertQuery( << 10 EOD, $this->database->queryString ); } /** * @covers ::select() * @covers ::dataImplode() * @covers ::whereClause() * @dataProvider typesProvider */ public function testRawWhereWithJoinClause($type) { $this->setType($type); $this->database->select( "post", [ "[>]account" => "user_id", ], [ "post.content" ], Medoo::raw("WHERE => 10") ); $this->assertQuery( << 10 EOD, $this->database->queryString ); } }