CODEWITHSUNDEEP
phplaravellaravel-query-builder
Jeremiah Fulbright
Jeremiah Fulbright

Reputation: 13

Laravel Query Builder returns no results while generated SQL works perfect

I am actively working on a laravel project and have ran into some issues with the Query Builder. I am trying to avoid using DB::Raw but it is looking like I may need to.

        $query = app($this->model());
        $query = $query->select(['last_name', 'first_name', 'birthday'])
                       ->distinct()
                       ->leftJoin('enrollments', 'students_meta.uid', '=', 'enrollments.student_uid')

        ->whereIn('enrollments.type', $types)
        ->where('enrollments.startdate', '<=', "'{$today}'")
        ->where(function ($join) use ($today) {
            $join->where('enrollments.dropdate', '>=', "'{$today}'")
                  ->orWhereNull('enrollments.dropdate');
        });

        // todo: add viewBy filter
        $query = $query->where('birth_month', '=', Carbon::today()->month);
        $query = $query->orderBy('last_name')->orderBy('first_name');
        $models = $query->get();

The above query builder generates the following SQL :

SELECT distinct `last_name`, `first_name`, `birthday` 
    FROM `students_meta` 
    LEFT JOIN `enrollments` ON `students_meta`.`uid` = `enrollments`.`student_uid` 
    WHERE `enrollments`.`type` IN ('ACTIVE', 'active') 
    AND `enrollments`.`startdate` <= '2019-10-29' 
    AND (`enrollments`.`dropdate` >= '2019-10-29' OR `enrollments`.`dropdate` IS NULL) 
    AND `birth_month` = 10 
ORDER BY `last_name` asc, `first_name` asc;

The generated SQL is perfect based on the old code I'm moving from and produces the expected results. If I move some things around, it seems I can get the query builder to return results, but they're not the correct ones. I've looked at other questions/answers about this kind of problem and tried multiple scenarios of moving the join/changing the where's around, still no luck.

Any suggestions? (other than taking the generated sql and running it in DB::Raw()

Upvotes: 0

Views: 1083

Answers (2)

aynber
aynber

Reputation: 23001

Remove your quotes from around $today. The third (or second, if you eliminate the comparison operator) parameter of the where clause sends the values as a parameter in a prepared statement . So 

 "'{$today}'"

would look like this in a straight query:

where enrollments.startdate <= "'2019-10-29'"

So change your query to 

 ->where('enrollments.startdate', '<=', $today)

Make sure you remove the quotes from all instances like this in your query.

Upvotes: 0

pr1nc3
pr1nc3

Reputation: 8338

$query = $query->orderBy('last_name')->orderBy('first_name')->get();

Your query worked fine but you didn't output it.

You can also use ->get()->toArray(); to retrieve the data in array format

Upvotes: 1

Related Questions