Eloquent "select" method not working with using "with" method

Question

My Village model;

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;

class Village extends Model {
    public function positions() {
        return $this->belongsTo(Map::class, 'id', 'field_id');
    }
}

My Map class migration;

Schema::create('map_data', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('field_type');
    $table->integer('field_id');
    $table->string('x');
    $table->string('y');
    $table->timestamps();
});

My "villages" methon on "VillageController" class;

public function villages() {
    $villages = Village::with([
        'positions' => function ($query) {
            $query->select('x', 'y');
        }
    ])->get();

    return $villages;
}

Result;

{
  "villages": [
    {
      "id": 1,
      "name": "village 1",
      "created_at": "2016-10-26 18:36:34",
      "updated_at": "2016-10-26 18:36:34",
      "positions": null
    },
    {
      "id": 2,
      "name": "village 2",
      "created_at": "2016-10-26 18:36:34",
      "updated_at": "2016-10-26 18:36:34",
      "positions": null
    }
  ]
}

"Select" method only needs to be mentioned, but not the column returns NULL.

If I delete the $query->select('x', 'y'); code returns the following results.

{
  "villages": [
    {
      "id": 1,
      "name": "village 1",
      "created_at": "2016-10-26 18:36:34",
      "updated_at": "2016-10-26 18:36:34",
      "positions": {
        "id": 1,
        "field_type": "1",
        "field_id": "1",
        "x": "21",
        "y": "21",
        "created_at": "2016-10-26 18:36:34",
        "updated_at": "2016-10-26 18:36:34"
      }
    },
    {
      "id": 2,
      "name": "village 2",
      "created_at": "2016-10-26 18:36:34",
      "updated_at": "2016-10-26 18:36:34",
      "positions": {
        "id": 2,
        "field_type": "1",
        "field_id": "2",
        "x": "0",
        "y": "0",
        "created_at": "2016-10-26 18:36:34",
        "updated_at": "2016-10-26 18:36:34"
      }
    }
  ]
}

But I use the $query->select('x'); code and the result should be the following

resource: https://stackoverflow.com/a/32185643/3287225

Answer 1

When you try to eagerly load positions relation for villages with

Village::with(['positions'])->get();

3 things happen:

1. Eloquent loads all villages
2. Eloquent loads all positions
3. Eloquent assigns positions to corresponding Village objects using the field_id column

In order for it to work, fetched positions need to have field_id column fetched, otherwise Eloquent is unable to match them with their corresponding Villages.

When you do

$query->select('x', 'y');

you're fetching only x and y columns from your positions table. field_id column is not fetched, that's why Eloquent is unable to fetch them with Village objects and that's why you're getting null in instead of a collection of positions.

Replace

$query->select('x', 'y');

with

$query->select('field_id', 'x', 'y');

to make your code work as expected.