I'm using Laravel 5.8 and

And all these ids are stored in baseinfos table. For example std_degree_id of 8 goes like this:

And now instead of returning number 8, I need to get bas_value and return it in the exported Excel file.

So it would looked like this:

if(students.std_degree_id == 9){
     // print Elementry
}elseif(students.std_degree_id == 10){
     // print Academy
}else{
     ...
}

So how can I do that?

UPDATE #1:

$records = DB::table('members')
            ->where('mys_creator_id',$id)
            ->where('mys_olp_id',4)
            ->join('my_students', 'members.mbr_usr_id', '=', 'my_students.mys_mbr_id')
            ->join('students', 'members.mbr_usr_id', '=', 'students.std_mbr_id')
            ->join('baseinfos', 'students.std_degree_id', '=', 'baseinfos.bas_id')
            ->join('baseinfos', 'members.mbr_gender_id', '=', 'baseinfos.bas_id')
            ->select(...,'baseinfos.bas_value','members.mbr_address','baseinfos.bas_value',...)->get()->toArray();
    return $records;

If I add two more joins with baseinfos table, I get this error:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique

And this mainly because I get baseinfos.bas_value twice in the select.

CodePudding user response：

You'd need to join one more table and show the corresponding field from that table:

public static function getAccounts($id)
    {
        $records = DB::table('members')
            ->where('mys_creator_id',$id)
            ->where('mys_olp_id',4)
            ->join('my_students', 'members.mbr_usr_id', '=', 'my_students.mys_mbr_id')
            ->join('students', 'members.mbr_usr_id', '=', 'students.std_mbr_id')
            ->join('baseinfos', 'students.std_degree_id', '=', 'baseinfos.bas_id')
            ->select('members.mbr_name', 'members.mbr_family', 'members.mbr_father_name','members.mbr_national_code','members.mbr_birthday','members.mbr_phone','members.mbr_mobile','members.mbr_post_code','members.mbr_prv_id','members.mbr_cit_id','members.mbr_gender_id','members.mbr_address','baseinfo.bas_value','students.std_grade_id','students.std_filed_id','students.std_major_id','students.std_school','students.std_education_type_id','my_students.mys_paid_price')
            ->get()->toArray();
        return $records;
    }

CodePudding user response：

I am not sure if you are using getAccounts anywhere else, If you are create a new method to format this data.

    public static function getAccounts($id)
    {
        $degrees = [
            9 => 'Elementry',
            10 => 'Academy',
        ];

        $records = DB::table('members')
            ->where('mys_creator_id',$id)
            ->where('mys_olp_id',4)
            ->join('my_students', 'members.mbr_usr_id', '=', 'my_students.mys_mbr_id')
            ->join('students', 'members.mbr_usr_id', '=', 'students.std_mbr_id')
            ->select('members.mbr_name', 'members.mbr_family', 'members.mbr_father_name','members.mbr_national_code','members.mbr_birthday','members.mbr_phone','members.mbr_mobile','members.mbr_post_code','members.mbr_prv_id','members.mbr_cit_id','members.mbr_gender_id','members.mbr_address','students.std_degree_id','students.std_grade_id','students.std_filed_id','students.std_major_id','students.std_school','students.std_education_type_id','my_students.mys_paid_price')
            ->get();

        // if map is not working add it in collect($records)->map.
        $records->map(function($record) use($degrees) {
            $record->std_degree_id = $degrees[$record->std_degree_id];

            return $record;
        });
    
        return $records->toArray();
    }

But I am guessing $record->std_degree_id is a relation, so just use the relation title instead of the array map i created.