$filter navigation entity targets the main entity

[Angelinsky7]

Context

class Person {

  #[LodataRelationship]
    public function link()
    {
        return $this->belongsTo(Link::class, 'link_id', 'id' );
    }

  #[LodataRelationship]
    public function tags(){
        return $this->belongsToMany(Tag::class);
    }
}

class Tag {
}

class Link {
}

Db

Table person{id, link_id}
Table tags{id}
Table link{id}
Table person_tag{person_id,tag_id}

Request
http://localhost:8080/odata/Person?$filter=link/id eq 1

Result
The result is the same with this request http://localhost:8080/odata/Person?$filter=id eq 1 the request filter only the id of the main entity not the id of the navigation entity. (we can see that this information is lost function generateWhere() of trait SQLWhere.
If we try to filter a property of the navigation link we have an error because the property does not exit. (For example, if you $filter on link/prop01 and person don't have a prop01 property in the database there will be an error :

{
    "@context": "http://localhost:8080/odata/$metadata#person(link())",
    "value": [OData-error: {
            "code": "expression_parser_error",
            "message": "Encountered an invalid symbol at: link/>p<rop01 eq 3"",
            "target": null,
            "details": [],
            "innererror": {}
        }

Expected result
We should be able to filter entities with navigation link as in https://docs.oasis-open.org/odata/odata/v4.01/os/part1-protocol/odata-v4.01-os-part1-protocol.html#sec_BuiltinFilterOperations with the first example :
Address/City eq 'Redmond'
and have a list of all person that have a link with the target property filtered.

Version
"flat3/lodata": "^5.11"

[Angelinsky7]

It seems that all the filters have the same kind of issue.
I tried with $filter=contains(link/values, '?') but it's the same issue.
At some point the parser should not target the main entitySet but the entitySet of the navigation property :

lodata/src/Expression/Parser.php

Lines 623 to 656 in 7f9ce1c

	public function tokenizeNavigationPropertyPath(): bool
	{
	$currentResource = $this->getCurrentResource();
	if (!$currentResource) {
	return false;
	}
	$navigationProperties = $currentResource->getType()->getNavigationProperties();
	$token = $this->lexer->with(function () {
	$identifier = $this->lexer->identifier();
	$this->lexer->char(Lexer::pathSeparator);
	return $identifier;
	});
	if (!$token) {
	return false;
	}
	$property = $navigationProperties->get($token);
	if (!$property) {
	return false;
	}
	$operand = new Node\Property\Navigation($this);
	$operand->setValue($property);
	$this->operandStack[] = $operand;
	$this->tokens[] = $operand;
	return true;
	}

does not change the current context and then

lodata/src/Expression/Parser.php

Line 754 in 7f9ce1c

$currentResource = $this->getCurrentResource();

fail to find the correct property

[Angelinsky7]

For some context here's the sql query generated by asp.net core odata for the kinda same request

http://localhost:5078/odata/Persons?$expand=Kind,Tags&$filter=Kind/Type eq 'E'

SELECT 

    [p].[Id], 
    [p].[first_name], 
    [p].[LinkdId], 
    [p].[last_name], 
    [p0].[Id], 
    [p0].[Information], 
    [p0].[type], 
    [t0].[Id], 
    [t0].[value], 
    [t0].[PersonsId], 
    [t0].[TagsId], 
    CAST(0 AS bit)

FROM [persons] AS [p]
INNER JOIN [link] AS [p0] ON [p].[LinkdId] = [p0].[Id]
LEFT JOIN (
  SELECT [t].[Id], [t].[value], [p1].[PersonsId], [p1].[TagsId]
  FROM [person_tags] AS [p1]
  INNER JOIN [tags] AS [t] ON [p1].[TagsId] = [t].[Id]
) AS [t0] ON [p].[Id] = [t0].[PersonsId]
WHERE [p0].[type] = 4
ORDER BY [p].[Id], [p0].[Id], [t0].[PersonsId], [t0].[TagsId]

with of course the same result :

{
  "@odata.context": "http://localhost:5078/odata/$metadata#Persons(Kind(),Tags())",
  "value": [
    {
      "Id": 4,
      "LinkId": 4,
      "Lastname": "LastName4",
      "Firstname": "Firstname4",
      "Link": {
        "Id": 4,
        "Type": "E",
        "Information": "Test4"
      },
      "Tags": []
    },
    {
      "Id": 6,
      "LinkId": 4,
      "Lastname": "LastName6",
      "Firstname": "Firstname6",
      "Link": {
        "Id": 4,
        "Type": "E",
        "Information": "Test4"
      },
      "Tags": []
    }
  ]
}

the db structure is almost (for the important part) the same, so from my point of view, the request should be (from the eloquent point of view) almost the same.

What do you thing ?

[27pchrisl]

I believe this may have the same source problem as #255 (comment) - can you confirm?

[27pchrisl]

Resolved in https://github.com/flat3/lodata/releases/tag/v5.14.0

[Angelinsky7]

@27pchrisl sadly it's not working correctly....
i still have

{
    "@context": "http://localhost:8080/odata/$metadata#Gparent01s(link())",
    "value": [OData-error: {
            "code": "expression_parser_error",
            "message": "Encountered an invalid symbol at: contains(link/>v<alues,'tenetur')",
            "target": null,
            "details": [],
            "innererror": {}
}

i tried with flat3/lodata": "^5.15

i think the issue is still at the same place as before : At the parser level

Like i said there :

It seems that all the filters have the same kind of issue. I tried with $filter=contains(link/values, '?') but it's the same issue. At some point the parser should not target the main entitySet but the entitySet of the navigation property :

lodata/src/Expression/Parser.php

Lines 623 to 656 in 7f9ce1c

	public function tokenizeNavigationPropertyPath(): bool
	{
	$currentResource = $this->getCurrentResource();
	if (!$currentResource) {
	return false;
	}
	$navigationProperties = $currentResource->getType()->getNavigationProperties();
	$token = $this->lexer->with(function () {
	$identifier = $this->lexer->identifier();
	$this->lexer->char(Lexer::pathSeparator);
	return $identifier;
	});
	if (!$token) {
	return false;
	}
	$property = $navigationProperties->get($token);
	if (!$property) {
	return false;
	}
	$operand = new Node\Property\Navigation($this);
	$operand->setValue($property);
	$this->operandStack[] = $operand;
	$this->tokens[] = $operand;
	return true;
	}

does not change the current context and then

lodata/src/Expression/Parser.php

Line 754 in 7f9ce1c

$currentResource = $this->getCurrentResource();

fail to find the correct property

i've made some test and this solution could work (but i'm hacking into your code) :

https://github.com/Angelinsky7/laravel-odata/tree/fix-parser-navigation-context

if you look at the changes : 5.x...Angelinsky7:fix-parser-navigation-context you can see the idea :

1. In tokenizeNavigationPropertyPath we change the context of the parser to reflect the current navigation property and add a new NavigationOperator
2. The next token is then check with the new context (the one from the navigation property)
3. When we encounter tokenizeSeparator or tokenizeOperator we clean the context (we go back to the original context)

Sadly it's still not working correctly because of

{
    "@context": "http://localhost:8080/odata/$metadata#Gparent01s(link())",
    "value": [OData-error: {
            "code": "The provided property (link) is not filterable",
            "message": "Bad request",
            "target": null,
            "details": [],
            "innererror": {}
        }

and this is "correct" because we now need to handle the new NavigationOperator to permit this behavior....

So the next step would be to handle NavigationOperator it's done here :

https://github.com/Angelinsky7/laravel-odata/tree/fix-sql-navigation-properties

and again if you look at the changes : 5.x...Angelinsky7:fix-sql-navigation-properties you will see the idea :

1. In Parser.applyOperator use the new NavigationOperator
2. In SqlExpression.evaluate handle the new NavigationOperator
3. Add a SQLJoin Trait and SQLJoinDefinition to EloquentEntitySet
4. In query and count add a call to generateJoins to add all navigation operators
5. Now when where expression is now doable and correct

select 
  `gparent01s`.* 
from `gparent01s` 
  left join `glink01s` on 
    `glink01s`.`id` = `gparent01s`.`glink01_fk` 
where 
  glink01s.values LIKE ? order by `id` asc limit 1000;
----
["%hhh%"]

And after all this we have something working....
Of course, it's absolutely not finished and i'm pretty sure that i've create redundant code (because i don't fully understand your code yet).... but i think it could be the right direction.

For example, we should not use aliases for the left joins because in the case of 2 different navigation's properties targeting the same sql table we would have an error. Instead it would be better to either use the left join capabilities of the Eloquent driver or generate custom aliases (p0, p1, p2 like the odata team are doing it in the dotNet project for example)

I hope that we'll be able to talk about this and find a solution.

[27pchrisl]

Right I see now, I was under the impression the behaviour you wanted existed but wasn't working correctly. Now I see it's in fact a feature that just doesn't exist!

I'm using your modifications to see how filtering on navigation properties can be implemented.

[Angelinsky7]

it's because i was under the impression that the lib was covering all odata's features (and that's the feature i need :-) )
I'm here to help and code if you need !
If you prefer a complete fork instead of two branches i can make it and if you need more explanation i will be happy to share my thoughts.... it will be easier

[NikAtYabble]

So is the filtration based on nested (expanded) entities not supported for now? Any ideas if there are plans to add support in the (near) future? Tried with 5.29 and still was not able to run api/v2/Chunks?$expand=response&$filter=response/id eq 1 as it targets chunk id instead of response id (which is a nested 1:1 child entity)

[RrOoSsSsOo]

Any news for that issue? It is crucial in a OData usage...

[carloeusebi]

I'm having the same issue here, $filter=roles/id eq 1 targets user's id instead of role's id.
$expand=roles works correctly.

Using v 5.33.5