Relationship with multiple foreign key in same table in datatable with Laravel, Eloquent

Question

I'm using Laravel 8, Eloquent and Datatables for few days now. I actually find how i can display a datatable with one of my table with datatable and Eloquent.

My problem now is that I need to display the name in a column (that you can find in the "appellations" table) instead of his foreign key.

screenshot of the actual result of my datatables

This is a screenshot of what I have actually, and my aim is to display the "abrege" that you can find in the "appellations" table in the 2 column I show you in red ("App CVI" and "App engage"), instead of they foreign key.

My code actually is :

My "SousParcelle" Model:

class SousParcelle extends Model
{
    use HasFactory;
    protected $table = 'sous_parcelles';
    protected $fillable = ['id',
        'parcelle_id',
        'cepage_id',
        'appellation_cvi_id',
        'dist_entre_rang',
        'dist_sur_rang',
        'densite_theorique',
        'superficie',
        'annee_plantation',
        'modification_par',
        'modification_le',
        'validee',
        'affectation_appellation_id',
        'affectation_superficie'];

    public function appellation(){
        return $this->belongsTo(Appellation::class, 'affectation_appellation_id', 'id');
    }
}

My "Appellation" Model :

class Appellation extends Model
{
    use HasFactory;
    protected $table = 'appellations';
    protected $fillable = ['id', 'libelle', 'abrege', 'siqo_id'];

    public function sousparcelles(){
        return $this->hasMany(Appellation::class, 'affectation_appellation_id', 'id');
    }
}

My "SousParcellesController" Datatable Controller :

class SousParcellesController
{
    public function index()
    {
        return view('welcome');
    }

    public function getSousParcelles(Request $request)
    {
        if ($request->ajax()) {
            $query = SousParcelle::with(['appellation' => function($query){
                $query->select('abrege');
            }]);

            return DataTables::of($query)
            ->addIndexColumn()
            ->addColumn('App engagee', function(SousParcelle $sousparcelles){
                return $sousparcelles->appellations()->abrege;
            })
            ->make(true);
        }
    }
}

The script of my view "welcome.blade.php" :

        <table id="sousparcelles" class="table table-bordered yajra-datatable">
            <thead>
            <tr>
                <th>Référence Cadastrale</th>
                <th>Commune</th>
                <th>Plantation</th>
                <th>Cepage</th>
                <th>App CVI</th>
                <th>Sup CVI</th>
                <th>App engagee</th>
                <th>Sup engagee</th>
            </tr>
            </thead>
            <tbody>
            </tbody>
        </table>


 <script type="text/javascript">
            function load_table(appellation_id) {

                if ($.fn.dataTable.isDataTable('.yajra-datatable')) {
                    $('.yajra-datatable').DataTable().destroy();
                }

                var table = $('.yajra-datatable').DataTable({
                    processing: true,
                    serverSide: true,
                    searching: true,

                    ajax: "{{ route('sousparcelles.liste') }}",
                    columns: [
                        {data: 'dist_entre_rang', name: 'dist_entre_rang'},
                        {data: 'parcelle_id', name: 'parcelle_id'},
                        {data: 'annee_plantation', name: 'annee_plantation'},
                        {data: 'cepage_id', name: 'cepage_id'},
                        {data: 'appellation_cvi_id', name: 'appellation_cvi_id', type: 'selection'},
                        {data: 'superficie', name: 'superficie'},
                        {data: 'affectation_appellation_id', name: 'appellations.abrege'},
                        {data: 'affectation_superficie', name: 'affectation_superficie'},
                    ]
                });
            }
            $(function () {
                load_table(5);
            });

            function change_appellation() {
                load_table(6);
            }
</script>

Could someone enlighten me about this relationship communication please ?

Answer 1

UPDATE ! I found the solution ! I decided to change the whole request and now it work well. Here is my solution :

The changed part un my controller (with more 'join' to other tables and a 'where' to only have the entries of connected account):

    public function getSousParcelles(Request $request)
    {
        if ($request->ajax()) {
            $id = Auth::id();
            $userData = DB::table('sous_parcelles')
                ->join('appellations as affectation', 'affectation.id', '=', 'sous_parcelles.affectation_appellation_id')
                ->join('appellations as engagee', 'engagee.id', '=', 'sous_parcelles.appellation_cvi_id')
                ->join('parcelles', 'parcelles.id', '=', 'sous_parcelles.parcelle_id')
                ->join('users', 'users.id', '=', 'parcelles.user_id')
                ->select('sous_parcelles.*', 'affectation.abrege as affectationlib', 'engagee.abrege as engageelib', 'parcelles.campagne as parcellescamp')->where('user_id', '=', $id);

            return Datatables::of($userData)->filter(function ($query) use ($request) {
            })->make(true);
        }

And here is my new JS script :

var table = $('.yajra-datatable').DataTable({
                    processing: true,
                    serverSide: true,
                    searching: false,

                    ajax: "{{ route('sousparcelles.liste') }}",
                    columns: [
                        {data: 'parcellescamp', name: 'parcelles.campagne'},
                        {data: 'parcelle_id', name: 'sous_parcelles.parcelle_id'},
                        {data: 'annee_plantation', name: 'sous_parcelles.annee_plantation'},
                        {data: 'cepage_id', name: 'sous_parcelles.cepage_id'},
                        {data: 'affectationlib', name: 'affectation.abrege'},
                        {data: 'superficie', name: 'sous_parcelles.superficie'},
                        {data: 'engageelib', name: 'engagee.abrege'},
                        {data: 'affectation_superficie', name: 'sous_parcelles.affectation_superficie'},
                    ]
                });

All other files do not change for this problem.

I hope it will be useful for someone.