CODEWITHSUNDEEP
mysqllaravel
Suvin94
Suvin94

Reputation: 240

Laravel - Mysql get elements from join table

I am getting the total carOwnerDept(department id) from cars table which has relation with department table but i can't get the issue from cars table.

Here's the code

$dept = DB::table('department')
->select('department.name', DB::raw('COUNT(cars.carOwnerDept) AS dept_count'))
->join('cars', 'department.id', '=', 'cars.carOwnerDept')
->select('department.name', 'cars.issue')
->groupBy('department.name')
->where('cars.carType', '=', 'Internal Customer')
->whereDate('cars.created_at', '>=', $from)
->whereDate('cars.created_at', '<=', $to)
->get();

Views

<table>
  <tr>
    <th>Department</th>
    <th>Issues</th>
    <th>Total</th>
  </tr>
  @foreach($dept as $depts)
  <tr>
    <td>{{$depts->name}}</td>
    <td>{{$depts->issue}}</td>
    <td>{{$depts->dept_count}}</td>
  </tr>
  @endforeach
</table>

DB Structure

cars

id | carOwnerDept | issue
1 1 barcode problem
2 3 scanner problem
3 2 barcode problem

department

id | name
1 QA
2 PP
3 PE

Upvotes: 1

Views: 64

Answers (3)

AnkitK
AnkitK

Reputation: 418

You may get the number of car owner departments as shown below.

$dept = DB::table('department')
        ->select(DB::raw('(SELECT COUNT(carOwnerDept) FROM cars WHERE carOwnerDept=department.id) AS dept_count'))
        ->join('cars', 'department.id', '=', 'cars.carOwnerDept')
        ->select('department.name', 'cars.issue')
        ->where('cars.carType', '=', 'Internal Customer')
        ->whereDate('cars.created_at', '>=', $from)
        ->whereDate('cars.created_at', '<=', $to)
        ->get();

Upvotes: 1

VIKAS KATARIYA
VIKAS KATARIYA

Reputation: 6005

Try This.

$dept = DB::table('department')
->join('cars', 'department.id', '=', 'cars.carOwnerDept')
->select('department.name', DB::raw('COUNT(cars.carOwnerDept) AS dept_count'), 'cars.issue')
->where('cars.carType', '=', 'Internal Customer')
->whereDate('cars.created_at', '>=', $from)
->whereDate('cars.created_at', '<=', $to)
->groupBy('department.name')
->get();

Upvotes: 1

John Carlo
John Carlo

Reputation: 54

I highly recommend that you create functions on your models so that you don't need to input raw queries. Fewer codes and easier to understand.

Upvotes: 1

Related Questions