Question 8206 in PHP Versions Question posted in
You can find a list of all versions here.

How to get records in Laravel 9.x belongsToMany relationship orderBy relationship existence, followed by abstinence using pagination – PHP Versions

Laravel version 9.x
PHP version 8.1

Current code.

    /**
     * Show the form for editing the specified resource.
     *
     * @param AppModelsSite $site
     *
     * @return IlluminateHttpResponse
     */
    public function edit(Request $request, Site $site)
    {
        $site->load(['servers' => function($query) {
            $query->select('id')->orderBy('id', 'asc');
        }]);

        $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, initially bringing all servers linked 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.
Assume you have access to the $site variable to perform the query.

This the part of the code that 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 the query.
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.

The web page:
This is the site editing (location) page. On this screen, all servers available for relationship are listed, in a paginated manner.
What I need is to display, at the beginning of the table listing all servers, the servers that are related to the site being edited, followed by the other servers.

Exemple:
Editing Vancouver Site

All Servers
| Checkbox  | Server Name    |
| --------  | -------------- |
| Checked   | server bbb     |
| Checked   | server ddd     |
| Checked   | server fff     |
| unchecked | server aaa     |
| unchecked | server ccc     |
| unchecked | server ggg     |

pages 1, 2, 3, 4, etc.
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    $result = Server::select('id', 'name')
            ->orderByDesc(
            Site::selectRaw(1)
                ->leftJoin('server_site', 'sites.id', 'server_site.site_id')
                ->whereColumn('server_site.server_id', 'servers.id')
                ->where('server_site.site_id', $site->id)
                ->union(fn ($query) => $query->selectRaw(0))
                ->limit(1)
                ->toBase()
        )
        ->orderBy('name', 'asc')
        ->simplePaginate($per_page);

    I asked the same question on the Laracast forum and got the answer there. User Rodrigo Pedra who replied and it's working.

    I post it here to multiply knowledge.

    Anyway, here's the link to the conversation.

    https://laracasts.com/discuss/channels/laravel/belongstomany-relationship-orderby-relationship-existence-followed-by-abstinence-using-pagination?page=1&replyId=778641


  2. 0

    According to me you have to execute the following code:

    $maxServersPerPages = 20;
$page = Request::input('page') ?? 1;
$relatedServers = $site->load('servers')->servers()->orderBy('name')->get();
$otherServers = Server::whereNotIn('id', $relatedServers->pluck('id'))->orderBy('name')->get();
$allServers = $relatedServers->merge($otherServers);
$currentPageServers = $allServers->forPage($page, $maxServersPerPage)

    we get all the servers related to your site by eager loading them; then we get all the servers that are not related to your site, this means we have to exclude all those we got in the query before; we merge the servers collections we obtained by getting one collection, finally we get the servers related to the specified page paginated depending on your $maxServerPerPage value.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search