Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Qlic's avatar
Level 18

[L5.0] Bindings order is wrong

I have a query which seems to give a wrong order to the bindings:

$favoriteList = ProjectFavorite::select(DB::RAW('project_favorites.*'))
            ->join('users', 'project_favorites.user_id', '=', 'users.id')->where('users.type', 'student')
            ->join('students', 'project_favorites.user_id', '=', 'students.user_id');


if (($expertises = $request->input('expertise')) or $expertises === '0')
{
    $favoriteList->whereHas('user.student.expertise', function($q) use ($expertises)
    {
        $q->whereIn('id', $expertises);
    });
}

if ($pointsCriteria = array_filter($request->only(['pointsTotal', 'pointsAverage'])))
{
    $favoriteList->whereHas('user.correspondences.correspondenceDetail', function($q) use ($pointsCriteria)
    {
        if($pointsTotal = Arr::get($pointsCriteria, 'pointsTotal'))
        {
            $q->having(DB::raw('SUM(score)'), '>=', $pointsTotal);
        }

        if($pointsAverage = Arr::get($pointsCriteria, 'pointsAverage'))
        {
            $q->having(DB::raw('AVG(score)'), '>=', $pointsAverage);
        }
    });
}

if ($institutions = $request->input('institution'))
{
    $favoriteList->whereHas('user.student.institution', function($q) use ($institutions)
    {
        $q->whereIn('type', $institutions);
    });
}

if (($assignment = $request->input('assignment')) or $assignment === '0')
{
    $favoriteList->whereHas('user.projectCommunication', function($q) use ($assignment)
    {
        $q->where('project_id', $assignment);
    });
}

$favoriteList
    ->where('project_favorites.organization_id', Auth::user()->organization->id)
    ->where('project_favorites.author', '=', 1);

switch($order = $request->input('orderBy'))
{
    case '0':
    case '1':
        $sub = CorrespondenceDetail::select('user_id')
            ->join('correspondences', 'correspondences.id', '=', 'correspondence_details.correspondence_id')
            ->groupBy('correspondences.user_id');

        if($order === '0')
            $sub->selectRaw("SUM(score) AS score");

        if($order === '1')
            $sub->selectRaw("AVG(score) AS score");

        $favoriteList->leftJoin(DB::raw('(' . $sub->toSql() . ') AS sub'), 'sub.user_id', '=', 'users.id')
            ->mergeBindings($sub->getQuery());

        $favoriteList->orderBy('sub.score', 'DESC');
        break;

    case '2':
        $favoriteList->orderBy('students.lastName', 'ASC');
        break;

    case '3':
        $favoriteList->orderBy('students.lastName', 'DESC');
        break;

    case '4':
        $favoriteList->orderBy('');
        break;
}

$favoriteList = $favoriteList->paginate(2);

foreach ($request->all() as $filter => $value)
{
    $favoriteList->appends($filter, $value);
}

Dump screenshot: http://i60.tinypic.com/2iazf5w.jpg

As you can see, the "having" value is 10, however the query places this value in the wrong location of the query which we can see when 'crashing' the query:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users2.type' in 'where clause' (SQL: select count(*) as aggregate from `project_favorites` inner join `users` on `project_favorites`.`user_id` = `users`.`id` inner join `students` on `project_favorites`.`user_id` = `students`.`user_id` left join (select `user_id`, AVG(score) AS score from `correspondence_details` inner join `correspondences` on `correspondences`.`id` = `correspondence_details`.`correspondence_id` group by `correspondences`.`user_id`) AS sub on `sub`.`user_id` = `users`.`id` where `users2`.`type` = student and (select count(*) from `users` where `project_favorites`.`user_id` = `users`.`id` and (select count(*) from `correspondences` where `correspondences`.`user_id` = `users`.`id` and (select count(*) from `correspondence_details` where `correspondence_details`.`correspondence_id` = `correspondences`.`id` having SUM(score) >= 1) >= 1) >= 1) >= 1 and `project_favorites`.`organization_id` = 1 and `project_favorites`.`author` = 10)

Sum score should have value 10, and author should have value 1

0 likes
2 replies

Please or to participate in this conversation.