数据库结果为两级菜单

Question

This is result I get from database (mysql). Now I would like to get menu with structure like

department title1
- project1
- project2
department title2
- project3
- project4
....

This are my results

project title   |   department title    | department_id 
 project1       |   department1         |   9
 project2       |   department2         |   2
 project3       |   department3         |   1
 project11      |   department1         |   9
 project23      |   department2         |   2
 project24      |   department3         |   1
 project15      |   department1         |   9
 project26      |   department2         |   2
 project21      |   department4         |   4

I'm using laravel 4.2.(and this will be in blade syntax) if this is any help.

Here's what I tried so far: In my controller

//projects is above results from db query builder
 foreach($projects as $project)
         {
            if($project->department_id != "")
            {
              $department = array(
               "id" => $project->department_id,
               "title" => $project->department_title, 
              );
              $departments_projects[] = array_push($departments_projects,$department);
            }
         }

And then in my view (where I'm build the menu) I have this code

 <ul class="sub-menu">
            @foreach($departments as $department)
            <li class="">
              <a href="javascript:;">
                {{ $department['title'] }} <i class="icon-arrow"></i>
              </a>
              <ul class="sub-menu">
                @foreach($projects as $project)
                  @if($project->department_id == $department['id'] && $project->title != "")
                    <li>
                      <a href="{{ route('department.project.show',array($project->department_id,$project->id)) }}">
                        {{ $project->title }}
                      </a>
                    </li>
                  @endif
                @endforeach

              </ul>
            </li>
            @endforeach
          </ul>

Answer 1

Maybe it would be better to solve this in MySQL end, but if you want to do it after you retrieve the result, you can try something along these lines:

The solution works under the premise that you have a result in the following structure:

$projects = [
    [
        'project_title' => 'project2',
        'department_title' => 'department2',
        'department_id' => 2
    ],
    [
        'project_title' => 'project1',
        'department_title' => 'department1',
        'department_id' => 9
    ],
    [
        'project_title' => 'project3',
        'department_title' => 'department3',
        'department_id' => 1
    ],
    [
        'project_title' => 'project11',
        'department_title' => 'department1',
        'department_id' => 9
    ],
    [
        'project_title' => 'project13',
        'department_title' => 'department2',
        'department_id' => 2
    ],
];

We initially get a unique array of department titles and we sort it alphabetically. Then, we create a $departments array which has the department titles as keys and includes the structure that we'll use.

Then, we iterate through the $projects array and we fill the details in the appropriate fields of the $departments array.

Finally, we sort the projects alphabetically as well.

$department_names = array_unique(array_column($projects, 'department_title'));
sort($department_names);

$departments = array_fill_keys($department_names, [
    'id' => null,
    'projects' => []
]);

foreach($projects as $project) {
    $departments[$project['department_title']]['id'] = $project['department_id'];
    $departments[$project['department_title']]['projects'][] = $project['project_title'];
}

foreach($departments as $key => $department) {
    sort($departments[$key]['projects']);
}

var_dump($departments);

That will produce:

array(3) {
  ["department1"]=>
  array(2) {
    ["id"]=>
    int(9)
    ["projects"]=>
    array(2) {
      [0]=>
      string(8) "project1"
      [1]=>
      string(9) "project11"
    }
  }
  ["department2"]=>
  array(2) {
    ["id"]=>
    int(2)
    ["projects"]=>
    array(2) {
      [0]=>
      string(9) "project13"
      [1]=>
      string(8) "project2"
    }
  }
  ["department3"]=>
  array(2) {
    ["id"]=>
    int(1)
    ["projects"]=>
    array(1) {
      [0]=>
      string(8) "project3"
    }
  }
}

You can use the $departments array to produce something similar to the following:

<ul>
<?php foreach($departments as $key => $department) { ?>
    <li>
        <?php echo $key; ?>
        <ul>
            <?php foreach($department['projects'] as $project) { ?>
            <li><?php echo $project; ?></li>
            <?php } ?>
        </ul>
    </li>
<?php } ?>
</ul>

An alternative is to use the department IDs as keys and then sort the array alphabetically based on the title value.

$department_ids = array_unique(array_column($projects, 'department_id'));

$departments = array_fill_keys($department_ids, [
    'title' => null,
    'projects' => []
]);

foreach($projects as $project) {
    $departments[$project['department_id']]['title'] = $project['department_title'];
    $departments[$project['department_id']]['projects'][] = $project['project_title'];
}

foreach($departments as $key => $department) {
    sort($departments[$key]['projects']);
}

uasort($departments, function ($x, $y) {
    return strcasecmp($x['title'], $y['title']);
});

var_dump($departments);

That will produce:

array(3) {
  [9]=>
  array(2) {
    ["title"]=>
    string(11) "department1"
    ["projects"]=>
    array(2) {
      [0]=>
      string(8) "project1"
      [1]=>
      string(9) "project11"
    }
  }
  [2]=>
  array(2) {
    ["title"]=>
    string(11) "department2"
    ["projects"]=>
    array(2) {
      [0]=>
      string(9) "project13"
      [1]=>
      string(8) "project2"
    }
  }
  [1]=>
  array(2) {
    ["title"]=>
    string(11) "department3"
    ["projects"]=>
    array(1) {
      [0]=>
      string(8) "project3"
    }
  }
}