問(wèn)題描述

我正在嘗試使用雄辯的模型將用戶(hù)表內(nèi)部連接到自身.我到處尋找，但似乎無(wú)法找到解決此不創(chuàng)建兩個(gè)查詢(xún)的解決方案，這正是我目前正在做的事情.

I'm trying to inner join a users table to itself using an eloquent model. I've looked everywhere but can't seem to find a solution to this without creating two queries which is what I am currently doing.

users 表通過(guò) pivot 表 friends

我嘗試將 Users::class 內(nèi)部連接到自身，但失敗了.在內(nèi)部連接中我能得到的最好結(jié)果是運(yùn)行兩個(gè)查詢(xún)并查看是否有重疊.因此，一個(gè)人聯(lián)系了另一個(gè)人，反之亦然.

I tried and failed inner joining Users::class to itself. The best I can get at an inner join is by running two queries and seeing if there is an overlap. Thus one person has reached out to the other and vice versa.

friends   | users
----------|------
send_id   | id
receive_id| name
is_blocked|

樣本數(shù)據(jù) &預(yù)期結(jié)果

users.id | name
---------|------
1        | foo
2        | bar
3        | baz

friends
send_id | receive_id | is_blocked
--------|------------|-----------
1       |    2       |  0
2       |    1       |  0
1       |    3       |  0
3       |    1       |  1
2       |    3       |  0

用戶(hù)應(yīng)該有一種稱(chēng)為朋友的雄辯關(guān)系.它應(yīng)該是您期望的來(lái)自 requestedFriends 或 receivedFriends 剛剛加入的結(jié)果.

The user should have an eloquent relationship called friends. It should be what you expect comes out of requestedFriends or receivedFriends just joined.

foo->friends
returns `baz`
bar->friends
returns `foo`
baz->friends
returns empty collection

正在使用

// User.php
public function requestedFriends()
{
    $left = $this->belongsToMany(User::class, 'friends','send_id','receive_id')
        ->withPivot('is_blocked')
        ->wherePivot('is_blocked','=', 0)
        ->withTimestamps();
    return $left;
}

public function receivedFriends()
{
    $right = $this->belongsToMany(User::class, 'friends','receive_id','send_id')
        ->withPivot('is_blocked')
        ->wherePivot('is_blocked','=', 0)
        ->withTimestamps();

    return $right;
}

public function friends()
{
    $reqFriends = $this->requestedFriends()->get();
    $recFriends = $this->receivedFriends()->get();
    $req = explode(",",$recFriends->implode('id', ', '));
    $intersect = $reqFriends->whereIn('id', $req);
    return $intersect;
}

目前的研究

Laravel 多對(duì)多自引用表只能以一種方式工作 ->老問(wèn)題，但仍然相關(guān)

Research so far

Laravel Many to many self referencing table only works one way -> old question, but still relevant

https://github.com/laravel/framework/issues/441#issuecomment-14213883 ->是的，它有效……但只有一種方式.

https://github.com/laravel/framework/issues/441#issuecomment-14213883 -> yep, it works… but one way.

https://laravel.com/docs/5.8/collections#method-wherein目前我找到的唯一方法是雄辯地做到這一點(diǎn).

https://laravel.com/docs/5.8/collections#method-wherein currently the only way I have found to do this in eloquent.

https://laravel.com/docs/5.7/queries#joins->理想情況下我會(huì)找到一個(gè)在自身上使用內(nèi)連接的解決方案，但無(wú)論我用哪種方式放置 ID，我都無(wú)法找到解決方案.

https://laravel.com/docs/5.7/queries#joins -> Ideally I would find a solution using an innerjoin onto itself, but no matter which way I put the id's I couldn't get a solution to work.

一個(gè)解決方案是在 laravel 5.7 或 5.8 中使用 eloquent 來(lái)內(nèi)部連接自引用表，其中關(guān)系僅在 send_id & 時(shí)存在.receive_id 出現(xiàn)在朋友表的多行中.

A solution would inner join a self referencing table using eloquent in laravel 5.7 or 5.8, where a relationship only exists if send_id & receive_id are present on multiple rows in the friends table.

或

以某種方式讓社區(qū)知道這是不可能的.

Somehow let the community know that this can't be done.

提前致謝！

推薦答案

我還沒(méi)有詳細(xì)檢查這個(gè)解決方案，但我已經(jīng)寫(xiě)了一個(gè)ManyToMany"類(lèi)擴(kuò)展了 laravel 附帶的BelongsToMany"類(lèi)，它似乎工作.該類(lèi)基本上只是覆蓋了get"方法，復(fù)制了原始查詢(xún)，反轉(zhuǎn)"了它，并對(duì)原始查詢(xún)執(zhí)行了聯(lián)合".

I have not checked this solution in every detail yet, but I have written a "ManyToMany" Class extending the "BelongsToMany" Class shipped with laravel, which appears to work. The class basically just overrides the "get" method, duplicating the original query, "inverting" it and just performing a "union" on the original query.

<?php

namespace AppDatabaseEloquentRelations;

use IlluminateDatabaseEloquentRelationsBelongsToMany;

class ManyToMany extends BelongsToMany
{

    /**
     * Execute the query as a "select" statement.
     *
     * @param  array  $columns
     * @return IlluminateDatabaseEloquentCollection
     */
    public function get($columns = ['*'])
    {
        // duplicated from "BelongsToMany"
        $builder = $this->query->applyScopes();

        $columns = $builder->getQuery()->columns ? [] : $columns;

        // Adjustments for "Many to Many on self": do not get the resulting models here directly, but rather
        // just set the columns to select and do some adjustments to also select the "inverse" records
        $builder->addSelect(
            $this->shouldSelect($columns)
        );

        // backup order directives
        $orders = $builder->getQuery()->orders;
        $builder->getQuery()->orders = [];

        // clone the original query
        $query2 = clone($this->query);

        // determine the columns to select - same as in original query, but with inverted pivot key names
        $query2->select(
            $this->shouldSelectInverse( $columns )
        );
        // remove the inner join and build a new one, this time using the "foreign" pivot key
        $query2->getQuery()->joins = array();

        $baseTable = $this->related->getTable();
        $key = $baseTable.'.'.$this->relatedKey;
        $query2->join($this->table, $key, '=', $this->getQualifiedForeignPivotKeyName());

        // go through all where conditions and "invert" the one relevant for the inner join
        foreach( $query2->getQuery()->wheres as &$where ) {
            if(
                $where['type'] == 'Basic'
                && $where['column'] == $this->getQualifiedForeignPivotKeyName()
                && $where['operator'] == '='
                && $where['value'] == $this->parent->{$this->parentKey}
            ) {
                $where['column'] = $this->getQualifiedRelatedPivotKeyName();
                break;
            }
        }

        // add the duplicated and modified and adjusted query to the original query with union
        $builder->getQuery()->union($query2);

        // reapply orderings so that they are used for the "union" rather than just the individual queries
        foreach($orders as $ord)
            $builder->getQuery()->orderBy($ord['column'], $ord['direction']);

        // back to "normal" - get the models
        $models = $builder->getModels();
        $this->hydratePivotRelation($models);

        // If we actually found models we will also eager load any relationships that
        // have been specified as needing to be eager loaded. This will solve the
        // n + 1 query problem for the developer and also increase performance.
        if (count($models) > 0) {
            $models = $builder->eagerLoadRelations($models);
        }

        return $this->related->newCollection($models);
    }


    /**
     * Get the select columns for the relation query.
     *
     * @param  array  $columns
     * @return array
     */
    protected function shouldSelectInverse(array $columns = ['*'])
    {
        if ($columns == ['*']) {
            $columns = [$this->related->getTable().'.*'];
        }

        return array_merge($columns, $this->aliasedPivotColumnsInverse());
    }

    /**
     * Get the pivot columns for the relation.
     *
     * "pivot_" is prefixed ot each column for easy removal later.
     *
     * @return array
     */
    protected function aliasedPivotColumnsInverse()
    {
        $collection = collect( $this->pivotColumns )->map(function ($column) {
            return $this->table.'.'.$column.' as pivot_'.$column;
        });
        $collection->prepend(
            $this->table.'.'.$this->relatedPivotKey.' as pivot_'.$this->foreignPivotKey
        );
        $collection->prepend(
            $this->table.'.'.$this->foreignPivotKey.' as pivot_'.$this->relatedPivotKey
        );

        return $collection->unique()->all();
    }

}

這篇關(guān)于自引用表上的 Laravel Eloquent Inner Join的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！