CODEWITHSUNDEEP
laravellaravel-5.7
smita patil
smita patil

Reputation: 45

how to return a json response based on database relationship

I'm quite new to Laravel, Let's say I have 2 tables: main_sport and sub_sport. These two tables have a one-to-many relationship. 1 sport can have many sub sports.

I want the following json format

{
    "success": "1",
    "sports": [
            "id": 1,
            "name_of_categories": "Popular Sports",
       "sub_sports:[
          {
            "id": 1,
            "name_sub_sport_category": "Badminton"
          },
          {
            "id": 2,
            "name_sub_sport_category": "Football"
         },
         {
            "id": 3,
            "name_sub_sport_category": "Cricket"
         },
          ]
      ]

  "sports":[
            "id": 2,
            "name_of_categories": "Team Sports",
       "sub_sports:[
          {
            "id": 4,
            "name_sub_sport_category": "Badminton"
          },
          {
            "id": 5,
            "name_sub_sport_category": "Football"
         },
          ]

]
}

I try for this function and i am getting following result

 public function fetch()
    {

    $query= DB::table('details')
             ->join('table_sub_sport_category','table_sub_sport_category.id','=','details.sub_id')
             ->join('table_main_sport_category','table_main_sport_category.id','=','details.main_id')
             ->select(DB::raw('table_main_sport_category.id as id'),'table_main_sport_category.name_of_categories','table_sub_sport_category.name_sub_sport_category')
             ->get()

    return response()->json(['success' =>'1','data'=>$query]);

    }



{
    "success": "1",
    "data": [
        {
            "id": 1,
            "name_of_categories": "Popular Sports",
            "name_sub_sport_category": "Badminton"
        },
        {
            "id": 1,
            "name_of_categories": "Popular Sports",
            "name_sub_sport_category": "Football"
        },
        {
            "id": 1,
            "name_of_categories": "Popular Sports",
            "name_sub_sport_category": "Cricket"
        },
]
}

Could you help me to get the desired result?

Upvotes: 0

Views: 45

Answers (2)

Ronak Chauhan
Ronak Chauhan

Reputation: 706

Use below code 

$query= DB::table('details')
         ->join('table_sub_sport_category','table_sub_sport_category.id','=','details.sub_id')
         ->join('table_main_sport_category','table_main_sport_category.id','=','details.main_id')
         ->select(DB::raw('table_main_sport_category.id as id'),'table_main_sport_category.name_of_categories','table_sub_sport_category.name_sub_sport_category')
         ->get()

return Response::json([
            'status' => 'error',
            'sports' => $query
        ], 200);

Upvotes: 0

Mosi
Mosi

Reputation: 198

You should define sub_sport in the main sport Model like this:

class Sport extends Model{
    public function sub_sport(){
        return $this->hasMany(SubSport::class);
    }
}

And in your controller you should write this:

$data = Sport::with('sub_sport')->get();

Upvotes: 1

Related Questions