API rules: `@request.data.*` for multiple fields that relate to the same collection not working

[impact-merlinzerbe]

I use PocketBase v0.22.2.

Consider the default users collection (with a name field) and a messages collection with fields to and from (both single relation to users).

I use both fields in my API rules, e.g. like this:

// create rule
@request.data.from.name = "peter" &&
@request.data.to.name = "john"

The generated SQL is this:

SELECT DISTINCT `messages`.*
FROM `messages`
LEFT JOIN `users` `__data_users` ON [[__data_users.id]]='ogmutrnuma7bv1a'
WHERE (`messages`.`id`='aevlecoxzh3ryf4')
  AND (([[__data_users.name]] = 'peter'
        AND [[__data_users.name]] = 'john'))
LIMIT 1

The request fails because of the single JOIN on __data_users. In the in create rule I can work around this by not using the @request.data. syntax, although I find it more explicit:

// create rule
from.name = "peter" &&
to.name = "john"

The corresponding (working) SQL is:

SELECT DISTINCT `messages`.*
FROM `messages`
LEFT JOIN `users` `messages_from` ON [[messages_from.id]] = [[messages.from]]
LEFT JOIN `users` `messages_to` ON [[messages_to.id]] = [[messages.to]]
WHERE (`messages`.`id`='n39xgd5qao30k42')
  AND (([[messages_from.name]] = 'peter'
        AND [[messages_to.name]] = 'john'))
LIMIT 1

However, in the update rule I need to use the @request.data. syntax because I want to filter on the sent data, not the current record data:

@request.data.from.name = "john" &&
@request.data.to.name = "peter"

Similar to the first create rule SQL, this always fails:

SELECT DISTINCT `messages`.*
FROM `messages`
LEFT JOIN `users` `__data_users` ON [[__data_users.id]]='ch7r7vuimaaikyu'
WHERE (`messages`.`id`='n39xgd5qao30k42')
        AND [[__data_users.name]] = 'john'
        AND [[__data_users.name]] = 'peter'))
LIMIT 1

How can I filter on the @request.data.* fields in the API rules if multiple fields relate to the same collection?

[ganigeorgiev]

This is a bug with @request.data.* relations resolver. I'll submit a fix sometime later today.

[ganigeorgiev]

I've pushed a fix in master and it will be available with the next minor v0.22.3 release a little bit later.

The tests were also updated to cover this use case so it should work correctly now.

[impact-merlinzerbe]

Awesome, thanks for the fixing this so fast.