Nested json response from many tables in PHP

Question

As of now I can call an API using laravel.

I have 2 different views in my database.

I have view_project_percentage and view_projtask

view_project_percentage is a list of project that contains project details.

view_projtask is a list of task per project

Like for example.

If I have 3 projects in my view_project_percentage

like

ProjCode of ABC1,ABC2, ABC3

and In my view_projtask

I have

2 task for ProjectCode ABC1

1 task for ProjectCode ABC2

3 task for ProjectCode ABC3

Now what I'm trying to do is to get the json array response like this

 [
{
    "id": 9,
    "proj_code": "ZQQKVOTRJJNZ",
    "proj_title": "P12",
    "proj_desc": "Project Description 12",
    "target_man_days": 1000000,
    "issue": 0,
    "report": 1,
    "total_employee": 3,
    "hours_8": "4",
    "hours_6": "0.0",
    "hours_3": "0",
    "total_weight_progress": "5.00",
    "est_start_date": "2020-01-20 09:42:00",
    "est_end_date": "2020-01-20 21:42:00",
    "act_start_date": "2020-01-20 09:42:00",
    "act_end_date": "2020-01-20 21:42:00",
    "longitude": "121.07642460",
    "latitude": "14.56055010",
    "location": "Pasig, Metro Manila, Philippinae",
    "status": null,
    "deleted": 0,
    "by_id": 7,
    "updated_by": "Keigh Dee",
    "created_at": "2020-01-20 09:42:31",
    "updated_at": "2020-01-22 14:01:08",
    {
       "taskCode": "J5X1FHCMVFSQ",
       "total_task_weight": null,
       "taskWeight": "50.00",
       "plan_days": "5",
       "actual_days": "5",
       "task_title": "Task 5",
       "task_desc": "Description 5",
       "taskDeleted": 0,
        "view_projtask_deleted": 0
    },
    {
        "taskCode": "FZPRFIWOKBFQ",
        "total_task_weight": "5.00",
        "taskWeight": "25.00",
        "plan_days": "5",
        "actual_days": "5",
        "task_title": "Task 2",
        "task_desc": "Description 2",
        "taskDeleted": 0,
        "view_projtask_deleted": 0
   }
},

........so on

As you can see on my example json response I have

1 project with 2 tasks

here's my working code

public function get_all_projtask()
{
    $proj_query = "SELECT a.id, a.proj_code, a.proj_title, a.proj_desc, a.target_man_days, a.issue, a.report, a.total_employee, a.hours_8, a.hours_6, a.hours_3,
    a.total_weight_progress, a.est_start_date, a.est_end_date, a.act_start_date, a.act_end_date, a.longitude, a.latitude, a.location, a.status, a.deleted, 
    a.by_id, a.updated_by, a.created_at, a.updated_at, b.taskCode, b.total_task_weight, b.taskWeight, b.plan_days, b.actual_days, b.task_title, b.task_desc, b.taskDeleted, b.deleted as view_projtask_deleted

    FROM `view_projtask` AS b LEFT JOIN `view_project_percentage` AS a ON b.projCode = a.proj_code";

    $proj = DB::connection('mysql')->select($proj_query);

    if(count($proj)){
        return $proj;
    }else{
        return response([
            'status'=>'bad',
            'message'=>'No record found'
            ]);
    }
}

My query in here is just getting all the tasks per specific project from view_projtask

I'm trying to do a json response that getting all the project list uniquely and inside of that json of project contains multiple tasks

My view_projtask

My view_project_percentage

update

This is my current output from api

[
{
    "id": 9,
    "proj_code": "ZQQKVOTRJJNZ",
    "proj_title": "P12",
    "proj_desc": "Project Description 12",
    "target_man_days": 1000000,
    "issue": 0,
    "report": 1,
    "total_employee": 3,
    "hours_8": "4",
    "hours_6": "1.0",
    "hours_3": "0",
    "total_weight_progress": "5.00",
    "est_start_date": "2020-01-20 09:42:00",
    "est_end_date": "2020-01-20 21:42:00",
    "act_start_date": "2020-01-20 09:42:00",
    "act_end_date": "2020-01-20 21:42:00",
    "longitude": "121.07642460",
    "latitude": "14.56055010",
    "location": "Pasig, Metro Manila, Philippinae",
    "status": null,
    "deleted": 0,
    "by_id": 7,
    "updated_by": "Keigh Dee",
    "created_at": "2020-01-20 09:42:31",
    "updated_at": "2020-01-22 14:01:08",
    "taskCode": "OKIX19IR1DST",
    "total_task_weight": null,
    "taskWeight": "25.00",
    "plan_days": "5",
    "actual_days": "5",
    "task_title": "Task 1",
    "task_desc": "Description 1",
    "taskDeleted": 0,
    "view_projtask_deleted": 0
},
{
    "id": 9,
    "proj_code": "ZQQKVOTRJJNZ",
    "proj_title": "P12",
    "proj_desc": "Project Description 12",
    "target_man_days": 1000000,
    "issue": 0,
    "report": 1,
    "total_employee": 3,
    "hours_8": "4",
    "hours_6": "1.0",
    "hours_3": "0",
    "total_weight_progress": "5.00",
    "est_start_date": "2020-01-20 09:42:00",
    "est_end_date": "2020-01-20 21:42:00",
    "act_start_date": "2020-01-20 09:42:00",
    "act_end_date": "2020-01-20 21:42:00",
    "longitude": "121.07642460",
    "latitude": "14.56055010",
    "location": "Pasig, Metro Manila, Philippinae",
    "status": null,
    "deleted": 0,
    "by_id": 7,
    "updated_by": "Keigh Dee",
    "created_at": "2020-01-20 09:42:31",
    "updated_at": "2020-01-22 14:01:08",
    "taskCode": "FZPRFIWOKBFQ",
    "total_task_weight": "5.00",
    "taskWeight": "25.00",
    "plan_days": "5",
    "actual_days": "5",
    "task_title": "Task 2",
    "task_desc": "Description 2",
    "taskDeleted": 0,
    "view_projtask_deleted": 0
},
{
    "id": 9,
    "proj_code": "ZQQKVOTRJJNZ",
    "proj_title": "P12",
    "proj_desc": "Project Description 12",
    "target_man_days": 1000000,
    "issue": 0,
    "report": 1,
    "total_employee": 3,
    "hours_8": "4",
    "hours_6": "1.0",
    "hours_3": "0",
    "total_weight_progress": "5.00",
    "est_start_date": "2020-01-20 09:42:00",
    "est_end_date": "2020-01-20 21:42:00",
    "act_start_date": "2020-01-20 09:42:00",
    "act_end_date": "2020-01-20 21:42:00",
    "longitude": "121.07642460",
    "latitude": "14.56055010",
    "location": "Pasig, Metro Manila, Philippinae",
    "status": null,
    "deleted": 0,
    "by_id": 7,
    "updated_by": "Keigh Dee",
    "created_at": "2020-01-20 09:42:31",
    "updated_at": "2020-01-22 14:01:08",
    "taskCode": "J5X1FHCMVFSQ",
    "total_task_weight": null,
    "taskWeight": "50.00",
    "plan_days": "5",
    "actual_days": "5",
    "task_title": "Task 5",
    "task_desc": "Description 5",
    "taskDeleted": 0,
    "view_projtask_deleted": 0
},
{
    "id": 8,
    "proj_code": "SUZ82OJI091M",
    "proj_title": "P1",
    "proj_desc": "Project Description 1",
    "target_man_days": 10000,
    "issue": 0,
    "report": 0,
    "total_employee": 2,
    "hours_8": "1",
    "hours_6": "0.5",
    "hours_3": "0",
    "total_weight_progress": null,
    "est_start_date": "2020-01-20 09:41:00",
    "est_end_date": "2020-01-20 21:41:00",
    "act_start_date": "2020-01-20 09:41:00",
    "act_end_date": "2020-01-20 21:41:00",
    "longitude": "121.07642460",
    "latitude": "14.56055010",
    "location": "Pasig, Metro Manila, Philippinae",
    "status": null,
    "deleted": 0,
    "by_id": 7,
    "updated_by": "Keigh Dee",
    "created_at": "2020-01-20 09:41:29",
    "updated_at": "2020-01-20 09:41:29",
    "taskCode": "J5X1FHCMVFSQ",
    "total_task_weight": null,
    "taskWeight": "50.00",
    "plan_days": "5",
    "actual_days": "5",
    "task_title": "Task 5",
    "task_desc": "Description 5",
    "taskDeleted": 0,
    "view_projtask_deleted": 0
},

Hello,did you consider using Eloquent and Laravel models to fetch your results ? That would make the formatting of your data much easier and neat if so, I recommend it. — Mickael Amar
– Mickael Amar, Commented Feb 5, 2020 at 8:10
could you give a dummy mysql dump to test the queries on? Is your query giving appropriate response in phpmyadmin? — Sahith Vibudhi
– Sahith Vibudhi, Commented Feb 5, 2020 at 10:11

jimmy winstone · Accepted Answer · 2020-02-05 10:15:30Z

2

If you consider using eloquent (which you really should) your models for these tables shall look something like:

class Project extends Model
{
    protected $table = "view_project_percentage";

    public function tasks()
    {
        return $this->hasMany(ProjectTask::class, 'projCode', 'proj_code');
    }
}

class ProjectTask extends Model
{
    protected $table = "view_projtask";

    public function project()
    {
        return $this->belongsTo(Project::class, 'projCode', 'proj_code');
    } 
}

Then all you need in your controller:

public function get_all_projtask()
{   
    return response()->json(Project::with('tasks')->get());
}

edited Feb 5, 2020 at 10:15

answered Feb 5, 2020 at 10:04

jimmy winstone

889 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Pablo Over a year ago

I did this seems too close here's my error occured ` "message": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'view_projtask.proj_code' in 'where clause' (SQL: select * from view_projtask where view_projtask.proj_code in (?))",`

Pablo Over a year ago

proj_code is a primary key of view_project_percentage and projCode is a foreign inside of view_projtask is there any idea to fix thi s one?

jimmy winstone Over a year ago

my mistake, try replace 'proj_code' and 'projCode' in your models. I mean in these relations hasMany/belongsTo EDIT: ive edited answer

jimmy winstone Over a year ago

yes, check this out laravel.com/docs/master/…

Pablo Over a year ago

I tried this one and it works too

return $this->belongsTo(Project::class, 'proj_code', 'projCode')         ->where('deleted',0)         ->where('taskDeleted',0);

thanks man

|

Collectives™ on Stack Overflow

Nested json response from many tables in PHP

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related