laravel / framework

The Laravel Framework.
https://laravel.com
MIT License
32.22k stars 10.91k forks source link

Using expressions in pivot queries #50787

Closed Jigsaw5279 closed 5 months ago

Jigsaw5279 commented 6 months ago

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 commented 5 months ago

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

tpetry commented 5 months ago

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 commented 5 months ago

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

github-actions[bot] commented 5 months ago

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 commented 5 months ago

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?