API Route - How to get users in teamId

[giuseppesilva]

Hello,

I'm new here.

I'm trying to dev a API endpoint to random select a User ID.

This user have to be in a team.

[
  {
    "route": "/SortSeller/:teamId",
    "method": "get",
    "params": {
      "controller": "RandomSortSeller",
      "action": "randomize",
      "teamId": ":teamId"
    }
  }
]​

<?php

namespace Espo\Custom\Controllers;

class RandomSortSeller extends \Espo\Modules\Crm\Controllers\User
{
    public function getActionRandomize($params, $data, $request) {
        $teamId = $request->getRouteParam('teamId');

        // how can I get users inside this Team by Team ID?
    }
}    ​

My question is: how can I get users inside this Team by Team ID?

[alter]

Hi there, I believe something simple as this should work for you:

<?php
namespace Espo\Custom\Controllers;
class RandomSortSeller extends \Espo\Modules\Crm\Controllers\User
{
    private $entityManager;
    public function __construct(EntityManager $entityManager)
    {
        $this->entityManager = $entityManager;
    }
    public function getActionRandomize($params, $data, $request) {
        $teamId = $request->getRouteParam('teamId');

        $team = $this->entityManager->getEntity('team', $teamId);

        $users = $this->entityManager
            ->getRDBRepository('Team')
            ->getRelation($team, 'users')
            ->find();
    }
}​

[bandtank]

There is another way that makes one database call instead of two:

<?php

namespace Espo\Custom\Controllers;

use Espo\Core\Exceptions\BadRequest;
use Espo\Core\Exceptions\Error;
use Espo\Core\Api\Response;
use Espo\Core\Api\Request;

class TestController extends \Espo\Core\Templates\Controllers\Base
{
  public function getActionRandomize(Request $request, Response $response) {
    $params = $request->getQueryParams();

    $errors = array();
    if(!array_key_exists("team", $params) or !isset($params["team"]))
      $errors[] = "Missing or invalid parameter: team";

    # other error checking ...

    if(count($errors)) {
      $output = array(
        "status" => "Error",
        "errors" => $errors,
      );

    } else {
      $where = array(
        "teams.name" => $params["team"],
        "isActive" => True, # Optional
      );
      $select = array(
        "id",
        "name",
      );
      $users = $this->entityManager->getRDBRepository("User")
        ->join("teams")->select($select)->where($where)->find();

      $users = $users->toArray(); # Convert the EntityCollection to an array
      shuffle($users); # Randomize the array

      $output = array(
        "status" => "Success",
        "randomUser" => $users[0], # Return the first user
      );
    }

    $response->writeBody(json_encode($output));
  }
}​

{
  "status": "Success",
  "randomUser": {
    "id": "6197...",
    "name": "Person ...",
    "userName": "user@name.com"
  }
}​

This is a comprehensive solution that checks for errors, makes one database call, and uses a team's name instead of the ID, which is often the better way to do it. To use the team's ID, change the where statement to use teams.id instead of teams.name. I would not extend the User controller for this, but that's up to you.

[alter]

Hi, I believe your intention was good, but looking at the PHP code you provided against the one I provided (I am not saying my code is the best, or something like that), your approach to code readability is not great, I would say it's actually really bad in terms of sacrificing one call to DB. I would like to mention I am no PHP developer (I have a .NET developer/React/Java background), but look at both approaches and try to convince me, that the code you provided is better, because it is definitely not -> you separated such a simple request to a lot of code lines, which I found hard to read from a developer's perspective, not to mention it does not provide any additional value. From a developer's perspective, all you did was provided a code solution that makes the code harder to read without providing any valuable benefit, because one call to DB with a direct WHERE (which ->get($entityName, $entityId) surely provides) and second call to db, that gets the related table, will not make any performance issues at all -> it would cause a performance issues when the table would have (for example) 2000 fields/columns and you would fetch all of the columns, even when you want to use only one column. However, I do believe I could even wrap my code to a one DB call via Select Manager or how is it named, but even then, there would be no significant performance benefit/issue. What I want to mention here is that the readability of the code should be prefered instead of micro-premature optimization that will return no additional value. I would like to apologize for my kind of a rude reply.

[bandtank]

I decided to respond in case someone else comes by who wants to know why making a second, pointless database call is not irrelevant.

Hi, I believe your intention was good

It's more than an intention. It solves the problem whereas your solution does not.

but looking at the PHP code you provided against the one I provided (I am not saying my code is the best, or something like that)

Your code is overly simplistic and it does not implement the full solution to the problem. The function makes two database calls when only one is necessary. I agree that it is not the best.

in terms of sacrificing one call to DB

It's not a sacrifice to cut out the most expensive step in the algorithm. It's the opposite of a sacrifice. Doing the wrong thing faster is not acceptable.

I would like to mention I am no PHP developer

Yes, that is obvious.

you separated such a simple request to a lot of code lines, which I found hard to read from a developer's perspective

I solved the problem. You did not. The number of lines doesn't matter when the solution is incomplete.

not to mention it does not provide any additional value

Yes, it does. It solves the problem, checks for errors, and reduces resource usage.

all you did was provided a code solution that makes the code harder to read without providing any valuable benefit

This is not correct.

because one call to DB with a direct WHERE (which ->get($entityName, $entityId) surely provides) and second call to db

This is the reason all databases have JOIN statements - specifically to never do what you just said.

that gets the related table, will not make any performance issues at all -> it would cause a performance issues when the table would have (for example) 2000 fields/columns and you would fetch all of the columns, even when you want to use only one column.

So your argument is that bad code is only bad when it's really bad. Excellent argument. It's clear you have never been in charge of anything that requires optimization.

However, I do believe I could even wrap my code to a one DB call via Select Manager or how is it named, but even then, there would be no significant performance benefit/issue

Yes, there would be a benefit. It would reduce resource usage, which is literally the most important aspect of programming after you've solved the problem.

What I want to mention here is that the readability of the code should be prefered instead of micro-premature optimization

It's not micro-premature (that isn't a thing), but it is an optimization, so I agree with you there. Readability was not sacrificed and my code runs more than 3x faster than yours to actually solve the issue.

I would like to apologize for my kind of a rude reply.

No apology is necessary. You are clearly learning, which is fine.


Your code doesn't solve the problem, it doesn't check for errors, and it makes an expensive database call for no reason when a join accomplishes the same thing. Readability is not sacrificed here. That is not a good argument anyway. Using more resources to accomplish the same thing is always wrong.

[alter]

I'm not going to argue with you. Your code is badly implemented, it does not check for errors, and it makes an expensive database call for no reason. My code is more sustainable and readable. Use whichever one you want, but I am a professional developer and I know what is good and what isn't.

Well, the call to DB is not expensive, but it doesn't matter. I only tried to provide a code solution for what the user was asking for - which was " how can I get users inside this Team by Team ID?"

[bandtank]

Well, the call to DB is not expensive, but it doesn't matter. I only tried to provide a code solution for what the user was asking for - which was " how can I get users inside this Team by Team ID?"

I solved the whole problem and I did it in a more complete way that works better and faster. Writing bad code and thinking it's okay is how you end up with slow applications. Thankfully, EspoCRM is not implemented the way you are suggesting, which is why it is fast and works well.

Here are some examples from EspoCRM's repository that show how important optimization is compared to "readability" (it's all perfectly readable, but it doesn't meet your standard).

[alter]

Well, whatever works for you and your codebase related to EspoCRM. By reading your edited response, I will not respond anymore based on your responses

[bandtank]

Well, whatever works for you and your codebase related to EspoCRM. By reading your edited response, I will not respond anymore based on your responses

That's a good idea considering your solution does not implement the right interface. Reading the documentation is helpful.

[esforim]

Post as many solution as possible.

I and many people have no coding skill, so we heavily rely on simple/easy to copy/paste example from people like you two.

Thanks.

[telecastg]

Please note that as of Espo 7 the "correct" method calling is:

  
public function getActionRandomize(Request $request, Response $response) {
    $params = $request->getQueryParams();

   // code
}​

The "older" method still works but might be deprecated in future updates

public function getActionRandomize($params, $data, $request) {
    $teamId = $request->getRouteParam('teamId');

    // code
}​

This was part of the massive re-factorization of Espo 7.

[giuseppesilva]

Really thank you all.

Solved.

Know, I wanna add (in code) a INT field to User to set "Weight in Sales Distribution", with a value between 0 and 100. And I wanna add (in code) a CHECKBOX field to Teams to set "This teams use Weight of User".

Then, I'll select the values of each one and apply logic to distribute the leads.

With Layout Manager and Entities I've solved, but I wanna do it with code.

Anyone know how to do this in code?

[giuseppesilva]

There is another way that makes one database call instead of two:

<?php

namespace Espo\Custom\Controllers;

use Espo\Core\Exceptions\BadRequest;
use Espo\Core\Exceptions\Error;
use Espo\Core\Api\Response;
use Espo\Core\Api\Request;

class TestController extends \Espo\Core\Templates\Controllers\Base
{
public function getActionRandomize(Request $request, Response $response) {
$params = $request->getQueryParams();

$errors = array();
if(!array_key_exists("team", $params) or !isset($params["team"]))
$errors[] = "Missing or invalid parameter: team";

# other error checking ...

if(count($errors)) {
$output = array(
"status" => "Error",
"errors" => $errors,
);

} else {
$where = array(
"teams.name" => $params["team"],
"isActive" => True, # Optional
);
$select = array(
"id",
"name",
);
$users = $this->entityManager->getRDBRepository("User")
->join("teams")->select($select)->where($where)->find();

$users = $users->toArray(); # Convert the EntityCollection to an array
shuffle($users); # Randomize the array

$output = array(
"status" => "Success",
"randomUser" => $users[0], # Return the first user
);
}

$response->writeBody(json_encode($output));
}
}​

{
"status": "Success",
"randomUser": {
"id": "6197...",
"name": "Person ...",
"userName": "user@name.com"
}
}​

This is a comprehensive solution that checks for errors, makes one database call, and uses a team's name instead of the ID, which is often the better way to do it. To use the team's ID, change the where statement to use teams.id instead of teams.name. I would not extend the User controller for this, but that's up to you.

Hey guys, how are you?

I'm having some difficult to retrieve some fields of the 'teams' relation.

For example:

$select = array(
"id",
"name",
["teams.id", "teamsId"]
);​

Isn't working!

[telecastg]

$select is a single dimension array which elements will be transformed to a comma separated string that will be used by the ORM to define the fields that will be retieved by the SELECT query, so the reference to ["teams.id", "teamsId"] is not valid becasue there is no such thing as a field named ["teams.id", "teamsId"]

If you need to retrieve linked values use the ->getRelation directive.

I suggest that you read the ORM documentation https://docs.espocrm.com/development/orm/ to get a better idea of how to do database operations, trying to implement a solution by copying and modifying the posted code doesn't really work in most cases.

[giuseppesilva]

I've solved not the best way, but is working.

I've attached the controller file here. Feel free to use and improve.

Thanks.

[telecastg]

The best solution is the one that works for you Thank you very much for sharing. !