Unable to get specific Columns from both tables that has one to many relationship in Laravel

Question

I have two tables One is farmers_table and other is farming_practices and their relationship has One to Many relationship. From these two tables, I want to get all rows of selected columns from both farmers_table and farming_practices table. I tried many ways but could not get the result what I want.

Here is the code:

Farmer Model:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Farmer extends Model
{
    protected $fillable = [
        'course_name',
        'registration_number',
        'district',
        'block_village',
        'fullname',
        'head_of_household',
        'gender',
        'dob',
        'p_house_no_bldg_apt',
        'p_street_road_lane',
        'p_area_locality_road',
        'p_village_town_city',
        'p_landmark',
        'p_post_office',
        'p_district',
        'p_pincode',

        'c_house_no_bldg_apt',
        'c_street_road_lane',
        'c_area_locality_road',
        'c_village_town_city',
        'c_landmark',
        'c_post_office',
        'c_district',
        'c_pincode',

        'mobile_number',
        'email',
        'social_group',
        'economic_group',
        'card_number_apl_bpl_antoydya',
        'adhaar_number',
        'govt_issued_id_card',
        'id_number',

        'name_of_farming_practice',
        'years_of_exp_as_farmer',
        'working_with_organisation_unit',

        'recieved_any_award',
        'is_own_smartphone',

        'fb',
        'insta',
        'whatsapp',
        'linkedin',

        'certificate_number',

        'file_name',
        'original_file_name',
        'second_file_name',
        'second_original_file_name',
    ];

    public function farmingPractices()
    {
        return $this->hasMany(FarmingPractices::class);
    }
}

FarmingPractices Model :

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class FarmingPractices extends Model
{
    protected $fillable = [
        'practice_name',
        'farmer_id'
    ];

    public function farmer()
    {
        $this->belongsTo(Farmer::class);
    }
}

FarmerController:

$data =  Farmer::with(
            [
                'farmingPractices' => function ($query) {
                    $query->select('id', 'practice_name');
                }
            ]
        )->select(
                'registration_number',
                'course_name',
                'district',
                'block_village',
                'fullname',
                'head_of_household',
                'gender',
                'dob',
                'p_house_no_bldg_apt',
                'p_street_road_lane',
                'p_area_locality_road',
                'p_village_town_city',
                'p_landmark',
                'p_post_office',
                'p_district',
                'p_pincode',

                'c_house_no_bldg_apt',
                'c_street_road_lane',
                'c_area_locality_road',
                'c_village_town_city',
                'c_landmark',
                'c_post_office',
                'c_district',
                'c_pincode',

                'mobile_number',
                'email',
                'social_group',
                'economic_group',
                'card_number_apl_bpl_antoydya',
                'adhaar_number',
                'govt_issued_id_card',
                'id_number',
                'working_with_organisation_unit',
                'is_own_smartphone',

                'fb',
                'insta',
                'whatsapp',
                'linkedin',

                'certificate_number',
            )->get();
            dd($data->toArray());

Got this result:

array:6 [▼
  0 => array:43 [▼
    "registration_number" => 4
    "course_name" => "MCA"
    "district" => "ZwQOWLTCTI"
    "block_village" => "lk6LIsA6hh"
    "fullname" => "nE9SfSYk9N"
    "head_of_household" => "RuqXZXttTv"
    "gender" => "Female"
    "dob" => "1990-03-10"
    "p_house_no_bldg_apt" => "92PjsVG3Vd"
    "p_street_road_lane" => "xRUnMEkgi6"
    "p_area_locality_road" => "qoZFL4LxvZ"
    "p_village_town_city" => "OMg18G7Yre"
    "p_landmark" => "EDtijFL17d"
    "p_post_office" => "7LaYXlWQmc"
    "p_district" => "154786"
    "p_pincode" => 698574
    "c_house_no_bldg_apt" => "eNJQVwAfEc"
    "c_street_road_lane" => "yEOri4USwy"
    "c_area_locality_road" => "0rPt7CNNBt"
    "c_village_town_city" => "y2DJuO2W54"
    "c_landmark" => "aPZquvatII"
    "c_post_office" => "LyWFxu5UlF"
    "c_district" => "meOjKLSr22"
    "c_pincode" => 785991
    "mobile_number" => "0987000260"
    "email" => "ZLXuNl9cQM@gmail.com"
    "social_group" => "GEN"
    "economic_group" => "Antyodya"
    "card_number_apl_bpl_antoydya" => "0657247874"
    "adhaar_number" => "006487049854"
    "govt_issued_id_card" => "PrbWF6BLuj"
    "id_number" => "3504713664"
    "years_of_exp_as_farmer" => "3"
    "working_with_organisation_unit" => 0
    "recieved_any_award" => 1
    "is_own_smartphone" => 1
    "fb" => "4HxqZGIX8g"
    "insta" => "scFJ9B6w9A"
    "whatsapp" => "pHivq2K9KV"
    "linkedin" => "bkGOvVhXVo"
    "certificate_number" => "4"
    "farming_practices" => null
  ]
  1 => array:43 [▶]
  2 => array:43 [▶]
  3 => array:43 [▶]
  4 => array:43 [▶]
  5 => array:44 [▶]
]

I stuck on this issue since yesterday. help needed. thanks in advance.

Answer 1

You need to include the primary key of Farmer model and foreign key of FarmingPractice model.

Try

$data = Farmer::with('farmingPractices:id,farmer_id,practice_name')
        ->select(
            'id',
            'registration_number',
            'course_name',
            'district',
            'block_village',
            'fullname',
            'head_of_household',
            'gender',
            'dob',
            'p_house_no_bldg_apt',
            'p_street_road_lane',
            'p_area_locality_road',
            'p_village_town_city',
            'p_landmark',
            'p_post_office',
            'p_district',
            'p_pincode',

            'c_house_no_bldg_apt',
            'c_street_road_lane',
            'c_area_locality_road',
            'c_village_town_city',
            'c_landmark',
            'c_post_office',
            'c_district',
            'c_pincode',

            'mobile_number',
            'email',
            'social_group',
            'economic_group',
            'card_number_apl_bpl_antoydya',
            'adhaar_number',
            'govt_issued_id_card',
            'id_number',
            'working_with_organisation_unit',
            'is_own_smartphone',

            'fb',
            'insta',
            'whatsapp',
            'linkedin',

            'certificate_number',
        )
        ->get();

dd($data->toArray());