jvbalcita
1 week ago

Get first data even there is multiple query

Posted 1 week ago by jvbalcita

Not sure if a have the right title for this issue but I have a school data that has a separate database for school year. The school data consist of the list of schools and its relationship user.

School Database

id
user_id
schl_sys_id
schl_name

School Year Database for

school_id
year_from
year_to

The school can have many school admin in this case there's 2 school admin user with the same schl_sys_id

schl_sys_id => SCHL0001

Then the first admin created the school year 2020-2021 and the 2nd admin created 2021-2022 on the same day.

the system will detect the latest creation. however what I want to achieve is regardless of who created the school year for the SCHL0001 should be the first created school year even if I'm passing the ID

Here's my code when showing the school data with the relationship with school year.

    public function show(School $school)
    {
        $database = $school->schl_sys_id;

        $school = DB::table('schools')
        ->join($database.'.school_year as school', 'school.school_id', '=', 'schools.id')
        ->where('schl_sys_id', '=', $database)
        ->limit(1)
        ->get();
    

        return SchoolResource::collection($school);
    }

This will return

id
user_id
schl_sys_id
schl_name
year_from
year_to

Please sign in or create an account to participate in this conversation.