Eloquent ORM resulting MySQL select not returning all rows

Question

I am using Laravel's Eloquent ORM to query a database for infomation, but for some reason the resulting query isn't returning all of the rows.

Eloquent Query:

\Permit::selectRaw('CONCAT(lk_departments.dept_id, ".") AS dept_id, '. 
                   'lk_departments.dept_name, '.
                   'COUNT(DISTINCT permits.permit_number) AS total')
       ->leftJoin('lk_departments', 'permits.dept_id', '=', 'lk_departments.dept_id')
       ->where('permits.permit_number', '!=', 'N/A')
       ->whereBetween('permits.valid_date', [$query_info['start_date'], $query_info['end_date']])
       ->groupBy('lk_departments.dept_name')
       ->orderBy('lk_departments.dept_name', 'ASC')
       ->get()

Resulting Query:

SELECT 
CONCAT(lk_departments.dept_id, ".") AS dept_id, lk_departments.dept_name,
COUNT(DISTINCT permits.permit_number) AS total
FROM permits
LEFT JOIN lk_departments ON permits.dept_id = lk_departments.dept_id
WHERE permits.valid_date BETWEEN '01/01/2000' AND '12/12/2100'
GROUP BY lk_departments.dept_name
ORDER BY lk_departments.dept_name ASC

Returned Data:

dept_id             dept_name                       total
+--------------------------------------------------------+
| NULL   NULL                                       0    | 
| 1.     Academic Achievement-(Special Services)    11   | 
| 3.     Academic Clubs                             1    | 
| 4.     Academic Custodial & Grounds Services      1    | 
| 5.     Accounting                                 2    | 
| 6.     Admissions                                 356  | 
| 7.     Advanced Programs Dept (Education Dept)    8    | 
| 9.     Aerospace Studies - Air Force ROTC         1    | 
| 11.    American Federation of Teachers            0    | 
| 13.    Anthropology                               1    | 
| 14.    Art Dept.                                  4    | 
| 17.    Athletic Dept.                             11   | 
| 21.    Biology Dept.                              2    | 
|                                                        |
| ...    ...                                        ...  |
+--------------------------------------------------------+

I referenced this question to modify total: Count distinct values

but I still receive one NULL row with a total of 0 and missing records. I'm not very good with MySQL, so forgive me if I am missing something. Ideally, I would like a count of permits from each department for a given time frame.

NOTE:
dept_id = auto incremented primary key
permits = table containing parking permit information that is linked to a department

Would adding a where dept_id IS NOT NULL not omit that unwanted row? — tadman
For some reason, adding dept_id IS NOT NULL returns an empty set. — user3562712
That's especially odd. As a note you should avoid doing presentation-type things in your query, such as appending . to the number, instead save that for when it's shown in whatever view it's ending up in. — tadman

Unknown Unknown · Accepted Answer · 2014-09-12T19:06:50

I finally figured out the problem. When I was using permits as the master table in the join, I neglected to realize that the records for dept_id wouldn't show up if there were no corresponding records from permits.dept_id that matched lk_departments.dept_id (if nobody had registered a permit for that department, in other words). Also, for some reason, I had to change the query to include records where permits.permit_number is null.

Resulting Eloquent Query:

LKDepartment::selectRaw('lk_departments.*, COUNT(permits.permit_number) AS total')
            ->leftJoin('permits', 'lk_departments.dept_id', '=', 'permits.dept_id')
            ->whereNull('permits.permit_number')
            ->orWhereRaw("(permits.permit_number != 'N/A' AND permits.valid_date BETWEEN '$query_info[start_date]' AND '$query_info[end_date]')")
            ->groupBy('lk_departments.dept_id')
            ->orderBy('lk_departments.dept_id', 'ASC')
            ->get()

Resulting Query:

SELECT lk_departments.* , COUNT( permits.permit_number ) AS total
FROM lk_departments
LEFT JOIN permits ON lk_departments.dept_id = permits.dept_id
WHERE permits.permit_number IS NULL
OR (
    permits.permit_number != 'N/A' 
    AND permits.valid_date BETWEEN '01/01/2000' AND '12/12/2100'
)
GROUP BY lk_departments.dept_id
ORDER BY lk_departments.dept_id ASC

Returned Data:

dept_id             dept_name                       total
+--------------------------------------------------------+
| NULL   NULL                                       0    | 
| 1      Academic Achievement-(Special Services)    11   | 
| 2      Academic Advising Center                   0    | 
| 3      Academic Clubs                             1    | 
| 4      Academic Custodial & Grounds Services      1    | 
| 5      Accounting                                 2    | 
| 6      Admissions                                 356  | 
| 7      Advanced Programs Dept (Education Dept)    8    | 
| 8      Advisory Council                           0    | 
| 9      Aerospace Studies - Air Force ROTC         1    | 
| 10     Alternative School                         0    | 
| 11     American Federation of Teachers            0    | 
| 12     Anthropology                               0    | 
|                                                        |
| ...    ...                                        ...  |
+--------------------------------------------------------+

Eloquent ORM resulting MySQL select not returning all rows

2 Answers