getPdo usage

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.

jlrdw

8 years ago

Level 75

Also note this part

        $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);

Is custom, as I don't use blade, so ignore this part and just return the view normally for blade.

But this part is needed if paginating

->with('pagelinks', $pagelinks)

And in view

    <?php echo $pagelinks; ?>  /// But convert to blade as needed

Edit: Adding custom pager example.

I mentioned a custom pager, here is an example

custompager.blade.php

@if ($paginator->hasPages())
    <ul class="pagination pagination">
        {{-- Previous Page Link --}}
        @if ($paginator->onFirstPage())
            <li class="disabled"><span>«</span></li>
        @else
            <li><a href="{{ $paginator->previousPageUrl() }}" rel="prev">«</a></li>
        @endif

        @if($paginator->currentPage() > 3)
            <li class="hidden-xs"><a href="{{ $paginator->url(1) }}">1</a></li>
        @endif
        @if($paginator->currentPage() > 4)
            <li><span>...</span></li>
        @endif
        @foreach(range(1, $paginator->lastPage()) as $i)
            @if($i >= $paginator->currentPage() - 2 && $i <= $paginator->currentPage() + 2)
                @if ($i == $paginator->currentPage())
                    <li class="active"><span>{{ $i }}</span></li>
                @else
                    <li><a href="{{ $paginator->url($i) }}">{{ $i }}</a></li>
                @endif
            @endif
        @endforeach
        @if($paginator->currentPage() < $paginator->lastPage() - 3)
            <li><span>...</span></li>
        @endif
        @if($paginator->currentPage() < $paginator->lastPage() - 2)
            <li class="hidden-xs"><a href="{{ $paginator->url($paginator->lastPage()) }}">{{ $paginator->lastPage() }}</a></li>
        @endif

        {{-- Next Page Link --}}
        @if ($paginator->hasMorePages())
            <li><a href="{{ $paginator->nextPageUrl() }}" rel="next">»</a></li>
        @else
            <li class="disabled"><span>»</span></li>
        @endif
    </ul>
@endif

In this pager I am just decreasing the number of links, it just works better for mobile with fewer links. But you can write your own custom pager as required for your app.

For more on a custom pager, please refer to the docs, as Taylor covers it there.

1 like

jlrdw

7 years ago

Level 75

I am adding another example of a report I had to do a while back, just used jquery date picker for dates:

A join and group by:

Looks like this when looped over:

Code:

public function monthlyReport()
    {
        $bdate = Request::input('begindate');
        $edate = Request::input('enddate');

        $sql = "select distinct `account_types`.`AccountType` AS `AccountType`,`accounts`.`AccountNumber` AS `AccountNumber`,`accounts`.`AccountName` AS `AccountName`,sum(`transactions`.`Expense`) AS `Sum_Expense`,sum(`transactions`.`Income`) AS `Sum_Income` from ((`account_types` join `accounts` on((`account_types`.`AccountTypeID` = `accounts`.`AccountTypeID`))) join `transactions` on((`accounts`.`AccountID` = `transactions`.`AccountID`))) where (`transactions`.`TransactionDate` Between '$bdate' and  '$edate') group by `account_types`.`AccountType`,`accounts`.`AccountNumber`,`accounts`.`AccountName`";
        
        $sth = DB::getPdo()->prepare($sql);
        $sth->execute();
        $quy = $sth->fetchAll(\PDO::FETCH_OBJ);
        $title = 'Monthly Report';
        return view('account.report', compact('quy', 'title'));
    }

If image not showing here:

https://i.imgur.com/RlzeSEp.jpg

Some logic is required during the loop. And this was a rough draft, not final version.

1 like

Please or to participate in this conversation.