Paging with Eager Loading in Laravel

Asked

Viewed 419 times

1

I’m having a little ordering problem involving paging and Eager Loading.

What I need to do:

  • On one part of my site, I need to bring paged results from a subcategory

  • The subcategory is daughter of a category, so I have to display in a table the parent which is the category, the subcategory and the other data of it.

  • I’m using Eager Loading to bring the category

The problem is that I need to sort by the category name, and then by the subcategory name, and the data is not being sorted correctly.

Follows the code: 

// EloquentSubcategoria
public function byPage($page=1, $limit=10)
{
    $result = new \StdClass;
    $result->page = $page;
    $result->limit = $limit;
    $result->totalItems = 0;
    $result->items = array();

    $obj = $this->subcategoria->with(array('categoria' => function ($query) 
            {
                $query->orderBy('categoria.cat_nome', 'ASC');
            }))
            ->orderBy('subcategoria.sbc_nome', 'ASC');

    $articles = $obj->skip( $limit * ($page-1) )
                    ->take($limit)
                    ->get();


    $result->totalItems = $this->totalSubcategorias();
    $result->items = $articles->all();

    return $result;
}

protected function totalSubcategorias()
{
    return $this->subcategoria->count();
}


// SubcategoriaController
public function index()
{
    // Valores inteiros definidos no app
    $page = Input::get('page', 1);
    $perPage = Config::get('app.perPage');

    // retorna dados para paginacao
    $pagiData = $this->subcategoria->byPage($page, $perPage);

    // paginar
    $subcategorias = Paginator::make($pagiData->items, $pagiData->totalItems, $perPage);

    return View::make('subcategorias.index')->with('subcategorias', $subcategorias);
}
laravel laravel-4 paging classification

  • The problem has been solved. $this->subcategoria->with('categoria').... In models, the subcategory is with a relationship "belongsTo" Category. But in my case, I cannot use belongsTo, as it will return only one. To solve, I used JOIN instead of Eager Loading ;$query = $this->subcategoria->join('categoria', 'cat_id', '=', 'sbc_cat_id')....

  • But for an opposite case, the above code would work, only changing from subcategories to categories: $this->categoria->with(array('subcategoria' => function ($query) 
 {
 $query->orderBy('subcategoria.sbc_nome', 'ASC');
 }))
 ->orderBy('categoria.cat_nome', 'ASC'); For the relation and 1-n between Categories and Subcategories. I’m sorry if I couldn’t be clearer, I’m not very good at explaining things xD.

  • 1

    Put your solution as an answer and accept it. So it gets organized to help other people who have been through the same problem and you can still earn a reputation from it.

1 answer

1


In my case, I have a relationship between Categories and Subcategories

Categories hasMany Subcategories

Subcategories belongsTo Categories

To bring the results in alphabetical order (Category name followed by subcategory name) as it is in my code, it will not work:

$obj = $this->subcategoria->with(array('categoria' => function ($query) 
{
    $query->orderBy('categoria.cat_nome', 'ASC');
}))
->orderBy('subcategoria.sbc_nome', 'ASC');

Because the Subcategory Belongsto Category (this returns only one record)

But if it’s done the other way around, it’ll work

$obj = $this->categoria->with(array('subcategoria' => function ($query) 
{
    $query->orderBy('subcategoria.sbc_nome', 'ASC');
}))
->orderBy('categoria.cat_nome', 'ASC');

However, in the end I chose to use JOIN.

Because in my case... in the structure of my application, doing as it is written above would greatly increase the amount of code that I would have to add, so the final structure that I ended up using is as follows:

public function byPage($page=1, $limit=10)
{
    $result = new \StdClass;
    $result->page = $page;
    $result->limit = $limit;
    $result->totalItems = 0;
    $result->items = array();

    $query = $this->subcategoria
            ->select('cat_nome', 'sbc_id', 'sbc_nome')
            ->join('categoria', 'cat_id', '=', 'sbc_cat_id')
            ->orderBy('cat_nome', 'ASC')
            ->orderBy('sbc_nome', 'ASC');

    $articles = $query->skip( $limit * ($page-1) )
                    ->take($limit)
                    ->get();


    $result->totalItems = $this->totalSubcategorias();
    $result->items = $articles->all();

    return $result;
}

Browser other questions tagged laravel laravel-4 paging classification

You are not signed in. Login or sign up in order to post.