Eager loading works, but returns no data (Lumen)

Are you sure your column names are correct?

return $this->hasMany('App\Instruction', 'foreign_key', 'local_key');

return $this->belongsTo('App\Recipe', 'foreign_key', 'other_key');

Yes, the naming and constraints are set up consistently. I have also tried various variants of the foreign/local key values but it's still not working.

I have a Many-to-Many relationship set up using custom columns, but one to many appears to be an issue.

Is this an existing database or did you create it as a laravel migration? Can you post the full model and the 'Actual' database schemas. Not the Laravel schema, but what the database has.

Did you specified the primary key of recipe is 'RecipeId' by setting the $primaryKey attribute on the model ?

Sorry guys, work has been busy. Heres the information you want.

$primaryKey value has been set in ALL models (due to Laravel/Lumen expecting 'id' to be the primary key name)

Tables were not created with migrations, heres the Schema for relevant tables.

CREATE TABLE `recipe` (
  `recipeID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userID` int(10) unsigned NOT NULL,
  `title` varchar(191) DEFAULT NULL,
  `description` text,
  `original` varchar(191) DEFAULT NULL,
  `yield` varchar(255) DEFAULT NULL,
  `activeTime` varchar(255) DEFAULT NULL,
  `totalTime` varchar(255) DEFAULT NULL,
  `notes` text,
  `image` varchar(255) DEFAULT NULL,
  `createdAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`recipeID`),
  KEY `userID` (`userID`),
  FULLTEXT KEY `title` (`title`,`description`),
  CONSTRAINT `recipe_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `user` (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `instruction` (
  `instructionID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `recipeID` int(10) unsigned NOT NULL,
  `step` tinyint(3) unsigned DEFAULT NULL,
  `instruction` text,
  PRIMARY KEY (`instructionID`),
  KEY `recipeID` (`recipeID`),
  CONSTRAINT `instruction_ibfk_1` FOREIGN KEY (`recipeID`) REFERENCES `recipe` (`recipeID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And to be complete, here are the models for Recipe and Instruction respectively

class Recipe extends Model
{
    public $recipeID, $userID, $title, $description, $original, $yield, $activeTime, $totalTime, $notes, $image, $createdAt, $updatedAt;

    protected $table = 'recipe';
    public $timestamps = false;
    protected $primaryKey = 'recipeID';

    public function instructions() {
        return $this->hasMany('App\Instruction', 'recipeID', 'recipeID');
    }

    // Other (working) relations ommited
}

class Instruction extends Model
{
    public $instructionID, $recipeID, $step, $instruction;

    protected $table = 'instruction';
    public $timestamps = false;
    protected $primaryKey = 'instructionID';

    public function recipe() {
        return $this->belongsTo('App\Recipe', 'recipeID', 'recipeID');
    }
}

The relations are not correct, you need to specify the foreign and the local key

public function instructions() {
    return $this->hasMany('App\Instruction', 'recipeID', 'instructionID');
}

public function recipe() {
    return $this->belongsTo('App\Recipe', 'recipeID', 'instructionID');
}

Documentation: http://laravel.com/docs/5.1/eloquent-relationships#one-to-many

Nope, that didn't work - also, I don't think its correct. The foreign key field in the instruction table is 'recipeID' (for the second parameter in hasMany)

Foreign key always comes first

public function comments()
{
    return $this->hasMany('App\Comment', 'foreign_key', 'local_key');
}

public function post()
{
    return $this->belongsTo('App\Post', 'foreign_key', 'other_key');
}

Sorry, was a typo. I meant second.

Also, to be clear, the foreign keys share the same name as the primary keys it relates to.

So, a link to a recipe in another table would use the name 'recipeID' - just like in Recipe which has 'recipeID' as its primary key

This is mainly due to being able to do something like left join ingredients using(recipeID) - much cleaner MySQL syntax.

Oke my brain just broke.... I have no clue here on what is correct and what is incorrect...

Basically, they share the same key name. The relations are set up correctly in Lumen and in the DB, and the related models are being called correctly, however it is only returning an empty array.

http://i.imgur.com/t436AMw.png Here is a diagram of the relationship if it helps.

I'm not sure if custom key names are the issue, but this was working before I changed the naming of the keys. Custom key names work with Many-to-Many relationships, but not with One-to-Many.

Just checked the database logs - the queries that Eloquent is crafting are correct and work (pulling out the data they are meant to)

Somewhere in the framework it is losing the 'instructions' it has just pulled out..

Thinking logically, you say Eloquent is building the correct queries.

I will guarantee you that Laravel is not broken.

That leads me to believe you haven't set your data up correctly.

Check your instruction's actually have the correct recipe IDs set.

Try die and dumping this:

$recipes = Recipe::where('recipeID', $id)
        ->with('instructions')
        ->get();

dd($recipes);

Does any other recipe have the relation loaded correctly?

Actually, I think it could be something in Laravel/Lumen..

I've rebuild the database structure manually from scratch, the relationships are set up and correctly named, I've followed all the documentation. It isn't pulling out instructions for ANY recipe, regardless of if they have them or not.

Either theres some simple, not-obvious mistake in how I've set up the relations in Lumen, or its an issue lower down in the code. After 3 days of looking over it, I might raise an issue on GitHub for Taylor to look at, as from what I can tell, it should work

If you are doing a join, followed by an eager load:

$recipes = Recipe::join('instructions', 'recipe.id', '=', 'instructions.recipe_id')->get();

$recipes->load(['instructions']);

This could cause issues if the tables have the same column names. Fix by only selecting the columns from the first table:

$recipes = Recipe::join('instructions', 'recipe.id', '=', 'instructions.recipe_id')
    ->select('recipes.*')
    ->get();

Sorry if this revive a zombie thread. I too have this problem, assuming that the relationship is correctly defined. Have you try use get() as to debug it?

For my case, it also returned an empty results if I use select(), if the select() doesn't consist of columns of the id between the two relationship. i.e.: in your case, the "recipeID" (both is the same in your recipe table and instruction table). However, I tested by removing the select() and replace with get() and it does return.

To get your select(), simply include additional "recipeID" as one of the eager loading.

source: http://stackoverflow.com/questions/43122127/eager-loading-relationship-returns-empty-using-select-in-with-clause