one-of-many relationship is not working if primary key is UUID (i.e Not Sortable).

[pushpak1300]

• Laravel Version: 8.48.2
• PHP Version: 8.0.7
• Database Driver & Version: Postgres

Description:

It seems that the new One of Many relationships from #37362 does not work well when the primary key is UUID (i.e not sortable)

Steps To Reproduce:

public function latestSubmission()
    {
        return $this->hasOne(Submission::class)->ofMany('created_at', 'max');
    }

When UUID is used for primary keys, fetching this relationship on the model I'm getting the following error.

Illuminate\Database\QueryException with message 'SQLSTATE[42883]: Undefined function: 7 ERROR:  function max(uuid) does not exist
LINE 1: select * from "submissions" inner join (select MAX(id) as id...
                                                       ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts. (SQL: select * from "submissions" inner join (select MAX(id) as id, "submissions"."assignment_id" from "submissions" inner join (select MAX(created_at) as created_at, "submissions"."assignment_id" from "submissions" where "submissions"."assignment_id" in (919ad545-44cfd2-bfd7-598c44761d) and "submissions"."deleted_at" is null group by "submissions"."assignment_id") as "latestSubmission" on "latestSubmission"."created_at" = "submissions"."created_at" and "latestSubmission"."assignment_id" = "submissions"."assignment_id" where "submissions"."deleted_at" is null group by "submissions"."assignment_id") as "latestSubmission" on "latestSubmission"."id" = "submissions"."id" and "latestSubmission"."assignment_id" = "submissions"."assignment_id" where "submissions"."deleted_at" is null)'

[driesvints]

Heya, thanks for reporting.

I'll need more info and/or code to debug this further. Can you please create a repository with the command below, commit the code that reproduces the issue and share the repository here? Please make sure that you have the latest version of the Laravel installer in order to run this command. Please also make sure you have both Git & the GitHub CLI tool properly set up.

laravel new bug-report --github="--public"

Please do not amend and create a separate commit with your custom changes. After you've posted the repository, I'll try to reproduce the issue.

Thanks!

Ping @cbl

[cbl]

see: #37362 (comment)

Regarding UUIDs, they are sortable by using (string) Str::orderedUuid() as your model UUIDs instead of the typical (string) Str::uuid().

[pushpak1300]

@cbl I'm using Str::orderedUuid() only as a model UUID.
I checked the thread and it's not working for postgres version 12.
Has anyone found the issue or workaround over here ?

[pushpak1300]

@driesvints @cbl @taylorotwell
you can check the bug in the following repository https://github.com/pushpak1300/bug-report
I have mentioned the steps to reproduce the bug in the readme.
Please use the mentioned postgres SQL version for it.

[driesvints]

Thanks @pushpak1300, that was helpful. @cbl it indeed doesn't seems to be possible to use one-of-many in combination with UUID's and PostgreSQL. PostgreSQL simply lacks support to calculate the max UUID. I've sent in a note to the docs to document this: laravel/docs#7170

@pushpak1300 there does seems to be a workaround by implementing a custom function: https://gist.github.com/devodo/8b39748d65e8185fbd89

[pushpak1300]

Thanks, @driesvints

[nikans]

I'm using "Advanced" method to fetch one-of-many (from Postgres with UUIDs).
I hoped that I'll be off the hook, but for some reason, Eloquent still wants to query IDs even if I fetch hasOne this way:

return $this->hasOne('App\Models\Passport', 'person_id')
      ->ofMany([
          'date_expires' => 'max'
      ], function ($query) {
          $query->where('date_expires', '>', now());
      });

@driesvints so I believe the only property that should be queried here is date_expires.

[pushpak1300]

@nikans its not like that.
@cbl has write the blog for implementation for one of many.
it will use id's to query the records

[cbl]

@nikans date_expires is not a unique attribute, there might be rows with the same value for that attribute. Those duplicates are filtered by using a unique key on them.

[joowdx]

@cbl is there a way we can somehow override the max(id)? Like casting the unique key as string first before calling the function? max(id::text)