Filter is not applied in the "where" clause

[fernando88to]

Describe the bug

When doing a left join and making clauses in the left join table, the restriction is not applied in the sql

Expected behavior

That all clauses and were applied in SQL, especially in the table where the inner join is being done

Actual behavior

How To Reproduce

` @Entity @Table(name = "film") public class Film {

@Id
@Column(name = "film_id")
public Integer filmId;

@Column(name = "film_name")
public String name;

@ManyToOne
@JoinColumn(name="language_id", nullable = false)
public Language language;

}

@Entity @Table(name = "language") public class Language {

@Id
@Column(name = "language_id")
public Integer languageId;

@Column(name = "country")
public String country;

}

jpaStreamer.stream(of(Film.class).joining(Film$.language))
.filter(Film$.name.containsIgnoreCase("test")) .filter(film -> film.language.country.equals("BR")) .forEach(System.out::println);

`

Generated SQL

select f1_0.film_id, f1_0.language_id, l1_0.language_id, l1_0.country, f1_0.film_name from film f1_0 left join language l1_0 on l1_0.language_id=f1_0.language_id where lower(f1_0.film_name) like ? escape

Build tool e.g. Gradle 8.1.1

JPAStreamer version e.g. JPAStreamer 3.0.4

JPA Provider e.g. Hibernate 6.2.9.Final

Java Version openjdk 11.0.17 2022-10-18

Additional context

Example repository https://github.com/fernando88to/issuejpastream

[julgus]

Hi Fernando, thank you for the detailed description of your problem. I would not really describe this as a bug - I'll try to explain why.

Your stream has one filter that leverages the JPAStreamer metamodel, and one that uses an anonymous lambda. These will be handled differently:

• The predicate Film$.name etc. is included in the query you provided where lower(f1_0.film_name) like ? escape as JPAStreamer can translate such predicate to SQL.
• The anonymous lambda however cannot be translated by JPAStreamer as the expression does not hold enough metadata about the underlying field. This is described in the docs here.

jpaStreamer.stream(of(Film.class).joining(Film$.language))
     .filter(Film$.name.containsIgnoreCase("test"))       // this is included in the query 
     .filter(film -> film.language.country.equals("BR"))   // this cannot be translated to SQL
     .forEach(System.out::println);

I am assuming you may also wonder how you can filter on the language property. Unfortunately current the answer is that you cannot filter on the joined entities in the database.

[fernando88to]

Thank you for your answer, it helps me understand better