Using expressions in pivot queries

[Jigsaw5279]

Laravel Version

10.48.3

PHP Version

8.3.3

Database Driver & Version

mysqlnd 8.3.3

Description

This issue is similar to #49894, but for querying pivot relations with raw expressions.

In Laravel 9 it was possible to do this:

$item->someMorphToMany()
     ->wherePivotNotIn(
                DB::raw("CONCAT(some_field, '_', other_field)"),
                ['a_b', 'c_d']
            )->detach();

which would produce a where statement like CONCAT(some_field, '_', other_field) not in (?, ?) which is to be expected.

With the changes of enforcing manual casting of the expression to its raw value, I can no longer get wherePivotNotIn to produce the expected output.

$item->someMorphToMany()
     ->wherePivotNotIn(
                DB::raw("CONCAT(some_field, '_', other_field)")->getValue(DB::connection()->getQueryGrammar()),
                ['a_b', 'c_d']
            )->detach();

now produces the following where clause

`database`.`pivot_table`.`CONCAT(some_field, '_', other_field)` not in (?, ?)`

which is correctly identified by mysql as an unknown field.

Following the callgraph, I can get rid of the database and table prefix, by prefixing the concatenated fields myself (see https://github.com/laravel/framework/blob/10.x/src/Illuminate/Database/Eloquent/Relations/BelongsToMany.php#L1527 ).

But then somehwhere in the wrap function it gets mangled again:

$item->someMorphToMany()
     ->wherePivotNotIn(
                DB::raw("CONCAT(table.some_field, '_', table.other_field)")->getValue(DB::connection()->getQueryGrammar()),
                ['a_b', 'c_d']
            )->detach();

`CONCAT(table`.`some_field, '_', table`.`other_field)` not in (?, ?)

I'm not confident enough to fiddle around in the guts of eloquent to create a PR for this, so any help would be greatly appreciated

Steps To Reproduce

1. Setup a morphToMany relationship
2. Try to query the relationship with a raw expression constraint to the pivot

[driesvints]

@tpetry is this something we need to fix as well?

[tpetry]

Yeah, special handling in the core is required to unwrap expressions in this case. But due to personal stuff, I cant work on this currently.

[driesvints]

No worries at all @tpetry. Will leave this one open for anyone to pick up.

[github-actions]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[plumthedev]

Thanks @Jigsaw5279 for reporting that, I have fixed that in #50849
However, I see that your issue is related to Laravel 10.x and I made MR to 11.x according to Which branch - is it okay @driesvints?