CODEWITHSUNDEEP
phpmysqllaravel
omrakhur
omrakhur

Reputation: 1402

How to write a dynamic number of Eloquent queries in Laravel 5?

(This question is building on my other question here)

There are four tables in my database:

  1. Users (columns id, dept_id)
  2. Departments (columns id, deptStringName)
  3. Absences (columns id, user_id, type_id)
  4. Absence_types (columns id, stringName)

At the moment there are 10 rows in the Departments table and 12 rows in the Absence_types table.

I'm trying to get a query that outputs ten tables, one for each department, with the types of absences and their counts next to the name, e.g., for the IT department:

+-----------+---+
| Sickness  | 4 |
| Paternity | 7 |
| Maternity | 3 |
| ...       | 6 |
+-----------+---+

I know the query to get these results. However, I'm wondering what is the best practice from a Software Engineering standpoint: do I hard-code the values in the WHERE clause as I've done (dept.id = 1) in the query below?

SELECT COUNT(abs.id) as AbsenceCount , absence_types.description FROM Absences abs JOIN Users u on u.id = abs.user_id JOIN Departments dept on dept.id = u.dept_id JOIN Absence_types on Absence_types.id = abs.type_id WHERE dept.id = 1 group by dept.description

Or do I use some other way to get the ID of a department? I can't think of a way in which I could write a Laravel script that would know how many departments there are and then run one query each per department.

EDIT: Example result could be like the one below (ideally), with A, B, C Absence Types for X, Y, Z Departments

+---+---+---+---+
|   | A | B | C |
+---+---+---+---+
| X | 4 | 8 | 5 |
| Y | 7 | 9 | 4 |
| Z | 5 |   |   |
+---+---+---+---+

Upvotes: 1

Views: 253

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31812

Try this way..

Initialize the table by getting all departments and all absence types:

$departments  = DB::table('Departments')->pluck('deptStringName');
$absenceTypes = DB::table('Absence_types')->pluck('stringName');

$row = [];
foreach ($absenceTypes as $absenceType) {
    $row[$absenceType] = 0;
}

$table = [];
foreach ($departments as $department) {
    $table[$department] = $row;
}

This should create a 2D array like:

[
    'X' => [
        'A' => 0,
        'B' => 0,
        'C' => 0,
    ],
    // ...
]

You can use NULL or an empty string instead of 0 if you like.

Now modify your query a bit. You need to group by departments and absence types.

$data = DB::select('
    SELECT 
        d.deptStringName as department,
        t.stringName as absenceType,
        COUNT(a.id) as absenceCount
    FROM Absences a
    JOIN Users u         ON u.id = a.user_id
    JOIN Departments d   ON d.id = u.dept_id
    JOIN Absence_types t ON t.id = a.type_id
    GROUP BY d.deptStringName, t.stringName
');

And fill the $table with values fron the query:

foreach ($data as $row) {
    $table[$row->department][$row->absenceType] = $row->absenceCount
}

Now the $table shlould look like

[
    'X' => [
        'A' => 4,
        'B' => 8,
        'C' => 5,
    ],
    'Y' => [
        'A' => 7,
        'B' => 9,
        'C' => 4,
    ],
    'Z' => [
        'A' => 5,
        'B' => 0,
        'C' => 0,
    ],
]

Upvotes: 1

Related Questions