Announcement

Collapse
No announcement yet.

Get all linked objects with 1 API call

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Get all linked objects with 1 API call

    I have the following linked entities:

    Code:
    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:

    Code:
    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:

    Code:
    // 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:
    Code:
    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:

    Code:
    GET timesheet with all timesheet/id/timesheetData and timesheet/id/timesheetData/id/project objects
    I see this in application/Espo/Resources/routes.json:

    PHP Code:

    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?

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

    Comment


    • #3
      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?

      Hi ! I am new to EspoCRM and I find it very cool with a very high potential with good structure and good technologies used. But I have many questions to be

      Comment


      • #4
        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:

        PHP Code:

        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:

        Code:
        {
            "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:

        Code:
        {
            "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:

        PHP Code:
        $related $this->getEntityManager()->getRepository('TimesheetData')->findRelated($data->current(),'project'); 
        but that still requires a loop to query for each Project in each TimesheetData object.

        Comment


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

          PHP Code:

            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:

          Code:
          {
              "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.

          Comment


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

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

            Comment


            • #7
              Thanks. That worked perfectly.

              Comment

              Working...
              X