Joe1992 Joe1992 - 6 months ago 201
PHP Question

Laravel Eloquent Case Sensitive Relationships

Hi SO I'm having real issues with some Laravel Eloquent relationships which I can only guess are being caused by a case-sensitive relation and I'm hoping somebody here can help!

Here are the models that I'm having the issues with:

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');
}

}


Here's the code in my controller that is supposed to fetch some of the above models from the DB.

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

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


I'm using the "
->load('address)
" line as no matter what I tried I could not get eager loading to work with "
sapDelivery.address
"

In 99% of cases the address is loaded successfully from the DB but I have come across one case in which I am experiencing an issue that I can only think is being caused by case-sensitivity.

Using Laravel DebugBar I can see that my application is executing the following query:

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


When I dump the contents of
$delivery->sapDelivery
in this occurrence the address relation is NULL, however, when I paste the SQL statement into my DB console and execute it manually I get the expected row returned.

The only difference I can see between this one address and the thousands of others that are working is that there is a case difference between the Address fields:

In the CRD1 table the Address field for the effected/expected row is "The Pizza Factory (Topping)" but the eloquent relationship is using AND [CRD1].[Address] IN ('The Pizza Factory (topping)') to try and find it I'm aware that SQL is case-insensitive be default but I can't think of any other reason why this one row is behaving differently to the others.

Does anybody have any other ideas as to what could be causing this issue and suggest any possible solutions or confirm either way my theory of case sensitivity being the culprit.

Many thanks!

Answer

So After giving this problem little thought over the past few months I revisited the issue today and found some very useful code on laravel.io by somebody experiencing the same issue I found myself with.

I've built on MattApril's solution to provide the least hacky way I can think of to provide a way to offer case insensitive relationships in laravel.

To achieve this you need to add a new class HasManyCI at \vendor\laravel\framework\src\Illuminate\Database\Eloquent\Relations\HasManyCI.php

note the strtolower() changes that allow the keys to be case insensitive.

<?php namespace Illuminate\Database\Eloquent\Relations;

use Illuminate\Database\Eloquent\Collection;

class HasManyCI extends HasOneOrMany {

    /**
     * Get the results of the relationship.
     *
     * @return mixed
     */
    public function getResults()
    {
        return $this->query->get();
    }

    /**
     * Initialize the relation on a set of models.
     *
     * @param  array   $models
     * @param  string  $relation
     * @return array
     */
    public function initRelation(array $models, $relation)
    {
        foreach ($models as $model)
        {
            $model->setRelation($relation, $this->related->newCollection());
        }

        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 = $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;
     }

    /**
     * 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)
    {
        return $this->matchMany($models, $results, $relation);
    }

}

You then need to add a method to the Model class in \vendor\laravel\framework\src\Illuminate\Database\Eloquent\Model.php that invokes the new HasManyCI class. (don't forget use Illuminate\Database\Eloquent\Relations\HasManyCI; )

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);
}

To use this new case-insensitive relationship you can simply use:

return $this->hasManyCI('model', 'key', 'key');

Obviously I've only covered the hasMany relationship here but the same principle applies to them all.