Laravel Query Builder returns no results while generated SQL works perfect

Question

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()

Take all of the quotes out of your $today where's, it's probably messing everything up. Using the query builder uses prepared statements/parameters, so your date string becomes something akin to enrollments.dropdate >= "'2019-10-29'" — aynber
– aynber, Commented Oct 30, 2019 at 16:13
Try do debug the resulted query: dd($query->toSql() also check if parameters are okay (mainly the month) — ka_lin
– ka_lin, Commented Oct 30, 2019 at 16:26
@aynber I will check that! I added them because for toSql() they needed to be there to work properly in the actual sql. — Jeremiah Fulbright
– Jeremiah Fulbright, Commented Oct 30, 2019 at 20:14
@aynber If you'll submit an official answer, I'll accept it. That did it since toSql() was what i was basing those changes on, but the query builder doesn't need — Jeremiah Fulbright
– Jeremiah Fulbright, Commented Oct 30, 2019 at 20:22

marc_s · Accepted Answer · 2019-10-30 16:17:04Z

1

$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

edited Oct 30, 2019 at 16:17

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 30, 2019 at 16:11

pr1nc3

8,3763 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeremiah Fulbright Over a year ago

Actually I forgot to copy the get() line over to here, will update

aynber · Accepted Answer · 2019-10-30 20:32:14Z

0

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.

answered Oct 30, 2019 at 20:32

aynber

23.2k9 gold badges57 silver badges69 bronze badges

Collectives™ on Stack Overflow

Laravel Query Builder returns no results while generated SQL works perfect

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related