(Laravel) Get data from a table which an ID correspond with another table that link with that table

Question

I was playing around with my first Laravel project and my question above might be a little bit confusing but let me explain:-

I have 3 tables (actually I have more, but lets ignore it), I have standards, stddetails & sections as shown:-

So the foreign key corresponds are below:-

• column stdPK from table standards = column stdFK from table stddetails

• column ssctionPK from table sections = column ssctionFK from
  table stddetails

And the scenario is this:-

Lets say I have an $id to be match on stdPK from table standards. With that $id, I am required to get all data from table sections. The problem is, I can't find a right query for that as both standards and sections tables only linked with stddetails table.

Currently my query in my web.php is this:-

Route::get('getstddtl/{id?}', function ($id) {

     $stdsec = Section::
            leftJoin('stddetails', 'ssctionFK', '=', 'ssctionPK')
            ->join('standards', function($join) use ($id){
                $join->on('stdPK', '=', 'stdFK')
                        ->where('stdFK', $id);
            });

    return view('standarddtl', [
        'stdsec' => $stdsec

    ]);
});

I thought it should be easy, boy... I was wrong... I hoped someone can help me with this because my brain have very limited thinking capacity.

UPDATE 1:-

I have set the eloquent relationship in each model and uses Laravel's Eloquent method in retrieving the data:-

$stdsec = Stddetail::with('section')->find($id);

All data is retrieved from both stddetails & sections tables, the problem now is difficulty on displaying data from column ssctionName in sections table in a display page as it return an error.

The related code on the display page is below:-

@foreach ($stdsec as $task)
       {{strtoupper($task->ssctionName)}}
@endforeach

The error shown:-

I think the eloquent method is good, now the display part is giving me trouble. Any ideas on how to solve this?

UPDATE 2:-

Here's the models for each table:-

Table standards as Standard model:-

namespace App;

use Illuminate\Database\Eloquent\Model;

class Standard extends Model
{

    public $primaryKey = 'stdPK';

    public function stddetail()
    {
        return $this->hasMany(Stddetail::class, 'stdFK', 'stdPK');
    }
}

Table stddetails as Stddetail model:-

namespace App;

use Illuminate\Database\Eloquent\Model;

class Stddetail extends Model
{
    public $table = "stddetails";
    public $primaryKey = 'sdtlPK';
    protected $fillable = ['stdFK', 'sdtlPgNo', 'sdtlLnNo', 'sdtlText', 'sdtlShrtnote', 'sdtlSchm', 'svrFK', 'ssctionFK', 'sdtlRefLbl'];

    public function standard()
    {   
        return $this->belongsTo(Standard::class, 'stdFK');
    }

    public function section()
    {   
        return $this->belongsTo(Section::class, 'ssctionFK');
    }

}

Table sections as Section model:-

namespace App;

use Illuminate\Database\Eloquent\Model;

class Section extends Model
{
    public $primaryKey = 'ssctionPK';
    public function stddetails()
    {
        return $this->hasMany(Stddetail::class, 'ssctionFK', 'ssctionPK');
    }
}

Solution

Take a look at eloquent relationships: https://laravel.com/docs/5.4/eloquent-relationships

You should be able to set proper relationships(hasMany, belongsTo, etc) between your models (tables) and get all data with a single command (Eloquent will create needed queries for you).

On an unrelated note, I would suggest improving your naming convention. It is really hard to understand event basic links with all acronyms and short names used

Comments

[Kaydarin]

What do you mean "Eloquent will create needed queries for you"? I thought we need to create the queries ourselves... I did set the relationships in each table's model...

[Nerman]

That the whole purpose of using ORM, like Eloquent. You are 'just' writing code, with so-called fluent syntax and if you got your relationships set up right, you would be able to get all data with just using Models.

[Nerman]

For instance: $details = Standards::with('stddetails')->find($id); Would return standards model with a property stddetails of collection type. You did not have to write SQL queries, Eloquent did that for you. Purpose of it is to abstract that layer from you so you can just write code (Sorry about two comments, I hit enter too soon and SO won't let me edit the first comment)

[Kaydarin]

I have read the documentation and set all the relations in each models but until the "Querying Relations", I don't get where the query supposed to be placed. Is it in the routes, controllers or models? Plus, back to my original question, if possible, could you provide me the query with the eloquent method? I'm kinda blurred with it...

[Bagus Tesa]

@Kaydarin, you don't have to put a literal sql queries.. you only need 'eloquent' queries - you could put this everywhere (it's related to system design), but most of the time people put them into controllers. anyway, if you wish for the query in eloquent, you'll need a model first..

[Kaydarin]

@BagusTesa Thank you, I have put the 'queries' in controller as you suggested and I have put the relationships in all of the related table and uses the eloquent method as suggested by Nerman. Now I have a new problem to display them...

[Bagus Tesa]

@Kaydarin, care to post the model? and tried to dd($stdsec)

[Kaydarin]

@BagusTesa done... Did it and it return all the data from both stddetails and sections with the $id. The query works but the displaying part is boggling me as my programming knowledge aren't very good at the moment...

[Bagus Tesa]

ahhh! i see, i bet you think it works just like joins. but unfortunately not.. try $task->section->ssctionName. i thought you'll get some gist by looking at the dd's result - oh well.