belongsToMany relationship orderBy relationship existence, followed by abstinence using pagination

Hello!

Laravel version 9.x PHP version 8.1

Current code:

/**
     * Show the form for editing the specified resource.
     *
     * @param \App\Models\Site $site
     *
     * @return \Illuminate\Http\Response
     */
    public function edit(Request $request, Site $site)
    {
        $result = Server::select('id', 'name')
        ->orderBy('name', 'asc')
        ->simplePaginate(config('app.limit'));

        if ($request->ajax()) {
            return response()->view('site.ajax.edit', compact('site', 'result'));
        }

        return response()->view('site.edit', compact('site', 'result'));
    }

\ app/models/Server.php
public function sites()
{
    return $this->belongsToMany(Site::class, 'server_site', 'server_id', 'site_id')->withTimestamps();
}

\ app/models/Site.php
public function servers()
{
    return $this->belongsToMany(Server::class, 'server_site', 'site_id', 'server_id')->withTimestamps();
}

New business rule: List all servers, paginated, showing first all servers related to the informed site, followed by the other servers that have no relationship with the informed site or any other site. In all cases (abstinence and existence), the result must be ordered by server name.

This the part of the code that I need to be updated.

    $result = Server::select('id', 'name')
    ->orderBy('name', 'asc')
    ->simplePaginate(config('app.limit'));

Note: Bringing in all the results to do the sort in PHP would not be an option. The idea is to bring the result ready with one query only. I tried using join() and with() but I didn't get it. I think because of my own limitation. If anyone has a suggestion on how to do this, I would appreciate the help.

Please, I'm not asking anyone here to make the query for me, but I need some guide coz I'm stucked :(

Thx for you help :)

rodrigo.pedra

4 years ago

Level 56

One suggestion:

$result = Server::query()
    ->select('id', 'name')
    ->orderByRaw('CASE 
        WHEN EXISTS(SELECT 1 FROM server_site WHERE server_id = servers.id) THEN 1
        ELSE 0
    END DESC')
    ->orderBy('name')
    ->simplePaginate(config('app.limit'));

reference: https://laravel.com/docs/9.x/queries#orderbyraw

P.S.: I used CASE SQL clause instead of a IF() SQL function as you did not mention which database you were using, and the IF() SQL function is not available on every database, for example SQLite does not implement it.

2 likes

rodrigo.pedra

4 years ago

Best Answer

Level 56

Alternative, might perform a little better:

$result = Server::query()
    ->orderByDesc(
        Site::query()
            ->selectRaw(1)
            ->leftJoin('server_site', 'sites.id', 'server_site.site_id')
            ->whereColumn('server_site.server_id', 'servers.id')
            ->union(fn ($query) => $query->selectRaw(0))
            ->limit(1)
            ->toBase()
    )
    ->orderBy('name')
    ->simplePaginate(config('app.limit'));

reference: https://laravel.com/docs/9.x/eloquent#subquery-ordering

The query after the union might need a ->fromRaw('dual') (or another default table name) in some databases. It should works as is on MySQL and SQLite.

2 likes

fcno

4 years ago

Level 6

Hey @rodrigo.pedra

Thank you very much for your help.

I don't know how I didn't see this in the documentation, but I think it would take me a few months to get anywhere, while u came with a solution within 5 minutes hahaha.

I think the only thing missing is one detail that you missed.

The servers that must come first are the ones that are related to the Site $site received in the action.

The idea is to display on the site editing page, at the top rows of the table, the servers that are related to the site being edited.

So, the final version is:

$result = Server::query()
    ->orderByDesc(
        Site::query()
            ->selectRaw(1)
            ->leftJoin('server_site', 'sites.id', 'server_site.site_id')
            ->whereColumn('server_site.server_id', 'servers.id')
			->where('sites.id', $site->id)  // <<<< added this line
            ->union(fn ($query) => $query->selectRaw(0))
            ->limit(1)
            ->toBase()
    )
    ->orderBy('name')
    ->simplePaginate(config('app.limit'));

Amigo, você é foda pra cacete. Muitíssimo obrigado (translation for non brasilians: "You are a magician")

Btw.. Im using MySql 8

1 like

Please or to participate in this conversation.