Left vs Inner joins issue

[bukajsytlos]

I didn't find any follow-up of #39.
How should one handle this issue? I don't think that join hints are way to go, as whether to use left or right join depends on query parameters being used and whether a relationship is optional somewhere on the path.

i.e if we have relationship1.value==abc or relationship1.value=na=''
if relationship1 is not optional, there is no point in left join.
if relationship1 is optional it should be joined left.
if relationship1 is optional but there is no "or is null" it should be inner join.
there is also usecase mentioned in last comment of linked PR.

afaiu this would need some preprocessing, cause join type can't be changed on the fly, right?

[perplexhub]

Do you have any sample codes?

[bukajsytlos]

for example this test

	@Test
	public final void testXXX() {
		String rsql = "company.code==demo,city.name==Moon";
		List<User> users = userRepository.findAll(toSpecification(rsql));
		long count = users.size();
		log.info("rsql: {} -> count: {}", rsql, count);
		assertThat(rsql, count, is(3l));
	}

[perplexhub]

Can you provide the expected result and actual result? Thanks.

[bukajsytlos]

expected result should be 3 users
2 from company, 1 from city
as a result of this query

select user0_.id          as id1_11_,
       user0_.city_id     as city_id5_11_,
       user0_.company_id  as company_6_11_,
       user0_.create_date as create_d2_11_,
       user0_.name        as name3_11_,
       user0_.status      as status4_11_
from users user0_
         inner join company company1_ on user0_.company_id = company1_.id
         left outer join city city2_ on user0_.city_id = city2_.id
where company1_.code = ?
   or city2_.name = ?

instead of one result as of this actual query

select user0_.id          as id1_11_,
       user0_.city_id     as city_id5_11_,
       user0_.company_id  as company_6_11_,
       user0_.create_date as create_d2_11_,
       user0_.name        as name3_11_,
       user0_.status      as status4_11_
from users user0_
         inner join company company1_ on user0_.company_id = company1_.id
         inner join city city2_ on user0_.city_id = city2_.id
where company1_.code = ?
   or city2_.name = ?;

[perplexhub]

Could you try this?

	@Test
	public final void testJoinHintsRelationWithMultipleOrCase() {
		String rsql = "company.code==demo,city.name=='Hong Kong Island'";
		Map<String, JoinType> joinHints = new HashMap<String, JoinType>() {{put("User.city", JoinType.LEFT);put("User.company", JoinType.LEFT);}};
		List<User> users = userRepository.findAll(toSpecification(rsql, true,null, joinHints));
		long count = users.size();
		log.info("rsql: {} -> count: {}", rsql, count);
		assertThat(rsql, count, is(3l));
	}

[bukajsytlos]

yes, but as I said I don't find join hints meaningful. I don't know which rsql string will be send by client.
I (as a user of this lib) don't want to care and parse rsql string to determine if I want left or inner join.

[perplexhub]

Should we always use left join only if the relationship is optional? Otherwise, use inner join.

[bukajsytlos]

unfortunately rules are not that simple. I will try to write them down

[bukajsytlos]

Not 100% sure if this is all but this is what I've came up:

1. checking isNull on relationship path. Left join has to be on from first optional relationship onward
2. having OR "split", where path joins on any branch have to be left joined from first optional relationship onward on path

[bukajsytlos]

although first point is questionable I guess

[perplexhub]

Do you have time to implement it? It would be more easy to verify with test cases.

[bukajsytlos]

well, I could try, but do you have an idea how to go about that? afaik we need to determine join type based on data later during visiting.

[perplexhub]

Could we first analyze the structure of the query and generate the JoinHint before processing in converter (RSQLVisitor)?

[bukajsytlos]

hmm, that's good idea.