Sort by related table's fileds using Eloquent not joining

Question

I used Eloquent method to work with tables. I did not use query and joins . Just making relations with hasMany and belongsTo. And I dont want to use joins and queries. I should sort the results based on some fields. Sorting for main table fields work without any problem. But when I want to sort by related table fields, I get error.

Request URL: http://localhost:8000/admin/pagination/fetch_data?page=1&sortby=product.product_title&sorttype=asc

Request Method: GET Status Code: 500 Internal Server Error

This is my code.

Comment

class Comment extends Model
{
    public function user()
    {
        return $this-> belongsTo('App\User', "comment_userid");
    }

    public function confirmerUser()
    {
        return $this-> belongsTo('App\User', "comment_confirmeruserid");
    }

    public function product()
    {
        return $this->belongsTo("App\Product", "comment_productid");
    }
}

Product

class Product extends Model
{
    public function comments()
    {
        return $this->hasMany('App\Comment', "comment_productid");
    }
}

User

class User extends Authenticatable
{
    public function comments()
    {
        return $this->hasMany('App\Comment', "comment_userid");
    }

    public function comments_confirmer()
    {
        return $this->hasMany('App\Comment', "comment_confirmeruserid");
    }
}

In controller

public function controller_fetch_data(Request $request)
{
    if( $request->ajax() ) {
        $sort_by = $request->get('sortby');
        $sort_type = $request->get('sorttype');
        $comments = Comment::with('user', 'confirmerUser', 'product')
            ->orderBy($sort_by, $sort_type)->paginate(5);
        $p_pagenumber = $request['page'];
        return view("adminPanel.comment_list")
            ->with('p_pagenumber', $p_pagenumber)
            ->with('comments', $comments)
            ->render();
    }
}

In view

function fetch_data(page, sort_type, sort_by)
{
    $.ajax({
        url : "/admin/pagination/fetch_data?page=" + page + "&sortby=" + sort_by + "&sorttype=" + sort_type,
        success : function(data) {
            $("#table_data").html('');
            $("#table_data").html(data);
        }
    });
}

Request URL: http://localhost:8000/admin/pagination/fetch_data?page=1&sortby=product.product_title&sorttype=asc Request Method: GET Status Code: 500 Internal Server Error — MohammadWebMaster, Oct 30 '19 at 09:49
500 is very abstract. check your logs in `storage/logs` and give us detailed info — Roman Meyer, Oct 30 '19 at 09:59
I am not sure you can avoid `join`. Looks related to: https://stackoverflow.com/questions/40837690/laravel-eloquent-sort-by-relationship — Oluwatobi Samuel Omisakin, Oct 30 '19 at 10:04
I am not sure. But I think that when I use hasMeny And belongsTo, There is not need to return to join and query. And Eloquent has to support all these type of requests. But If you think, ordering does not support by Eloquent, Please say me. @ Oluwatobi Samuel Omisakin — MohammadWebMaster, Oct 30 '19 at 12:38
Can this technology alone be able to handle all sorting situations or does it have to go back to Join, etc.? — MohammadWebMaster, Oct 30 '19 at 13:02

score 0 · Answer 1 · answered Oct 31 '19 at 08:52

I recommend using Laravel Collections for eloquent relationships.

$comments = Comment::with('user', 'confirmerUser', 'product')->get();
$comments = collect($comments)->sortBy($sort_by)->splice(5);

Use sortBy() or sortByDesc() for order and splice() for pagination

Hope this helps. Cheers!

Sort by related table's fileds using Eloquent not joining

1 Answers1