Get all linked objects with 1 API call

[bandtank]

I have the following linked entities:

Timesheet
TimesheetData (many-to-one relationship with Timesheet)
Project (one-to-many relationship with TimesheetData)

For example, this is what you might see in the database:

Timesheet0
  TimesheetData0 Project0
  TimesheetData1 Project4
  TimesheetData2 Project5
Timesheet1
  TimesheetData3 Project10
  TimesheetData4 Project5
  TimesheetData5 Project0

To get all of the data, I'm currently doing this:

// Get list of timesheets
GET /timesheet

//Get the timesheetData objects that are linked to a timesheet object
GET /timesheet/{id}/timesheetData

// Get the project object that is linked to a timesheetData object
GET /timesheetData/{id}/project

I have to parse many, many calls to the server because the objects are queried like this:

timesheets = get Timesheet objects from API (GET /timesheet)
for each timesheet
  timesheetDatas = get TimesheetData objects from API (GET /timesheet/{id}/timesheetdata)
  foreach timesheetData
    project = get Project objects from API (GET /timesheetData/{id}/project)

However, it would be a lot less load on my server if I could get all of that data at once. It would also be a big improvement if I could get the project object associated with each timesheetData object while also getting the timesheetData object. It would drastically reduce the number of calls. I can't figure out how to do any of that, though.

Ideally, I would be able to do this:

GET timesheet with all timesheet/id/timesheetData and timesheet/id/timesheetData/id/project objects

I see this in application/Espo/Resources/routes.json:

311   {
312     "route":"/:controller/:id/:link",
313     "method":"get",
314     "params":{
315         "controller":":controller",
316         "action":"listLinked",
317         "id":":id",
318         "link":":link"
319     }
320   } 

It looks like 'listLinked' is the action that queries linked objects. Is there a way to use that action to query the parent object AND all of the child objects at the same time?

[tanya]

Hello
No, but you can create own action. Add it to controller and define the route

[bandtank]

Does the solution in the following post accomplish my goal? It seems like it does, but he didn't say to add a route. Is the getActionxxxxx parsed automatically by the base controller?

http://forum.espocrm.com/forum/developer-help/10034-how-to-create-a-controller-routing-and-views-template-in-html

[bandtank]

Ok, so the solution above worked in terms of setting up the controller method, route, etc. I posted my solution there. However, I can't figure out how to get the repository to select my TimesheetData objects with the related Project objects. This is what I have so far:

18     $data = $this->getEntityManager()->getRepository('TimesheetData')
19       ->where(array(
20         'timesheetId' => $timesheetId
21       ))->order('order',false)->find();
        ...
25     return json_encode(array('total' => $data->count(), 'data' => $data->toArray())); 

The result is this:

{
    "total": 8,
    "data": [
        {
            "id": "59f6a7bdbd420aad7",
            "name": "data",
            "deleted": false,
            ...
            "projectId": "59ed70ee523556f77",
            "projectName": "00-0000-Z1"
        },
        {
            "id": "59f6a7bdb1568fc33",
            "name": "data",
            "deleted": false,
            ...
            "projectId": "59ed76c96e05dd548",
            "projectName": "00-0000-Z3"
        },
        {
             ...
        }
    ]
}

How do I get it to include the actual project object? I'm trying to get something like this:

{
    "total": 8,
    "data": [
        {
            "id": "59f6a7bdbd420aad7",
            "name": "data",
            "deleted": false,
            ...
            "project":{
                "projectId": "59ed70ee523556f77",
                "projectName": "00-0000-Z1"
                ...
            }

        },
        {
            "id": "59f6a7bdb1568fc33",
            "name": "data",
            "deleted": false,
            ...
            "project":{
                "id: "59ed76c96e05dd548",
                "projectName": "00-0000-Z3"
                ...
            }
        },
        {
             ...
        }
    ]
}

I've seen this called 'with(...)' in other frameworks. I know of the findRelated function, which works on single objects in a collection:

$related = $this->getEntityManager()->getRepository('TimesheetData')->findRelated($data->current(),'project'); 

but that still requires a loop to query for each Project in each TimesheetData object.

[bandtank]

So far, this is the best I've been able to accomplish:

  1 <?php
  2
  3 namespace Espo\Custom\Controllers;
  4
  5 use Espo\Core\Exceptions\BadRequest;
  6
  7 class Timesheet extends \Espo\Core\Templates\Controllers\BasePlus
  8 {
  9   public function getActionData($params, $data, $request) {
 10     $assignedUserId = $request->get('assignedUserId');
 11     $timesheetId    = $request->get('timesheetId');
 12     if(!$assignedUserId || !$timesheetId) {
 13       throw new BadRequest();
 14     }
 15
 16     $timesheetData = $this->getEntityManager()->getRepository('TimesheetData')
 17       ->where(array(
 18         'timesheetId' => $timesheetId
 19       ))->order('order',false)->find();
 20
 21     $projectIds = array();
 22     foreach($timesheetData->toArray() as $td) {
 23       $projectIds[$td['projectId']] = 1;
 24     }
 25
 26     $projects = array();
 27     foreach(array_keys($projectIds) as $projectId) {
 28       $temp = $this->getEntityManager()->getRepository('Project')
 29         ->where(array(
 30           'id' => $projectId
 31         ))->findOne();
 32       array_push($projects,$temp->toArray());
 33     }
 34
 35
 36     return json_encode(
 37       array(
 38         'timesheetData' => $timesheetData->toArray(),
 39         'projects'      => $projects
 40       )
 41     );
 42   }
 43 }

which returns something like this:

{
    "timesheetDatas": [
        {
            "id": "59f6a7bdbd420aad7",
            "name": "data",
            "deleted": false,
           ...
            "projectId": "5a05a8d950803350d",
            "projectName": "17-0001-D1"
        },
        ...
    ],
    "projects": [
        {
            "id": "5a05a8d950803350d",
            "name": "17-0001-D1",
           ...
        },
        ...
    ]
}

It's not quite the same, but it will work if there's no other way.

[tanya]

You could also override the method toArray in custom/Espo/Custom/Entities/TimesheetData.php

application/Espo/ORM/Entity.php - base path

[bandtank]

Thanks. That worked perfectly.