John Davidson

Message:

$result = DB::table('students')

        ->join('cities', 'cities.id', '=', 'students.city_id' )

        ->select('cities.city_name as CityStudent', DB::raw('COUNT(students.id) as total'))

        ->groupBy('cities.id')->get();



view blade :



@foreach($result as $r)

<tr>

       <td>{{ $r->CityStudent }}</td>

       <td>{{ $r->total }}</td>

</tr>

@endforeach

I want to display the number of cities. And display the name of the city in the data.
Why does the code error and it doesn't work?

---

My Answer:
>join('courses', 'students.course_id', '=', 'courses.id')
->select('students.id', 'students.name', 'courses.name as course_name')
->groupBy('students.id')
->get();

This code will join the 'students' table with the 'courses' table based on the 'course_id' column in the 'students' table and the 'id' column in the 'courses' table. It will then select the 'id' and 'name' columns from the 'students' table and the 'name' column from the 'courses' table, aliasing it as 'course_name'. Finally, it will group the results by the 'id' column of the 'students' table.

Make sure that the 'students' table has a 'course_id' column and the 'courses' table has an 'id' column that corresponds to the 'course_id' values in the 'students' table.