Eloquent hasOne Relationship Returns NULL even when related records exist

Question

I have a small practice accounting app that I'm building using Laravel to provide a REST api. I am trying to use eloquent relationships to handle delivering objects including their relations.

Right now I have a "Transaction" model which looks like the following.

class Transaction extends Model
{
  protected $table = "transaction";

  protected $fillable = [
    "transaction_type_id",
    "account_id",
    "description",
    "amount",
    "comments",
    "transaction_status_id",
    "posting_date"
  ];

  protected $guarded = [
    "id"
  ];

  public static function create(array $attributes = Array()) {

    $account = BankAccount::find($attributes["account_id"]);

    if ($attributes["transaction_type_id"] == 1) {
        //Credit
        $account["balance"] += $attributes["amount"];
    } else if ($attributes["transaction_type_id"] == 2) {
        //Debit
        $account["balance"] -= $attributes["amount"];
    }

    $account->save();
    parent::create($attributes);    
  }

  public function transactionType() {
    return $this->hasOne("App\Model\TransactionType", "id");
  }

  public function transactionStatus() {
    return $this->hasOne("App\Model\TransactionStatus", "id");
  }
}

The TransactionStatus model looks like this

class TransactionStatus extends Model
{
  protected $table = "transaction_status";

  protected $guarded = [
      "id", "description"
  ];
}

And the TransactionType model looks like this

class TransactionType extends Model
{
  protected $table = "transaction_type";

  protected $guarded = [
    "id", "description"
  ];
}

I have the following in my controller

return Transaction::with("TransactionType", "TransactionStatus")
    ->where("account_id", $accountId)
    ->get()
    ->toJson();

This query returns a result that looks like the following, where the first result returns the relation, but every subsequent record has null.

  {
   "id": 1,
   "transaction_type_id": 1,
   "account_id": 1,
   "description": "Morbi metus. Vivamus euismod urna.",
   "amount": "4179.00",
   "comments": "ullamcorper, nisl arcu iaculis enim,",
   "transaction_status_id": 1,
   "posting_date": "2016-12-10 21:24:25",
   "created_at": "2016-12-10 00:00:00",
   "updated_at": "2016-12-10 00:00:00",
   "transaction_type": {
     "id": 1,
     "description": "Credit",
     "created_at": "2016-12-09 13:37:00",
     "updated_at": "2016-12-09 13:37:00"
  },
  "transaction_status": {
    "id": 1,
    "description": "Pending",
    "created_at": "2016-12-09 13:37:00",
    "updated_at": "2016-12-09 13:37:00"
  }
 },
 {
  "id": 3,
  "transaction_type_id": 1,
  "account_id": 1,
  "description": "lorem ut aliquam iaculis, lacus",
  "amount": "2710.00",
  "comments": "ac, eleifend vitae, erat. Vivamus",
  "transaction_status_id": 1,
  "posting_date": "2016-07-16 04:23:34",
  "created_at": "2016-12-10 00:00:00",
  "updated_at": "2016-12-10 00:00:00",
  "transaction_type": null,
  "transaction_status": null
 }

The above result makes no sense to me because all of the records in the transaction table have both a transaction_type_id and a transaction_status_id, so these shouldn't be null at all.

I've looked high and low trying to understand why this might happen, but I've not been able to find an explanation. I've included a screenshot below that indicates how the records look in the table in MySQL as well.

Answer 1

public function dispute() {
    return $this->hasOne('App\PaypalDispute', 'transaction_id', 'transaction_id');
}

I managed to get it like this. We should use the local_key as the column name in order to work.

Answer 2

I found the solution to the problem and it was along the same lines of what IzzEps pointed out.

The way that I had the relationships originally set up is like this.

public function transactionType() {
  return $this->hasOne("App\Model\TransactionType", "id");
}

public function transactionStatus() {
  return $this->hasOne("App\Model\TransactionStatus", "id");
}

I took another look at the eloquent relationship docs and found this:

Additionally, Eloquent assumes that the foreign key should have a value matching the id (or the custom $primaryKey) column of the parent. In other words, Eloquent will look for the value of the user's id column in the user_id column of the Phone record. If you would like the relationship to use a value other than id, you may pass a third argument to the hasOne method specifying your custom key: return $this->hasOne('App\Phone', 'foreign_key', 'local_key');

When I changed the relationships to this:

public function transactionType() {
    return $this->hasOne("App\Model\TransactionType", "id", "transaction_type_id");
}

public function transactionStatus() {
    return $this->hasOne("App\Model\TransactionStatus", "id", "transaction_status_id");
}

I got the results I was expecting. In this case "id" is the foreign key which exists on the transaction_status or transaction_type table, and "transaction_type_id" or "transaction_status_id" is the key that is "local" to the "transaction" table or the "local_key" in the parlance of the docs.

I was not fully specifying how the relationships are defined and what keys the relationships should be using. Thanks for pointing me in the right direction which, as should be second-nature, was back to the documentation.

So, as always, I should more closely RTM.

Answer 3

Didn't see it the first time. Actually you are storing the transaction_type_id and transaction_status_id on your `Transaction model. Which essentially means that the relation should be defined as

  Transaction belongsTo TransactionType  &  (Inverse) TransactionType hasMany Transaction

and

  Transaction belongsTo TransactionStatus  & (Inverse) TransactionStatus hasMany Transaction  

The signatures of hasOne, belongsTo and hasMany are very different.

So the relationships should be defined (as per your current code)

class Transaction extends Model
{
    protected $table = "transaction";

      protected $fillable = [
        "transaction_type_id",
        "account_id",
        "description",
        "amount",
        "comments",
        "transaction_status_id",
        "posting_date"
      ];

    public function transactionType()
    {
        return $this->belongsTo('App\Model\TransactionType');
    }

    public function transactionStatus()
    {
        return $this->belongsTo('App\Model\TransactionStatus');
    }
}  

class TransactionType extends Model
{

    protected $table = "transaction_types";

    protected $fillable = [ /* mass assignable fields */];

    public function transactions()
    {
        return $this->hasMany('App\Model\Transaction');
    }
}


class TransactionStatus
{

    protected $table = "transaction_statuses";

    protected $fillable = [ /* mass assignable fields */];

    public function transactions()
    {
        return $this->hasMany('App\Model\Transaction');
    }
}  

As per Laravel documentation Eloquent determines the foreign key of the relationship based on the model name.

Since you are following desired convention of naming the foreign key corresponding to the Model names

transaction_type_id <=> TransactionType and transaction_status_id <=> TransactionStatus  

you can omit specifying the foreign key as second argument to the relationship function.

After defining the relationships as above

return Transaction::with('transactionType', 'transactionStatus')
->where("account_id", $accountId)
->get()
->toJson();  

should return the desired output for you. Haven't tested but should definitely work.

Hope this helps. Let me know if otherwise.

Answer 4

When defining the following relationships

public function transactionType() {
    return $this->hasOne("App\Model\TransactionType", "id");
}

public function transactionStatus() {
   return $this->hasOne("App\Model\TransactionStatus", "id");
}

you are specifying the id of the Transaction model table as the foreign key for both of these relationships (as opposed to the correct correct foreign key's of transaction_type_id and transaction_status_id respectively). The result is that when calling Transaction::with("TransactionType", "TransactionStatus") you are joining both the transaction_type and transaction_status table on the wrong foreign key (transaction.id).

To Fix:

Simply update your relationships to use the correct foreign keys as follows:

public function transactionType() {
    return $this->hasOne("App\Model\TransactionType", "transaction_type_id");
}

public function transactionStatus() {
   return $this->hasOne("App\Model\TransactionStatus", "transaction_status_id");
}

Hope this helps...