jlrdw
767
2
Guides

getPdo usage

Posted 1 year ago by jlrdw

I have posted a while back a guide on lengthaware paginator: https://laracasts.com/discuss/channels/guides/length-aware-paginator

Sometimes for a more complex query I like using regular sql by using laravel's getPdo:

Basic usage is :

  • You will probably need a custom lengthaware paginator
  • you will need a count and calculate the current page

At top of controller include the DB Facade:

use Illuminate\Support\Facades\DB;

An example method using getPdo()

        public function indexGet()
    {
        $page = Request::input('page', '1');
        $t1 = "b"; // thrown in to test querystring
        $perpage = "5";
        $offset = ($page - 1) * $perpage;
        $krows = DB::select('select COUNT(dogid) as count from dc_dogs');
        $numrows = $krows[0]->count;
        $t1 = "b"; // thrown in to test querystring
        $pagingQuery = "LIMIT {$offset}, {$perpage}";
        $sql = "SELECT * FROM dc_dogs " . $pagingQuery;
        $sth =  DB::getPdo()->prepare($sql);
        $sth->execute();
        $dogs = $sth->fetchAll(\PDO::FETCH_OBJ);
        $pageclass = 'custompager';
        $pagelinks = LengthPager::makeLengthAware($dogs, $numrows, $perpage, ['t1' => $t1], null);
        $title = 'Dogs';
        $view = 'dog/indexg';
        $layout = ViewLayout::getLayout('dog/indexget');
        $content = View::make($view)
                ->with('dogs', $dogs)
                ->with('pagelinks', $pagelinks);
        return view($layout)->with('content', $content)->with('title', $title);        
               
    }

If you have custom pagination code use like:

$pagelinks = LengthPager::makeLengthAware($dogs, $numrows, $perpage, ['t1' => $t1], $pageclass);

Also if using custom pagination code, change the custom class as follows:

public static function makeLengthAware($collection, $total, $perPage, $appends = null)

// to

public static function makeLengthAware($collection, $total, $perPage, $appends = null, $pageclass = null)
    

and this:

return str_replace('/?', '?', $paginator->render());

// to

return str_replace('/?', '?', $paginator->render($pageclass));

You can also use getPdo with bindings, like:

    public function getOne($dogid)
    {
        $sql = "SELECT * FROM " . PREFIX . "dogs WHERE dogid = :dogid";
        $sth = DB::getPdo()->prepare($sql);
        $params = [':dogid' => $dogid];
        $sth->execute($params);
        return $sth->fetch(\PDO::FETCH_OBJ);
    }

Another example, Controller would call this from a custom model:

public function getChecks($offset = "", $rowsperpage = "", $checksearch = "")
    {
        $checksearch = $checksearch . "%";
        $pagingQuery = " LIMIT $offset, $rowsperpage";
        $sql = "SELECT OD.checkid, OD.transdate, OD.transdescribe, OD.widthdraw, OD.deposit, OD.isclr,";
        $sql = $sql . " (SELECT (Sum(IFNULL(deposit, 0)) - Sum(IFNULL(widthdraw, 0))) FROM checks";
        $sql = $sql . " WHERE checkid<=OD.checkid) AS RunningSum";
        $sql = $sql . " FROM checks AS OD" . $pagingQuery;
        $sth = DB::getPdo()->prepare($sql);
        $sth->execute();
        $results = $sth->fetchAll(\PDO::FETCH_ASSOC);
        return $results;
    }

Just a quick example, bindings could have been used also.

Note PDO needs the \

Each persons choice weather to use eloquent, QB, or DB. I use eloquent and QB sometimes, but I just like some queries with joins written in regular sql.

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.