Taeluf.com

Query.php

<?php

namespace Phad\Test\Unit;

class Query extends \Phad\Tester {

    /**
     * This tests an sql string that was creating a bug, likely created by there being a new line after :question_id
     */
    public function testBuildSql(){
        $sql = 
        "SELECT candidate.name, candidate.thumb_image_url, answer.summary FROM answer 
            JOIN candidate
                ON answer.candidate_id = candidate.id
            WHERE answer.question_id = :question_id
        ";
        $query = new \Phad\Query();
        $final = $query->buildSql('answer', ['sql'=>$sql], ['question_id'=>'111'],$binds);
        var_dump($final);
        var_dump($binds);

        $this->is_true(isset($binds[':question_id']));
        $this->compare(111, $binds[':question_id']);
        // exit;
    }

    public function testByGETIdForForm(){

        // if (($query_info['name']??null)=='default'&&$args['item_type']=='form'){
            // if (isset($_GET['id'])){
                // $query_info['name']='form_default';
                // $query_info['where'] = "{$item_name}.id = :id";
                // $args['id'] = $_GET['id'];
            // } else {
                // return [ new \Phad\BlackHole() ];
            // }
        // }
        //
        //
        //
        // public function get(string $item_name, $query_info, $args=[]){
        $ldb = \Tlf\LilDb::sqlite();
        $ldb->create('blog', ['id' => 'integer PRIMARY KEY AUTOINCREMENT', 'title'=>'varchar(200)']);

        $ldb->insert('blog', ['title'=>'okay']);
        $ldb->insert('blog', ['title'=>'not-okay', 'id'=>2]);
        $ldb->insert('blog', ['title'=>'depression sucks']);
        $ldb->insert('blog', ['title'=>'Misery exhausts my soul']);
        $ldb->insert('blog', ['title'=>'joy is nice']);
        $ldb->insert('blog', ['title'=>'my friends bring me joy, often times']);

        $query = new \Phad\Query();
        $query->pdo = $ldb->pdo;
        $_GET['id'] = 2;
        $rows = $query->get('Blog', 
            [ 'type'=>'default',
            ],
            [
            ],
            'form',
        );

        $this->compare(
            [
                ['id'=>2, 'title'=>'not-okay'],
            ], 
        $rows);

    }

    public function testEmptyObjectForForm(){

        $query = new \Phad\Query();
        // $query->pdo = new \PDO('sqlite::memory:');
        // Since some other test may set $_GET['id']
        unset($_GET['id']);
        $rows = $query->get('Blog', 
            [
                'type'=>'default',
            ],
            [
            ],
            'form',
        );
        // print_r($rows);
        // exit;

        // $this->is_a($rows[0], '\\Phad\\BlackHole');
        // $this->compare();
        $this->compare($rows[0]['_object'], 'Phad\\BlackHole');

    }

    public function testStructuredQuery(){
        $ldb = \Tlf\LilDb::sqlite();
        $ldb->create('blog', ['id' => 'integer PRIMARY KEY AUTOINCREMENT', 'title'=>'varchar(200)']);

        $ldb->insert('blog', ['title'=>'okay']);
        $ldb->insert('blog', ['title'=>'not-okay']);
        $ldb->insert('blog', ['title'=>'depression sucks']);
        $ldb->insert('blog', ['title'=>'Misery exhausts my soul']);
        $ldb->insert('blog', ['title'=>'joy is nice']);
        $ldb->insert('blog', ['title'=>'my friends bring me joy, often times']);

        $query = new \Phad\Query();
        $query->pdo = $ldb->pdo;
        // $rows = $query->get('Blog',[]);
        $rows = $query->get('Blog', 
            [
                'cols' => 'title',
                'where' => 'Blog.title NOT LIKE :title AND id <> :id',
                'limit' => '1,2',
                'orderby' => 'id ASC',
            ],
            [
                'title'=>'%okay%',
                'id'=>5
            ]
        );

        $this->compare(
            [
                ['title'=>'Misery exhausts my soul'],
                ['title'=>'my friends bring me joy, often times'],
            ], 
        $rows);
    }

    public function testSqlBinds(){
        $ldb = \Tlf\LilDb::sqlite();
        $ldb->create('blog', ['title'=>'varchar(200)']);
        $ldb->insert('blog', ['title'=>'okay']);
        $ldb->insert('blog', ['title'=>'not-okay']);
        $ldb->insert('blog', ['title'=>'depression sucks']);
        $ldb->insert('blog', ['title'=>'Misery exhausts my soul']);

        $query = new \Phad\Query();
        $query->pdo = $ldb->pdo;
        $rows = $query->get('Blog', 
            ['sql'=>"SELECT * FROM blog WHERE title LIKE :title"],
            ['title'=>'%okay%']
        );

        $this->compare([['title'=>'okay'], ['title'=>'not-okay']], $rows);
    }

    public function testFullQuery(){
        $ldb = \Tlf\LilDb::sqlite();
        $ldb->create('blog', ['title'=>'varchar(200)']);
        $ldb->insert('blog', ['title'=>'okay']);
        $ldb->insert('blog', ['title'=>'not-okay']);
        $ldb->insert('blog', ['title'=>'depression sucks']);
        $ldb->insert('blog', ['title'=>'Misery exhausts my soul']);

        $query = new \Phad\Query();
        $query->pdo = $ldb->pdo;
        $rows = $query->get('Blog', 
            ['sql'=>"SELECT * FROM blog WHERE title LIKE '%okay%'"],
        );

        $this->compare([['title'=>'okay'], ['title'=>'not-okay']], $rows);
    }

    public function testSimpleQuery(){
        $ldb = \Tlf\LilDb::sqlite();
        $ldb->create('blog', ['title'=>'varchar(200)']);
        $ldb->insert('blog', ['title'=>'okay']);
        $ldb->insert('blog', ['title'=>'not-okay']);

        $query = new \Phad\Query();
        $query->pdo = $ldb->pdo;
        $rows = $query->get('Blog', []);

        $this->compare([['title'=>'okay'], ['title'=>'not-okay']], $rows);
    }

    public function testSelectAll(){
        $ldb = \Tlf\LilDb::sqlite();
        $ldb->create('blog', ['title'=>'varchar(200)']);
        $ldb->insert('blog', ['title'=>'okay']);
        $ldb->insert('blog', ['title'=>'not-okay']);

        $query = new \Phad\Query();
        $query->pdo = $ldb->pdo;
        $rows = $query->get('Blog', []);

        $this->compare([['title'=>'okay'], ['title'=>'not-okay']], $rows);
    }

    /**
     * @test that when passing both an Item & an ItemList, the ItemList is returned, not the individual item
     */
    public function testPreferListOverItem(){
        $query = new \Phad\Query();

        $Blog1 = ['title'=>'cat', 'type'=>'custom', 'description'=>'fahlahlah'];
        $Blog2 = ['title'=>'dog', 'type'=>'custom', 'description'=>'trot trot trot'];

        $rows = $query->get('Blog', [], 
            ['Blog'=>$Blog1, 
            'BlogList'=>[$Blog1,$Blog2]
            ]
        );


        $this->compare([$Blog1, $Blog2], $rows);
    }

    /**
     * @test that if an item list is passed in the args array, it will be returned instead of running a query
     */
    public function testSkipQueryWhenItemListPassed(){

        $query = new \Phad\Query();

        $Blog1 = ['title'=>'cat', 'type'=>'custom', 'description'=>'fahlahlah'];
        $Blog2 = ['title'=>'dog', 'type'=>'custom', 'description'=>'trot trot trot'];

        $query_info = [
            'table' => 'blog',
            'where' => 'Blog.type LIKE :type',
            'limit' => '0,2',
        ];

        $rows = $query->get('Blog', $query_info, ['BlogList'=>[$Blog1,$Blog2]]);


        $this->compare([$Blog1,$Blog2], $rows);
    }

    /**
     * @test that if an item is passed in the args array, it will be returned instead of running a query
     */
    public function testSkipQueryWhenItemPassed(){
        $query = new \Phad\Query();

        $Blog = ['title'=>'cat', 'type'=>'custom', 'description'=>'fahlahlah'];

        $query_info = [
            'where' => 'Blog.type LIKE :type',
            'limit' => '0,2',
        ];

        $rows = $query->get('Blog', $query_info, ['Blog'=>$Blog]);


        $this->compare([$Blog], $rows);
    }
}