php - Laravel Eloquent 区分大小写的关系

Question

您好，我遇到了一些 Laravel Eloquent 关系的实际问题，我只能猜测是由区分大小写的关系引起的，我希望这里有人可以提供帮助!

以下是我遇到问题的模型:

class DeliveryManifestLines extends Eloquent
{
    protected $table = 'manifests';

    public function sapDelivery()
    {
        return $this->hasOne('Delivery', 'DocNum', 'sap_delivery');
    }

}


class Delivery extends Eloquent
{
    protected $connection = 'sap';
    protected $table = 'ODLN';
    protected $primaryKey = 'DocNum';

    public function deliveryManifest() {
      return $this->belongsTo('DeliveryManifest', 'DocNum', 'sap_delivery');
    }

    public function address()
    {
        return $this->hasOne('Address', 'Address', 'ShipToCode')->where('CardCode', $this->CardCode)->where('AdresType', 'S');
    }

    public function geolocation()
    {
        return $this->hasOne('GeoLocation', 'Address', 'ShipToCode')->where('CardCode', $this->CardCode)->where('AdresType', 'S')->where('Lat', '>', 0)->where('Lng', '>', 0);
    }
}

class Address extends Eloquent
{
    protected $connection = 'sap';
    protected $table = 'CRD1';
    protected $primaryKey = 'Address';

    public function delivery() {
      return $this->belongsTo('Delivery', 'Address', 'ShipToCode');
    }

}

这是我的 Controller 中的代码，它应该从数据库中获取上述一些模型。

$deliveries = DeliveryManifestLines::with('sapDelivery')->where('manifest_date', $date))->get();

foreach ($deliveries as $delivery) {
    $delivery->sapDelivery->load('address');
}

我正在使用“->load('address)”行，因为无论我尝试什么，我都无法获得与“sapDelivery.address”一起工作的急切加载>"

在 99% 的情况下，地址从数据库成功加载，但我遇到过一个问题，我认为这是由区分大小写引起的。

使用 Laravel DebugBar 我可以看到我的应用程序正在执行以下查询:

SELECT * FROM [CRD1] WHERE [CardCode] = 'P437' AND [AdresType] = 'S' AND [CRD1].[Address] IN ('The Pizza Factory (topping)')

当我在这种情况下转储 $delivery->sapDelivery 的内容时，地址关系为 NULL，但是，当我将 SQL 语句粘贴到我的数据库控制台并手动执行时，我得到了预期的结果行返回。

我能看到的这个地址与其他数千个正在工作的地址之间的唯一区别是地址字段之间存在大小写差异:

在 CRD1 表中，受影响/预期行的地址字段是“The Pizza Factory (Topping)”，但 Eloquent 关系是使用 AND [CRD1].[Address] IN ('The Pizza Factory (topping)')尝试找到它 我知道默认情况下 SQL 是不区分大小写的，但我想不出这一行与其他行的行为不同的任何其他原因。

是否有人对可能导致此问题的原因有任何其他想法并提出任何可能的解决方案或确认我的区分大小写理论是罪魁祸首。

非常感谢!

Answer 1

因此，在过去几个月很少考虑这个问题之后，我今天重新审视了这个问题，并在 laravel.io 上找到了一些非常有用的代码。由遇到与我相同的问题的人提出。

我在 MattApril 的解决方案的基础上提供了一种我能想到的最简单的方法来提供一种在 laravel 中提供不区分大小写的关系的方法。

要实现这一点，您需要添加一些新类，这些类利用 strtolower() 函数创建小写键，这允许关系中使用的 isset() 函数找到不同大小写但匹配的键:

ModelCI.php (app\Models\Eloquent\ModelCI.php)

<?php

namespace App\Models\Eloquent;

use App\Models\Eloquent\Relations\BelongsToCI;
use App\Models\Eloquent\Relations\HasManyCI;
use App\Models\Eloquent\Relations\HasOneCI;
use Illuminate\Database\Eloquent\Model;


abstract class ModelCI extends Model
{
    /**
     * Define a one-to-many relationship.
     *
     * @param string $related
     * @param string $foreignKey
     * @param string $localKey
     *
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function hasManyCI($related, $foreignKey = null, $localKey = null)
    {
        $foreignKey = $foreignKey ?: $this->getForeignKey();

        $instance = new $related();

        $localKey = $localKey ?: $this->getKeyName();

        return new HasManyCI($instance->newQuery(), $this, $instance->getTable().'.'.$foreignKey, $localKey);
    }

        /**
         * Define a one-to-one relationship.
         *
         * @param  string  $related
         * @param  string  $foreignKey
         * @param  string  $localKey
         * @return \Illuminate\Database\Eloquent\Relations\HasOne
         */
        public function hasOneCI($related, $foreignKey = null, $localKey = null)
        {
            $foreignKey = $foreignKey ?: $this->getForeignKey();

            $instance = new $related;

            $localKey = $localKey ?: $this->getKeyName();

            return new HasOneCI($instance->newQuery(), $this, $instance->getTable().'.'.$foreignKey, $localKey);
        }

    /**
     * Define an inverse one-to-one or many relationship.
     *
     * @param  string  $related
     * @param  string  $foreignKey
     * @param  string  $otherKey
     * @param  string  $relation
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function belongsToCI($related, $foreignKey = null, $otherKey = null, $relation = null)
    {
      // If no relation name was given, we will use this debug backtrace to extract
      // the calling method's name and use that as the relationship name as most
      // of the time this will be what we desire to use for the relationships.
      if (is_null($relation))
      {
        list(, $caller) = debug_backtrace(false, 2);

        $relation = $caller['function'];
      }

      // If no foreign key was supplied, we can use a backtrace to guess the proper
      // foreign key name by using the name of the relationship function, which
      // when combined with an "_id" should conventionally match the columns.
      if (is_null($foreignKey))
      {
        $foreignKey = snake_case($relation).'_id';
      }

      $instance = new $related;

      // Once we have the foreign key names, we'll just create a new Eloquent query
      // for the related models and returns the relationship instance which will
      // actually be responsible for retrieving and hydrating every relations.
      $query = $instance->newQuery();

      $otherKey = $otherKey ?: $instance->getKeyName();

      return new BelongsToCI($query, $this, $foreignKey, $otherKey, $relation);
    }
}

BelongsToCI.php (app\Models\Eloquent\Relations\BelongsToCI.php)

<?php namespace App\Models\Eloquent\Relations;


use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\Expression;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class BelongsToCI extends BelongsTo {
    /**
     * Match the eagerly loaded results to their parents.
     *
     * @param  array   $models
     * @param  \Illuminate\Database\Eloquent\Collection  $results
     * @param  string  $relation
     * @return array
     */
    public function match(array $models, Collection $results, $relation)
    {
        $foreign = $this->foreignKey;

        $other = $this->otherKey;

        // First we will get to build a dictionary of the child models by their primary
        // key of the relationship, then we can easily match the children back onto
        // the parents using that dictionary and the primary key of the children.
        $dictionary = array();

        foreach ($results as $result)
        {
            $dictionary[strtolower($result->getAttribute($other))] = $result;
        }

        // Once we have the dictionary constructed, we can loop through all the parents
        // and match back onto their children using these keys of the dictionary and
        // the primary key of the children to map them onto the correct instances.
        foreach ($models as $model)
        {
            if (isset($dictionary[strtolower($model->$foreign)]))
            {
                $model->setRelation($relation, $dictionary[strtolower($model->$foreign)]);
            }
        }

        return $models;
    }

}

HasManyCI.php (app\Models\Eloquent\Relations\HasManyCI.php)

<?php namespace App\Models\Eloquent\Relations;

use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Relations\HasMany;

class HasManyCI extends HasMany {
    /**
        * Build model dictionary keyed by the relation's foreign key.
        *
        * @param  \Illuminate\Database\Eloquent\Collection  $results
        * @return array
        */
     protected function buildDictionary(Collection $results)
     {
             $dictionary = array();

             $foreign = $this->getPlainForeignKey();

             // First we will create a dictionary of models keyed by the foreign key of the
             // relationship as this will allow us to quickly access all of the related
             // models without having to do nested looping which will be quite slow.
             foreach ($results as $result)
             {
                     $dictionary[strtolower($result->{$foreign})][] = $result;
             }

             return $dictionary;
     }

    /**
        * Match the eagerly loaded results to their many parents.
        *
        * @param  array   $models
        * @param  \Illuminate\Database\Eloquent\Collection  $results
        * @param  string  $relation
        * @param  string  $type
        * @return array
        */
     protected function matchOneOrMany(array $models, Collection $results, $relation, $type)
     {
             $dictionary = $this->buildDictionary($results);


             // Once we have the dictionary we can simply spin through the parent models to
             // link them up with their children using the keyed dictionary to make the
             // matching very convenient and easy work. Then we'll just return them.
             foreach ($models as $model)
             {
                     $key = strtolower( $model->getAttribute($this->localKey) );
                     if (isset($dictionary[$key]))
                     {
                             $value = $this->getRelationValue($dictionary, $key, $type);
                             $model->setRelation($relation, $value);
                     }
             }

             return $models;
     }

}

HasOneCI.php (app\Models\Eloquent\Relations\HasOneCI.php)

<?php namespace App\Models\Eloquent\Relations;

use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Relations\HasOne;

class HasOneCI extends HasOne {

    /**
     * Match the eagerly loaded results to their many parents.
     *
     * @param  array   $models
     * @param  \Illuminate\Database\Eloquent\Collection  $results
     * @param  string  $relation
     * @param  string  $type
     * @return array
     */
    protected function matchOneOrMany(array $models, Collection $results, $relation, $type)
    {
        $dictionary = $this->buildDictionary($results);

        // Once we have the dictionary we can simply spin through the parent models to
        // link them up with their children using the keyed dictionary to make the
        // matching very convenient and easy work. Then we'll just return them.
        foreach ($models as $model)
        {
            $key = strtolower($model->getAttribute($this->localKey));

            if (isset($dictionary[$key]))
            {
                $value = $this->getRelationValue($dictionary, $key, $type);

                $model->setRelation($relation, $value);
            }
        }

        return $models;
    }

    /**
     * Build model dictionary keyed by the relation's foreign key.
     *
     * @param  \Illuminate\Database\Eloquent\Collection  $results
     * @return array
     */
    protected function buildDictionary(Collection $results)
    {
        $dictionary = array();

        $foreign = strtolower($this->getPlainForeignKey());

        // First we will create a dictionary of models keyed by the foreign key of the
        // relationship as this will allow us to quickly access all of the related
        // models without having to do nested looping which will be quite slow.
        foreach ($results as $result)
        {
            $dictionary[$result->{$foreign}][] = $result;
        }

        return $dictionary;
    }

}

要使用新类，您必须这样定义关系:

$this->belongsToCI('Model');

或

$this->hasManyCI('Model');

或

$this->hasOneCI('Model');