Building a Dynamic Query Builder Using PHP __call and __callStatic Methods

ওয়েব ডেভেলপমেন্টের ডাইনামিক ল্যান্ডস্কেপে, ফ্লেক্সিবল এবং শক্তিশালী ডাটাবেস Query Builder তৈরি করা শক্তিশালী অ্যাপ্লিকেশন তৈরির একটি ভিত্তি। পিএইচপি, একটি বহুমুখী সার্ভার-সাইড স্ক্রিপ্টিং ল্যাঙ্গুয়েজ, ডাটাবেসের সাথে ইন্টারঅ্যাক্ট করার জন্য বিভিন্ন পদ্ধতির প্রস্তাব করে। এমন একটি পদ্ধতি যা এর elegance এবং expressiveness অর্থাৎ অভিব্যক্তির জন্য দাঁড়িয়েছে তা হল __call এবং __callStatic magic methods এর ব্যবহার।

এমন একটি সিনারিও কল্পনা করুন যেখানে এসকিউএল কোয়েরি তৈরি করা একটি মেথড কল করার মতোই স্বজ্ঞাত হয়ে ওঠে। PHP এর ম্যাজিক মেথড গুলো ব্যবহার করে আপনি একটি QueryBuilder ক্লাস তৈরি করতে পারেন। এই আর্টিকেলে , আমরা একটি Dynamic Query Builder ডিজাইন করার সময় এর জটিলতাগুলিকে সমাধান করতে __call এবং __callStatic ম্যাজিক মেথডকে কাজে লাগাব।

SELECT কোয়েরি তৈরির প্রাথমিক বিষয়গুলি থেকে শুরু করে advance ফীচারগুলি যেমন ordering, limiting, এবং টেবিল গুলো জয়েন দেওয়া, এই আর্টিকেলটি টি আপনাকে একটি বহুমুখী এবং user-friendly query builder তৈরির মাধ্যমে গাইড করবে৷ আমরা আপনার ডাটাবেসের সাথে ইন্টারঅ্যাক্ট করার জন্য একটি পরিষ্কার এবং অভিব্যক্তিপূর্ণ সিনট্যাক্স প্রদান করে বিভিন্ন ডাটাবেস ক্রিয়াকলাপগুলিকে গতিশীলভাবে পরিচালনা করার পিছনের ম্যাজিকটি উন্মোচন করব।

আপনি একজন অভিজ্ঞ ডেভেলপার যা আপনার টুলকিট বাড়ানোর জন্য খুঁজছেন বা dynamic query building এর principles গুলি উপলব্ধি করতে আগ্রহী একজন নবাগত হোক না কেন, এই যাত্রা আপনাকে আপনার ডাটাবেস ইন্টারঅ্যাকশনকে স্ট্রিমলাইন করার জন্য নলেজ এবং স্কিল দিয়ে সজ্জিত করবে। চলুন একটি ডায়নামিক কোয়েরি বিল্ডার তৈরির শিল্পে গভীরভাবে চিন্তা করি এবং দক্ষ এবং পঠনযোগ্য ডাটাবেস কোয়েরি তৈরির জন্য পিএইচপি-এর ম্যাজিক মেথড গুলোকে ব্যবহার করি।

<?php 
class QueryBuilder
{
    private $pdo;
    private $table;
    private $rawSQL;
    private $select=[];
    private $orderBy=[];
    private $inRandomOrder;
    private $limit;
    private $offset;
    private $groupBy=[];
    private $having=[];
    private $havingBetween=[];
    private $where=[];
    private $orWhere=[];
    private $whereBetween=[];
    private $whereNotBetween=[];
    private $whereBetweenColumns = [];
    private $whereNotBetweenColumns=[];
    private $whereIn=[];
    private $whereNotIn=[];
    private $whereNull=[];
    private $whereNotNull=[];
    private $whereDate=[];
    private $whereMonth=[];
    private $whereDay=[];
    private $whereYear=[];
    private $whereTime=[];
    private $whereColumn=[];
    private $innerJoins=[];
    private $leftJoins=[];
    private $rightJoins=[];

    public function __construct(PDO $pdo, $table)
    {
        $this->pdo=$pdo;
        $this->table=$table;
    }


    public function select(...$columns)
    {
        $this->select=$columns;
        return $this;
    }

    public function innerJoin($table, $on, $alias = null)
    {
        $this->innerJoins[] = compact('table', 'on', 'alias');
        return $this;
    }

    public function leftJoin($table, $on, $alias = null)
    {
        $this->leftJoins[] = compact('table', 'on', 'alias');
        return $this;
    }

    public function rightJoin($table, $on, $alias = null)
    {
        $this->rightJoins[] = compact('table', 'on', 'alias');
        return $this;
    }

    public function where($column, $operator, $value=null)
    {
        if($value === null)
        {
            $value =  $operator;
            $operator= '=';
        }

        $this->where[]=compact('column', 'operator', 'value');
        return $this;
    }

    public function orWhere($column, $operator, $value=null)
    {
        if($value === null)
        {
            $value =  $operator;
            $operator= '=';
        }

        $this->orWhere[]=compact('column', 'operator', 'value');
        return $this;
    }
    
    public function whereBetween($column, array $values)
    {
        $this->whereBetween [] = compact('column', 'values');
        return $this;
    }

    public function whereNotBetween($column, array $values)
    {
        $this->whereNotBetween [] = compact('column', 'values');
        return $this;
    }

    public function whereBetweenColumns($column, array $rangeColumns)
    {
        $this->whereBetweenColumns[]=compact('column','rangeColumns');
        return $this;
    }

    public function whereNotBetweenColumns($column, array $rangeColumns)
    {
        $this->whereNotBetweenColumns[]=compact('column','rangeColumns');
        return $this;
    }

    public function whereIn($column, array $values){
        $this->whereIn[]=compact('column','values');
        return $this;
    }

    public function whereNotIn($column, array $values){
        $this->whereNotIn[]=compact('column','values');
        return $this;
    }

    public function whereNull($column){
        $this->whereNull[]=$column;
        return $this;
    }

    public function whereNotNull($column){
        $this->whereNotNull[]=$column;
        return $this;
    }
    public function whereDate($column, $date)
    {
        $this->whereDate[]=compact('column','date');
        return $this;
    }
    public function whereMonth($column, $month)
    {
        $this->whereMonth[]=compact('column','month');
        return $this;
    }
    public function whereDay($column, $day)
    {
        $this->whereDay[]=compact('column','day');
        return $this;
    }
    public function whereYear($column, $year)
    {
        $this->whereYear[]=compact('column','year');
        return $this;
    }
    public function whereTime($column, $operator, $time)
    {
        $this->whereTime[]=compact('column','operator','time');
        return $this;
    }

    public function whereColumn($firstColumn, $operatorOrSecondColumn, $secondColumn=null)
    {
        if($secondColumn===null)
        {
            $operator= '=';
            $secondColumn=$operatorOrSecondColumn;
        }else{
            $operator = $operatorOrSecondColumn;
        }
        $this->whereColumn[] = compact('firstColumn','operator','secondColumn');
        return $this;
    }

    public function orderBy($column, $direction = 'asc')
    {
        $this->orderBy[]=compact('column','direction');
        return $this;
    }

    public function inRandomOrder()
    {
        $this->inRandomOrder = true;
        return $this;
    }

    public function limit($count)
    {
        $this->limit=$count;
        return $this;
    }
    public function skip($offset)
    {
        $this->offset=$offset;
        return $this;
    }
    public function take($count)
    {
        $this->limit($count);
        return $this;
    }
    public function count()
    {
        $this->select('COUNT(*) as count');
        $result = $this->limit(1)->get();
        return isset($result[0]['count'])? (int) $result[0]['count']:0;
    }

    public function find($findColumn, $findValue)
    {
        $this->where($findColumn,$findValue);
        $result = $this->get();
        return !empty($result) ? $result[0] : null;
    }

    public function value($column)
    {
        $this->select($column);
        $result= $this->limit(1)->get();
        return isset($result[0][$column])?$result[0][$column]:null;
    }

    public function max($column)
    {
        $this->select("MAX($column) as max");
        $result = $this->limit(1)->get();
        return isset($result[0]['max'])?$result[0]['max']:null;
    }

    public function avg($column)
    {
        $this->select("AVG($column) as avg");
        $result = $this->limit(1)->get();
        return isset($result[0]['avg'])?$result[0]['avg']:null;
    }

    public function groupBy(...$columns)
    {
        $this->groupBy = array_merge($this->groupBy, $columns);
        return $this;
    }

    public function having($column, $operator, $value)
    {
        $this->having[] = compact('column','operator','value');
        return $this;
    }

    public function havingBetween($column, array $values)
    {
        $this->havingBetween[]=compact('column','values');
        return $this;
    }


    public function get()
    {
        /* $this->prepareRawSQL();
        return $this->rawSQL;  */
        $sql="SELECT ";
        
        if(!empty($this->select))
        {
            $sql .= implode(", ",$this->select);
        }else{
            $sql .= "*";
        }
        
        $sql .= " FROM {$this->table}";

        if(!empty($this->innerJoins))
        {
            foreach($this->innerJoins as $join)
            {
                $sql .=" INNER JOIN {$join['table']}";

                if($join['alias'])
                {
                    $sql .=" AS {$join['alias']}";
                }

                $sql .=" ON {$join['on']}";
            }
        }

        if(!empty($this->leftJoins))
        {
            foreach($this->leftJoins as $join)
            {
                $sql .=" LEFT JOIN {$join['table']}";

                if($join['alias'])
                {
                    $sql .=" AS {$join['alias']}";
                }

                $sql .=" ON {$join['on']}";
            }
        }

        if(!empty($this->rightJoins))
        {
            foreach($this->rightJoins as $join)
            {
                $sql .=" RIGHT JOIN {$join['table']}";

                if($join['alias'])
                {
                    $sql .=" AS {$join['alias']}";
                }

                $sql .=" ON {$join['on']}";
            }
        }

        $params=[];

        if(!empty($this->orWhere))
        {
            $sql .=' OR';
            foreach($this->orWhere as $condition)
            {
                $sql .= " {$condition['column']} {$condition['operator']} :{$condition['column']} OR";
                $params[":{$condition['column']}"]=$condition['value'];
            }
            $sql =rtrim($sql, ' OR');
        
            //Reset Where
            $this->orWhere = [];
        }
        

        if(!empty($this->whereBetween))
        {
            $sql .=" WHERE";
            foreach($this->whereBetween as $condition){
                $sql .=" {$condition['column']} BETWEEN :{$condition['column']}_start AND :{$condition['column']}_end AND";
                $params[":{$condition['column']}_start"] = $condition['values'][0];
                $params[":{$condition['column']}_end"] = $condition['values'][1];
            }
            $sql = rtrim($sql, " AND");
        }

        if(!empty($this->whereNotBetween))
        {
            $sql .=" WHERE";
            foreach($this->whereNotBetween as $condition){
                $sql .=" {$condition['column']} NOT BETWEEN :{$condition['column']}_start AND :{$condition['column']}_end AND";
                $params[":{$condition['column']}_start"] = $condition['values'][0];
                $params[":{$condition['column']}_end"] = $condition['values'][1];
            }
            $sql = rtrim($sql, " AND");
        }

        if(!empty($this->whereBetweenColumns))
        {
            $sql .=" WHERE";
            foreach($this->whereBetweenColumns as $condition)
            {
                $sql .=" {$condition['column']} BETWEEN {$condition['rangeColumns'][0]} AND {$condition['rangeColumns'][1]} AND";
            }
            $sql = rtrim($sql, ' AND');
        }

        if(!empty($this->whereNotBetweenColumns))
        {
            $sql .=" WHERE";
            foreach($this->whereNotBetweenColumns as $condition)
            {
                $sql .=" {$condition['column']} NOT BETWEEN {$condition['rangeColumns'][0]} AND {$condition['rangeColumns'][1]} AND";
            }
            $sql = rtrim($sql, ' AND');
        }

        if(!empty($this->whereIn))
        {
            $sql .=' WHERE ';
            foreach($this->whereIn as $condition)
            {
                $sql .="{$condition['column']} IN (:" . implode(', :', $condition['values']) .") AND";
                foreach( $condition['values'] as $value)
                {
                    $params[":$value"]=$value;
                }
            }
            $sql = rtrim($sql, ' AND');
        }

        if(!empty($this->whereNotIn))
        {
            $sql .=' WHERE ';
            foreach($this->whereNotIn as $condition)
            {
                $sql .="{$condition['column']} NOT IN (:" . implode(', :', $condition['values']) .") AND";
                foreach( $condition['values'] as $value)
                {
                    $params[":$value"]=$value;
                }
            }
            $sql = rtrim($sql, ' AND');
        }

            if(!empty($this->whereNull))
            {
                $sql .=" WHERE ";
                foreach($this->whereNull as $column)
                {
                    $sql .= "{$column} IS NULL AND";
                }
                $sql = rtrim($sql, " AND");
            }

            if(!empty($this->whereNotNull))
            {
                $sql .=" WHERE ";
                foreach($this->whereNotNull as $column)
                {
                    $sql .= "{$column} IS NOT NULL AND";
                }
                $sql = rtrim($sql, " AND");
            }
        
        if(!empty($this->whereDate))
        {
            $sql .=' WHERE ';
            foreach($this->whereDate as $condition)
            {
                $sql .="DATE({$condition['column']}) = '{$condition['date']}' AND";
            }
            $sql =rtrim($sql, ' AND');
        }

        if(!empty($this->whereMonth))
        {
            $sql .=' WHERE ';
            foreach($this->whereMonth as $condition)
            {
                $sql .="MONTH({$condition['column']}) = '{$condition['month']}' AND";
            }
            $sql =rtrim($sql, ' AND');
        }

        if(!empty($this->whereDay))
        {
            $sql .=' WHERE ';
            foreach($this->whereDay as $condition)
            {
                $sql .="DAY({$condition['column']}) = '{$condition['day']}' AND";
            }
            $sql =rtrim($sql, ' AND');
        }

        if(!empty($this->whereYear))
        {
            $sql .=' WHERE ';
            foreach($this->whereYear as $condition)
            {
                $sql .="YEAR({$condition['column']}) = '{$condition['year']}' AND";
            }
            $sql =rtrim($sql, ' AND');
        }

        if(!empty($this->whereTime))
        {
            $sql .=' WHERE ';
            foreach($this->whereTime as $condition)
            {
                $sql .="TIME({$condition['column']}) {$condition['operator']} '{$condition['time']}' AND";
            }
            $sql =rtrim($sql, ' AND');
        }

        if(!empty($this->whereColumn))
        {
            $sql .=' WHERE ';
            foreach($this->whereColumn as $condition)
            {
                $sql .="{$condition['firstColumn']} {$condition['operator']} {$condition['secondColumn']} AND";
            }
            $sql = rtrim($sql, ' AND');
        }


        if(isset($this->limit))
        {
            $sql .=" LIMIT {$this->limit}";
        }

        if(isset($this->offset))
        {
            $sql .=" OFFSET {$this->offset}";
        }

        if (!empty($this->groupBy)) {
            $sql .= ' GROUP BY ' . implode(', ', $this->groupBy);
        }

        if(!empty($this->orderBy))
        {
            $sql .=' ORDER BY ';
            foreach($this->orderBy as $order)
            {
                $sql .= "{$order['column']} {$order['direction']}, ";
            }
            $sql =rtrim($sql, ', ');
        }elseif($this->inRandomOrder){
            $sql = "SELECT * FROM ({$sql} ORDER BY RAND()) AS subquery";
        }

        //Add HAVING clause

        if(!empty($this->having))
        {
            $sql .=' HAVING ';
            foreach($this->having as $condition)
            {
                $sql .= "{$condition['column']} {$condition['operator']} '{$condition['value']}' AND ";
            }
            $sql = rtrim($sql, 'AND ');
        }

        //Add HAVING BETWEEN clause
        if(!empty($this->havingBetween))
        {
            $sql .=' HAVING ';
            foreach($this->havingBetween as $condition)
            {
                $column = $condition['column'];
                $values = $condition['values'];
                $sql .= "{$column} BETWEEN {$values[0]} AND {$values[1]} AND ";
            }
            $sql = rtrim($sql, 'AND ');
        }
        echo $sql;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC); 
    }


    public function __call($method,$args)
    {
        throw new BadFunctionCallException("Method {$method}() does not exist");
    }

    public static function __callStatic($method,$args)
    {
        throw new BadFunctionCallException("Static Method {$method}() does not exist");
    }

    public function dumpRawSQL()
    {
        $this->prepareRawSQL();
        echo $this->rawSQL;
    }

    private function prepareRawSQL()
    {
        $sql="SELECT ";
        
        //Add Selected Columns
        if(!empty($this->select))
        {
            $sql .= implode(", ",$this->select);
        }else{
            $sql .= "*";
        }

        //Add FROM clause
        $sql .= " FROM {$this->table}";

        //Add ORDER BY clause
        if(!empty($this->orderBy))
        {
            $sql .=' ORDER BY ';
            foreach($this->orderBy as $order)
            {
                $sql .= "{$order['column']} {$order['direction']}, ";
            }
            $sql =rtrim($sql, ', ');
        }


        $this->rawSQL=$sql;
        
    }
}

 

আমাদের dbconfig.php ফাইলের কোড গুলো হবে নিম্নরুপঃ

<?php
return [
    "host" => "localhost",
    "dbname" => "e_commerce",
    "username" => "root",
    "password" => "",
];

আমাদের Db.php ফাইলের কোড গুলো হবে নিম্নরুপঃ

<?php 
class Db
{
    private static $pdo;
    public static function connect(){
        if(!isset(self::$pdo))
        {
            extract(require_once 'dbConfig.php');
            $dsn="mysql:host=$host;dbname=$dbname";

            try{
                self::$pdo=new PDO($dsn,$username,$password,[
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_EMULATE_PREPARES => false,
                ]);
            }catch (PDOException $e){
                throw new RuntimeException("Database connectio error: ".$e->getMessage());
            }
        }
        return self::$pdo;
    }

    public static function table($tableName)
    {
        return new QueryBuilder(self::connect(),$tableName);
    }

    public function __call($method,$args)
    {
        throw new BadFunctionCallException("Method {$method}() does not exist");
    }

    public static function __callStatic($method,$args)
    {
        throw new BadFunctionCallException("Static Method {$method}() does not exist");
    }

}

আমাদের testQuery.php ফাইলের কোড গুলো হবে নিম্নরুপঃ

<?php 
require_once "Db.php";
require_once 'QueryBuilder.php';

try{
    //$users=DB::table('users');
    //$users->select('name','age')->dumpRawSQL();
    //$allUsers=$users->select('name','age')->get();
    //$allUsers=$users->select('name','email','age','status')->get();
    /* $allUsers=$users->get();
    print_r($allUsers); */


    // Retrive all rows with orderBy ASC
    //$allUsersWithOrderBy=$users->orderBy('age')->get();
    /* $allUsersWithOrderBy=$users->select('name','age')->orderBy('age')->get();
    print_r($allUsersWithOrderBy);
 */    //$users->orderBy('name','DESC')->dumpRawSQL();

    // Retrive all rows with orderBy DESC
    /* $allUsersWithOrderBy=$users->orderBy('age', 'DESC')->get();
    print_r($allUsersWithOrderBy);
 */
    // Retrive all rows with MultiColumn orderBy

    /* $orderByMultiColumn=$users
                        ->orderBy('name','DESC')
                        ->orderBy('age','asc')
                        ->get();

print_r($orderByMultiColumn); */

//$orderBywithSelectedColumn=$users->select("id","name","age","email")->orderBy("age")->get();
/* $randomOrder=$users->select("id","name","age","email")->inRandomOrder()->get();
print_r($randomOrder);
 */
//$limitedUsers = $users->limit(3)->get();
//print_r($limitedUsers);

//echo $users->limit(3)->dumpRawSQL();

/* $limitedUsersWithSkip=$users->skip(2)->take(3)->get();

print_r($limitedUsersWithSkip); */

//echo $users->count();

/* $users=DB::table("users");

$result=$users
        ->select('age','COUNT(*) as total')
        ->groupBy('age')
        ->get();

print_r($result); */

//Before use below example, please connect classicmodels database
 /* $orders = Db::table('orders');

$result = $orders
    ->select('status', 'COUNT(*) as total')
    ->groupBy('status')
    ->get();

print_r($result);
 */

/*  $orders = Db::table('orders');

 $result = $orders
     ->select('status', 'COUNT(*) as total')
     ->groupBy('status')
     ->having('total', '>', 4)
     ->get();
 
 print_r($result);
 */ 

/* $orders = Db::table('orders');

$result = $orders
    ->select('YEAR (orderDate) AS year', "COUNT(orderNumber) as 'Total Order'")
    ->groupBy('year')
    ->get();

print_r($result);  */


/* $orders = Db::table('orders');

$result = $orders
    ->select('YEAR (orderDate) AS year', "COUNT(orderNumber) as 'total_order'")
    ->groupBy('year')
    ->having('year','>','2003')
    ->get();

print_r($result);  */

/* $orders = Db::table('orders');

$result = $orders
    ->select('YEAR (orderDate) AS year', "COUNT(orderNumber) as 'total_order'")
    ->groupBy('year')
    ->havingBetween('year',[2003,2004])
    ->get();

print_r($result); */

/* $orders = Db::table('orders');

$report=$orders
        ->select('customerNumber','COUNT(customerNumber) as number_of_orders')
        ->groupBy('customerNumber')
        ->having('number_of_orders','>',3)
        ->get();
print_r($report); */

 /* $orders = Db::table('orders');

$report=$orders
        ->select('customerNumber','COUNT(customerNumber) as number_of_orders')
        ->groupBy('customerNumber')
        ->havingBetween('number_of_orders',[5,6])
        ->get();
print_r($report); */

//$customers=Db::table('customers');

//$whereFilter=$customers->where('customerNumber', 114)->get();
/* $whereFilter=$customers
            ->select('customerNumber','customerName','phone','country','creditLimit')
            ->where('creditLimit', '>', 50000)
            ->where('country','France')
            ->get(); */

/* $whereFilter=$customers
            ->select('customerNumber','customerName','phone','country','creditLimit')
            ->where('creditLimit', '>', 50000)
            ->orWhere('country','France')
            ->get(); */

//print_r($whereFilter);

/* $whereBetweenFilter=$customers
                    ->select('customerNumber','customerName','creditLimit')
                    ->whereBetween('creditLimit',[25000,50000])
                    ->get();

print_r($whereBetweenFilter); */

/* $whereBetweenFilter=$customers
                    ->select('customerNumber','customerName','creditLimit','country')
                    ->whereNotBetween('creditLimit',[25000,100000])
                    ->get();

print_r($whereBetweenFilter); */

//$patients=Db::table('patients');

/* $result=$patients
        ->select('name','weight','minimum_allowed_weight','maximum_allowed_weight')
        ->whereBetweenColumns('weight',['minimum_allowed_weight','maximum_allowed_weight'])
        ->get();

print_r($result); */

/* $result=$patients
        ->select('name','weight','minimum_allowed_weight','maximum_allowed_weight')
        ->whereNotBetweenColumns('weight',['minimum_allowed_weight','maximum_allowed_weight'])
        ->get();

print_r($result); */

/* $sql="SELECT * FROM customers WHERE country in('USA','UK','France')";

$sql = "SELECT * FROM customers WHERE country ='USA' OR country ='UK' OR country='France'";
 */

/*  $whereInFilter=$customers
                ->select('customerName','country','city')
                ->whereIn('country',['France','Germany','Spain'])
                ->get();

print_r($whereInFilter); */

/* $whereNotInFilter=$customers
->select('customerName','country','city')
->whereNotIn('country',['France','Germany','Spain','USA'])
->get();

print_r($whereNotInFilter); */

/* $whereNull=$customers
            ->select('customerName','country','state','city')
            ->whereNull('state')
            ->get();

print_r($whereNull); */

/* $whereNotNull=$customers
            ->select('customerName','country','state','city')
            ->whereNotNull('state')
            ->get();

print_r($whereNotNull);
 */

 //$orders=Db::table('orders');

 /* $whereDateFilter=$orders
                    ->select('orderNumber','customerNumber','orderDate','shippedDate')
                    ->whereDate('orderDate','2005-04-15')
                    ->get();

print_r($whereDateFilter); */

/* $whereMonthFilter=$orders
                    ->select('orderNumber','customerNumber','orderDate','shippedDate')
                    ->whereMonth('orderDate','04')
                    ->get();

print_r($whereMonthFilter); */

/* $whereDayFilter=$orders
                    ->select('orderNumber','customerNumber','orderDate','shippedDate')
                    ->whereDay('orderDate','15')
                    ->get();

print_r($whereDayFilter); */

/* $whereYearFilter=$orders
                    ->select('orderNumber','customerNumber','orderDate','shippedDate')
                    ->whereYear('orderDate','2004')
                    ->get();

print_r($whereYearFilter); */

//$users=Db::table("users");

/* $whereTimeFilter=$users
                    ->whereTime('created_at','=','17:15:24')
                    ->get();

print_r($whereTimeFilter); */

/* $whereColumnFilter=$users
                    ->whereColumn('created_at','updated_at')
                    ->get(); */

/* $whereColumnFilter=$users
                    ->whereColumn('created_at','<','updated_at')
                    ->get();
 */

 /* $patients=Db::table("patients");
 $whereColumnFilter=$patients
                    ->whereColumn('weight','<','minimum_allowed_weight')
                    ->get();

print_r($whereColumnFilter); */

/* $users=Db::table("users")
        ->count();

echo $users; */

/* $users=Db::table('users')
        ->where("email","bob@example.com")->value('name'); */

//echo Db::table('users')->avg('age');

/* INNER JOIN EXAMPLES */
//Example Usage 1:

/* 
SELECT 
	status, 
    SUM(quantityOrdered*priceEach) AS amount,
    quantityOrdered as 'Total Order'
FROM 
	`orders` 
INNER JOIN 
	orderdetails
ON
	orders.orderNumber=orderdetails.orderNumber 
GROUP BY status;
*/

/* $query1=Db::table('orders')
        ->select('status', 'SUM(quantityOrdered*priceEach) AS Amount', 'quantityOrdered')
        ->innerJoin('orderdetails','orders.orderNumber = orderdetails.orderNumber')
        ->groupBy('status')
        ->get();

print_r($query1); */

//Example Usage: 2
/* 
SELECT 
	YEAR(orderDate) AS year, 
    SUM(quantityOrdered*priceEach) AS 'Total Amount',
    SUM(quantityOrdered) as 'Total Order'
FROM 
	`orders` 
INNER JOIN 
	orderdetails
ON
	orders.orderNumber=orderdetails.orderNumber
WHERE status = 'Shipped'
GROUP BY YEAR(orderDate);
*/

/* $query2=Db::table('orders')
        ->select("YEAR(orderDate) AS year", "SUM(quantityOrdered*priceEach) AS 'Total Amount'", "SUM(quantityOrdered) as 'Total Order'")
        ->innerJoin('orderdetails','orders.orderNumber = orderdetails.orderNumber')
        ->where('status','Shipped')
        ->groupBy('year')
        ->get();

print_r($query2);  */

//Example Usage: 3
/* 
SELECT 
	YEAR(orderDate) AS year, 
    SUM(quantityOrdered*priceEach) AS total_amount,
    SUM(quantityOrdered) as total_order
FROM 
	`orders` 
INNER JOIN 
	orderdetails
ON
	orders.orderNumber=orderdetails.orderNumber
WHERE status = 'Shipped'
GROUP BY year
HAVING year>2003;
*/
/* $query3=Db::table('orders')
->select("YEAR(orderDate) AS year", "SUM(quantityOrdered*priceEach) AS 'Total Amount'", "SUM(quantityOrdered) as 'Total Order'")
->innerJoin('orderdetails','orders.orderNumber = orderdetails.orderNumber')
->where('status','Shipped')
->groupBy('year')
->having('year','>',2003)
->get();

print_r($query3);  */ 

//Example Usage: 4
/* 
SELECT 
	YEAR(orderDate) AS year,
    status,
    SUM(quantityOrdered*priceEach) AS total_amount,
    SUM(quantityOrdered) as total_order
FROM 
	`orders` 
INNER JOIN 
	orderdetails
ON
	orders.orderNumber=orderdetails.orderNumber
GROUP BY year,status
ORDER BY year;
*/
/* $query4 = Db::table('orders')
    ->select('YEAR(orderDate) AS year', 'status', 'SUM(quantityOrdered * priceEach) AS total')
    ->innerJoin('orderdetails', 'orders.orderNumber = orderdetails.orderNumber')
    ->groupBy('year', 'status')
    ->orderBy('year')
    ->get();
 */

//print_r($query4);

// Example Usage: 5

/* SELECT 
	a.orderNumber, 
    status, 
    SUM(priceEach*quantityOrdered) AS total 
FROM 
	orderdetails a
INNER JOIN 
	orders b ON b.orderNumber = a.orderNumber
GROUP BY
	a.orderNumber, 
    status
HAVING
	status = 'Shipped'
AND 
total>1500; */

/* $query5=Db::table('orderdetails a')
        ->select('a.orderNumber','status','SUM(priceEach * quantityOrdered) total')
        ->innerJoin('orders b', 'b.orderNumber = a.orderNumber')
        ->groupBy('orderNUmber','status')
        ->having('status','=','Shipped')
        ->having('total','<',1500)
        ->get();

print_r($query5); */

// Example Usage: 6

/* 
SELECT
	productCode, productName, textDescription
FROM
	products t1
INNER JOIN
	productlines t2 ON t1.productLine=t2.productLine;
*/

/* $query6=Db::table('products t1')
        ->select('productCode', 'productName', 'textDescription')
        ->innerJoin('productlines t2','t2.productLine = t2.productLine')
        ->get();

print_r($query6); */

// Example Usage: 7


/* SELECT
	productCode, productName, textDescription
FROM
	products
INNER JOIN
	productlines ON products.productLine=productlines.productLine */;

/* $query7=Db::table('products')
    ->select('productCode', 'productName', 'textDescription')
    ->innerJoin('productlines','products.productLine = productlines.productLine')
    ->get();

print_r($query7);
 */

//  Example Usage: 8
/* 
SELECT 
	orders.orderNumber, 
    orders.orderDate, 
    orderdetails.orderLineNumber,
    products.productName,
    orderdetails.quantityOrdered,
    orderdetails.priceEach,
    SUM(orderdetails.quantityOrdered*orderdetails.priceEach) AS total
FROM 
	orders
INNER JOIN orderdetails
ON orders.orderNumber =  orderdetails.orderNumber
INNER JOIN products
ON orderdetails.productCode = products.productCode
GROUP BY
	orders.orderNumber,
    orders.orderDate,
    orderdetails.orderLineNumber,
	products.productName;
*/

/* $query8=Db::table('orders')
    ->select('customers.customerNumber','customers.customerName','orders.orderNumber','orders.orderDate','orderdetails.orderLineNumber','products.productName','orderdetails.quantityOrdered','orderdetails.priceEach','SUM(orderdetails.quantityOrdered*orderdetails.priceEach) AS total')
    ->innerJoin('orderdetails','orders.orderNumber=orderdetails.orderNumber')
    ->innerJoin('products','orderdetails.productCode = products.productCode')
    ->innerJoin('customers','orders.customerNumber=customers.customerNumber')
    ->groupBy('orders.orderNumber','orders.orderDate','orderdetails.orderLineNumber','products.productName')
    ->orderBy('orders.orderNumber')
    ->get(); */

    //Example Usage of leftJoin
    /* 
SELECT 
	customers.customerNumber,
    customerName,
    orderNumber,
    status 
FROM
	customers
LEFT JOIN
	orders
ON customers.customerNumber=orders.customerNumber
WHERE orderNumber is NULL
ORDER BY `orders`.`orderNumber` DESC;
    */

/*     $query9=Db::table('customers')
        ->select('customers.customerNumber','customerName','orderNumber','status')
        ->RightJoin('orders','customers.customerNumber=orders.customerNumber')
        ->get();
print_r($query9); */

/* 
SELECT
    e.firstName, 
    e.lastName,
    customerNumber,
    customerName
FROM 
	employees e 
LEFT JOIN
	customers c
ON
	e.employeeNumber=c.salesRepEmployeeNumber  
ORDER BY `c`.`customerName` ASC;
*/

$query10=Db::table("employees")
        ->select("firstName","lastName","customerName",'checkNumber','amount')
        ->leftJoin("customers","employeeNumber=salesRepEmployeeNumber")
        ->rightJoin("payments","payments.customerNumber=customers.customerNumber")
        ->orderBy("customerName")
        ->orderBy("checkNumber")
        ->get();
print_r($query10);

}catch (PDOException $e){
    echo "Error: ".$e->getMessage();
}

উপরের ক্লাসটি ব্যবহার করতে নিম্নোক্ত টেবিল গুলো এবং তার ডেটা গুলো ব্যবহার করতে পারেন :

https://github.com/masudeden/sample-database

Masud Alam

আমি মাসুদ আলম, বাংলাদেশের ৩৬ তম Zend Certified Engineer । ২০০৯ সালে কম্পিউটার সাইন্স থেকে বেচেলর ডিগ্রী অর্জন করি। দীর্ঘ ১৫ বছর আমি Winux Soft, SSL Wireless, IBCS-PRIMAX, Max Group, Canadian International Development Agency (CIDA), Care Bangladesh, World Vision, Hellen Keller, Amarbebsha Ltd সহ বিভিন্ন দেশি বিদেশী কোম্পানিতে ডেটা সাইন্স, মেশিন লার্নিং, বিগ ডেটা, ওয়েব ডেভেলপমেন্ট এবং সফটওয়্যার ডেভেলপমেন্ট এর উপর বিভিন্ন লিডিং পজিশন এ চাকরি এবং প্রজেক্ট লিড করি। এছাড়াও বাংলাদেশের ১৮৫ জন জেন্ড সার্টিফাইড ইঞ্জিনিয়ার এর মধ্যে ১২০ এরও অধিক ছাত্র আমার হাতে জেন্ড সার্টিফাইড ইঞ্জিনিয়ার হয়েছেন। বর্তমানে w3programmers ট্রেনিং ইনস্টিটিউট এ PHP এর উপর Professional এবং Advance Zend Certified PHP -8.2 Engineering, Laravel Mastering Course with ReactJS, Python Beginning To Advance with Blockchain, Machine Learning and Data Science, Professional WordPress Plugin Development Beginning to Advance কোর্স করাই। আর অবসর সময়ে w3programmers.com এ ওয়েব টেকনোলজি নিয়ে লেখালেখি করি।