Eloquent's join

Anyone, please?

Your structure should be like this:

items:

    id
    post_id
    name
    category
    ...

item_details:

    item_id
    description
    ...

item_games:

    item_id
    description
    ...

items would be your main table, in your item model you would have:

public function post()
{
    return $this->belongsTo(Post::class);
}

public function details()
{
    return $this->hasMany(ItemDetail::class);
}

public function games()
{
    return $this->hasMany(ItemGame::class);
}

Next, in both the ItemDetail and ItemGame models you would have:

public function item()
{
    return $this->belongsTo(Item::class);
}

Also you have written:

protected $primaryKey = 'post_id'

I am assuming the post_id column is referencing a posts table, in which case you don't want the post_id to be the primary key. The other possibility is that post is another name for item and you do want that to be the primary column. I would advise against this. In my experience it is a good idea to keep names consistent and references to tables should be clear as to which table they are referencing. Also you already have an id column in your items table which is used as the primary key by default. This is a popular convention and makes the post_id column redundant. If you really want to make the primary key post_id then there is no need for the id column.

Thanks to both of you, trying to understand all Laravel's mechanics is not that simple when you never did any studies in developement :D

@ Qlic: Tried, I now understand (I think) how does this works with your example, but I don't understand why Laravel is renaming the colums. With your code, when I'm doing:

$test = $itemsModel::find($id)->details()->first();

Laravel returns an error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'items_details.items_item_id' in 'where clause' (SQL: select * from `items_details` where `items_details`.`items_item_id` = 0 and `items_details`.`items_item_id` is not null limit 1)

I understand the error, I don't have any "items_item_id" in my "items_details" table, but I'm not calling this column name, why is Laravel trying to get this one and not simply, "items_details.item_id" ?

[EDIT]: I get it, as the documentation said: "Eloquent determines the foreign key of the relationship based on the model name. In this case, the Phone model is automatically assumed to have a user_id foreign key. If you wish to override this convention, you may pass a second argument to the hasOne method:", I should have specified the foreign key, I dit not, now with:

return $this->hasMany(ItemsDetails::class, 'item_id');

It's okay, but I'm still getting only "items_details" columns :O Still investigating, I'll edit if I find the solution.

@ Stofflees: You pointed right on my error. I don't know what I was thinking when writing my migrations, post_id was refering (in my head) to item_id. I re-runned my migrations so it's now as it should be :)

Okay, so I've almost reached my goal, I now get informations from both tables, but my "where" clause seems to not be taken in consideration by Eloquent.

I have this request:

$test = $itemsModel::with(['details'])->where('item_id', $id)->first();

The "where" clause seems to be okay on the "items" table, but not on the "item_details" table, I have the first occurence found and not the correct one...

I have this code:

In App\Models\Items.php

public function details()
    {
        return $this->hasOne(ItemsDetails::class, 'item_id');
    }

In App\Models\Details.php

 public function Items () {
        return $this->belongsTo(Items::class);
    }

Thanks

NB: Is there any benefits from using Eloquent? I mean, I want to do the "best" code possible, following the best pratices, but is this really usefull? I'd have finished this request looooong ago with "standard" SQL :(

[EDIT]: Ok, finally got it, my query code now looks like:

$test = $itemsModel::with(
            [
                'details' => function($query) use ($id) { $query->where('item_id', $id); },
                'games'  => function($query) use ($id) { $query->where('item_id', $id); },
            ]
        )->where('item_id', $id)->first();

Can I do better?

And I removed the

public function Items () {
        return $this->belongsTo(Items::class);
    }

Without any visible consequences, is that normal? I mean, it's not here for the "scene"..

Good for you that through reading the docs you found out about the eager load closures, however in this case it should not be needed since the eager loading automatically binds on the relations.

So in your case, when querying on the model Items, the eager load will look for a column called item_id in the item_details table.

In your code example however, you seem to use itemsModel as the name, which is not following the convention of Laravel/Eloquent.

1. Tables should be in snakecase, and in the 'multiple' way: user becomes users.
2. Models are in camelcase, and in the 'single' way: table users has model User.
3. Tables have a incremental field called id, relations call on this id by simply adding the related name, so user_functions will have id, user_id, etc.

@Sreadon here is your answer:

1. Migration creates

       Schema::create('items', function(Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->unsignedInteger('post_id')->unique()->index();
            $table->string('name', 100);
            $table->string('category', 100);
            $table->timestamps();
        });

        Schema::create('item_details', function(Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->unsignedInteger('item_id')->unique()->index();
            $table->text('description');
            $table->timestamps();
        });

        Schema::create('item_games', function(Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->unsignedInteger('item_id')->unique()->index();
            $table->text('description');
            $table->timestamps();
        });

1. Model and controller example

2.1. Use Singular naming convention when creating models as each model instance will be a single item e.g. Item, itemDetail. DB tables would be plural as it contains many items

2.2. ->hasOne and ->belongsTo will Define a one-to-one or many relationship and as such will return a single instance so again name them accordingly.

In short you only need this syntax with relations inside of your item model:

     /**
         * @param $id
         * @return mixed
         */
        public function getItem($id)
        {
            return $this
                ->where('item_id', $id)
                ->with(['itemDetail', 'games'])
                ->first();
        }

Also note that your original syntax would work

** DETAIL WITH LOTS OF EXAMPLES**

     class HomeController extends Controller
    {
        /**
         * @var Item
         */
        private $item;

        /**
         * HomeController constructor.
         * @param Item $item
         */
        public function __construct(Item $item)
        {
            $this->item = $item;
        }


        /**
         * @return $this|\Illuminate\Contracts\View\Factory|\Illuminate\View\View
         */
        public function index()
        {

            if ($item = $this->item->getItem(1) ) {
                
                $exampleRelations = [
                    'game' => $item->game->description,
                    'detail' => $item->itemDetail->description,
                ];
                
                return view('itemView')->with(['item' => $item, 'descriptions' => $exampleRelations]);
            }

            return view('itemErrorView');

        }

        public function getGame($gameId)
        {
            $item = $this->item->find(1);

            $game = $item->getGame($gameId);

//            OR 
            
            $item = $this->item->find(1)->getGame($gameId);

        }
        
    }

    class Item extends Model
    {
        /**
         * @var string
         */
        protected $table = 'items';

        /**
         * @var string
         */
        protected $primaryKey = 'id';

        /**
         * @return \Illuminate\Database\Eloquent\Relations\HasOne
         */
        public function itemDetail()
        {
            return $this->hasOne(ItemsDetail::class);
        }

        /**
         * @return \Illuminate\Database\Eloquent\Relations\HasMany
         */
        public function games()
        {
            return $this->hasMany(ItemGame::class);
        }

        /**
         * @param $id
         * @return mixed
         */
        public function getItem($id)
        {
            return $this
                ->where('item_id', $id)
                ->with(['itemDetail', 'games'])
                ->first();
        }

        /**
         * Purely for illustrative purposes where Im query a specific game 
         * @param $id
         * @return mixed
         */
        public function getGameOfItem($id,$gameId)
        {
            return $this
                ->where('item_id', $id)
                ->with(['itemDetail', 'games' => function($query) use ($gameId) {
                    $query->where('id', $gameId);
                }])
                ->first();
        }

        /**
         * Use the relationship to query a game
         * 
         * @param $gameId
         * @return mixed
         */
        public function getGame($gameId)
        {

          // NOTE: $item->game->description IS NOT THE SAME AS $item->game()->description  - the first will work the 2nd will fail as description is not a method on ItemGame model whereas description is a property on the instance of ItemGame


            return $this
                ->game()
                ->where('id', $gameId)
                ->first();
        }


    }



    class ItemDetail extends Model
    {
        /**
         * @var string
         */
        protected $table = 'item_details';

        /**
         * @var string
         */
        protected $primaryKey = 'id';

        /**
         * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
         */
    public function Item()
    {
        return $this->belongsTo(Item::class);
    }

    }

    class ItemGame extends Model
    {
        /**
         * @var string
         */
        protected $table = 'item_games';

        /**
         * @var string
         */
        protected $primaryKey = 'id';

        /**
         * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
         */
        public function Item()
        {
            return $this->belongsTo(Item::class);
        }


    }

Thanks guys, I made it with your answers. Still trying to optimise / clean as much as I can my code though :)