Weird eloquent bindings when using addSelect method, Bindings are messing with different conditions.

Erth0 commented 4 years ago

Laravel Version: 7.13.0
PHP Version: 7.4.6
Database Driver & Version: Mysql & 8.0.20

Description:

There is a weird behaviour when i tried to use addSelect method on a database model. The bindings looks to have messed up and they are going on different parts of the query.

This is the eloquent query

$jobs = Postedjobs::with('skills')
        ->addSelect([
            'has_applied_for_job' => function ($query) {
                $query->selectRaw("job_proposals.proposal_id as has_applied_for_job")
                    ->from('job_proposals')
                    ->whereColumn('job_proposals.job_id', 'postedjobs.job_id')
                    ->where(function ($subquery) {
                        $subquery->where(function ($q) {
                            $q->where('proposable_type', User::class)->where('proposable_id', auth()->user()->id);
                        })
                        ->orWhere(function ($q) {
                            $myTeams = auth()->user()->ownTeams()->select('teams.team_id')->get();
                            $q->where('proposable_type', Team::class)->whereIn('proposable_id', $myTeams->toArray());
                        });
                    })
                    ->limit(1);
            }
        ])
        ->whereHas('user')
        ->whereDoesntHave('proposals', function ($query) {
            return $query->where('is_accepted', 'Y');
        })
        ->where('job_moderation', '2')
        ->where('available_until', '>', \Carbon\Carbon::now()->format('Y-m-d'))
        ->whereIn('job_status', ['1', '3'])
        ->where('job_action', '1')
        ->filter($request)
        ->get();

And this one is the raw query produced

SELECT
    `postedjobs`.*,
    (
    SELECT
        COUNT(*)
    FROM
        `job_proposals`
    WHERE
        `postedjobs`.`job_id` = `job_proposals`.`job_id`
) AS `proposals_count`,
(
    SELECT
        COUNT(*)
    FROM
        `user_chats`
    WHERE
        `postedjobs`.`job_id` = `user_chats`.`job_id`
) AS `messages_count`,
(
    SELECT
        job_proposals.proposal_id AS has_applied_for_job
    FROM
        `job_proposals`
    WHERE
        `job_proposals`.`job_id` = `postedjobs`.`job_id` AND(
            (
                `proposable_type` = 'App\\User' AND `proposable_id` = 4
            ) OR(
                `proposable_type` = 'App\\Models\\Team\\Team' AND `proposable_id` IN(1)
            )
        )
    LIMIT 1
) AS `has_applied_for_job`
FROM
    `postedjobs`
WHERE EXISTS
    (
    SELECT
        *
    FROM
        `users`
    WHERE
        `postedjobs`.`user_id` = `users`.`id`
) AND NOT EXISTS(
    SELECT
        *
    FROM
        `job_proposals`
    WHERE
        `postedjobs`.`job_id` = `job_proposals`.`job_id` AND `is_accepted` = 4
) AND `job_moderation` = 1 AND `available_until` > 'Y' AND `job_status` IN(2, '2020-06-07') AND `job_action` = 1

This is the query without the `addSelect` method

$jobs = Postedjobs::with('skills')
        ->whereHas('user')
        ->whereDoesntHave('proposals', function ($query) {
            return $query->where('is_accepted', 'Y');
        })
        ->where('job_moderation', '2')
        ->where('available_until', '>', \Carbon\Carbon::now()->format('Y-m-d'))
        ->whereIn('job_status', ['1', '3'])
        ->where('job_action', '1')
        ->filter($request)
        ->get();

SELECT
    `postedjobs`.*,
    (
    SELECT
        COUNT(*)
    FROM
        `job_proposals`
    WHERE
        `postedjobs`.`job_id` = `job_proposals`.`job_id`
) AS `proposals_count`,
(
    SELECT
        COUNT(*)
    FROM
        `user_chats`
    WHERE
        `postedjobs`.`job_id` = `user_chats`.`job_id`
) AS `messages_count`
FROM
    `postedjobs`
WHERE EXISTS
    (
    SELECT
        *
    FROM
        `users`
    WHERE
        `postedjobs`.`user_id` = `users`.`id`
) AND NOT EXISTS(
    SELECT
        *
    FROM
        `job_proposals`
    WHERE
        `postedjobs`.`job_id` = `job_proposals`.`job_id` AND `is_accepted` = 'Y'
) AND `job_moderation` = 2 AND `available_until` > '2020-06-07' AND `job_status` IN(1, 3) AND `job_action` = 1

As we can see above it looks like job_moderation should be 2 instead of 1 and available_until should be the date which in this case has jumped to job_status, is_accepted should be Y and job_status should be [1, 3]

Let me know if more information is needed, this is my first issue posting so sorry if it's not formatted correctly will be more than happy to help with more information!

staudenmeir commented 4 years ago

Please provide a minimal, reproducible example.

driesvints commented 4 years ago

Hey there,

Can you first please try one of the support channels below? If you can actually identify this as a bug, feel free to report back and I'll gladly help you out and re-open this issue.

Thanks!

laravel / framework