laravel 5.2 eloquent order by on relationship result count

Question

I have two tables website_link and website_links_type. website_link is related website_links_type with hasmany relationship.

$this->website_links->where('id',1)->Paginate(10);

and relationship

public function broken()
{
    return $this->hasMany('App\Website_links_type')->where('status_code','!=',"200");
}

Now I want to get result from website_link table but Orderby that result on count of broken relationship result.

score 1 · Answer 1 · edited May 23 '17 at 12:15

There are many ways to solve this problem. In my answer I'll use two I know.

You can eagerload your relationship and use the function sortBy(). However I don't think you can use the paginate() functionality with this solution.

Example:

$results = Website_link::with('website_links_type')->get()->sortBy(function ($website_link) {

    return $website_link->website_links_type->count();

});

See this answer

You can also use raw queries to solve this problem. With this solution you can still use the pagination functionality (I think).

Example:

$results = Website_link

    ::select([
        '*',
        '(
            SELECT COUNT(*) 
            FROM website_links_type 
            WHERE 
                website_links_type.website_link_id = website_link.id 
            AND 
                status_code <> 200 
        ) as broken_count'
    ])

    ->orderBy('broken_count')

    ->paginate(10);

You may have to change the column names to match your database.

Ketav · Answer 2 · 2016-05-31T06:10:31.640

0

You can not put WHERE condition in model file.

You just give relationship hasMany in model file.

And use where condition in controller side.

Refer this document.

Try this

Model file:

public function broken()
{
    return $this->hasMany('App\Website_links_type');
}

Controller file:

$model_name->website_links->where('id',1)
            ->where('status_code','!=',"200")
            ->orderBy('name', 'desc')
            ->Paginate(10);

edited May 31 '16 at 06:10

answered May 31 '16 at 05:51

Ketav

760
1
8
27

For orderBy you can use orderBy() in controller. And please more specify about **relationship broken count** – Ketav May 31 '16 at 06:11
I don't know what @KetavChotaliya Is talking about, but you can put a `where` clause in your model just fine. If no method chain is provided, the query builder is closed and an Eloquent collection will be returned. If further method chains are provided, then the Builder relationship will append further arguments at the end. – Ohgodwhy May 31 '16 at 06:44
yes @Ohgodwhy you are right. is it tough to use orderby on relationship field? should i use my that code through query builder? – Umair Zahid May 31 '16 at 07:22
This answer is not a solution for his question. He is asking how to order by COUNT(). – Thomas Van der Veen May 31 '16 at 07:27
@ThomasVanderVeen waiting for Answer. :-( – Umair Zahid May 31 '16 at 07:53

laravel 5.2 eloquent order by on relationship result count

2 Answers2