Group descendants by position

[whoami15]

Hi everyone, posting this discussion to seek help.

I'm trying to efficiently retrieve the descendants of the current logged-in user. While I have managed to fetch the descendants using the following code:

$accountId = $request->user()->id;

$descendants = User::find($accountId)
    ->descendants()
    ->depthFirst()
    ->get();

The query above returns data as follows:

[
  {
    "id": 3,
    "parent_id": 2,
    "name": "bill",
    "position": "left",
    "depth": 1,
    "path": "3"
  },
  {
    "id": 4,
    "parent_id": 3,
    "name": "manuel",
    "position": "left",
    "depth": 2,
    "path": "3.4"
  },
  {
    "id": 16,
    "parent_id": 2,
    "name": "eddie76",
    "position": "right",
    "depth": 1,
    "path": "16"
  },
  {
    "id": 17,
    "parent_id": 16,
    "name": "hintz",
    "position": "left",
    "depth": 2,
    "path": "16.17"
  }
]

I'm looking to group these descendants by their position (left and right), but I want to start the grouping with bill - left and eddie76 - right, as these two accounts are positioned as the left and right children of the logged-in user ($accountId).

The desired outcome should be as follows:

{
  "left": [
    {
      "id": 3,
      "parent_id": 2,
      "name": "bill",
      "position": "left",
      "depth": 1,
      "path": "3"
    },
    {
      "id": 4,
      "parent_id": 3,
      "name": "manuel",
      "position": "left",
      "depth": 2,
      "path": "3.4"
    }
  ],
  "right": [
    {
      "id": 16,
      "parent_id": 2,
      "name": "eddie76",
      "position": "right",
      "depth": 1,
      "path": "16"
    },
    {
      "id": 17,
      "parent_id": 16,
      "name": "hintz",
      "position": "left",
      "depth": 2,
      "path": "16.17"
    }
  ]
}

[staudenmeir]

Hi @whoami15,
Do you mean something like $descendants->groupBy('position')?

[whoami15]

Hi @staudenmeir

Yeah, I added that but I'm getting an error.

$descendants = Account::find($accountId)
    ->descendants()
    ->groupBy('position')
    ->depthFirst()
    ->get();

Error

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'laravel_cte.id'

So what I did was add it after the ->get()

$descendants = Account::find($accountId)
    ->descendants()
    ->groupBy('position')
    ->depthFirst()
    ->get();
    
$groupedDescendants = $descendants->groupBy('position');

The problem with this is that "name": "hintz", is now in the left group, which makes total sense because her position is left.

But what I want is to show her in the right group because her parent ("name": "eddie76") is in the right position of the root account.

I guess I need to get the Level 1 left and right of the root account first.

[staudenmeir]

Now I see the issue.

You can group them by the first segment of their path:

$descendants = Account::find($accountId)
    ->descendants()
    ->depthFirst()
    ->get();

$leftChild = $descendants->where('position', 'left')->where('depth', 1)->first();

$groupedDescendants = $descendants->groupBy(
    fn($item) => explode('.', $item->path)[0] == $leftChild->id ? 'left' : 'right'
);

[whoami15]

Wow! didn't think of it this short! Thanks, @staudenmeir