Using Querydsl with multiple filter conditions in java

[java brain]

0down votefavorite

I am new to querydsl and trying to get data from Postgres Using JPA and based on multiple fiters. I have tried using any() but it did not help. Tried another option with subquery but not sure how to handle multiple subquery within a query. So i have a requirement where i want to filter data from different tables and based on multiple filters. Company entity has a list of products it deals with(just a list, Product is not an entity) So i have a company entity, Employee entity, Address Entity and Department Entity. I want to fetch records from company entity based on following filter: 1. For a particular employee or list of employee AND 2. For employees from a particular country or from a list of countries AND 3. For employee from a particular department of list of department AND 4. For a particular product which is a list in company. I want all of the above criteria to be used in querydsl as all these values will be coming out of values selected from UI Drop down filter. Can anyone please help me in filtering this data using querydsl?

[Timo Westkämper]

Please provide minimal versions of the Company, Employee, Address and Department classes. 

If Product is not an entity, what type is it then?

Also what did you try, and how did it fail?

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[java brain]

I have slightly modified my requirement:

I want to fetch records from company entity based on following filter: 1. For a particular employee or list of employee AND 2. For employees from a particular country or from a list of countries AND 3. For employee from a particular department of list of department

Company Entity looks like:

@Entity

public class Company {

    @Id

    @GeneratedValue(strategy= GenerationType.TABLE, generator="seqGen")

    private Long id;

    @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)

    @JoinTable(

            name = "company_employee",

            joinColumns={@JoinColumn(.......)

    private List<Employee> employees

    

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)

    @JoinTable(

            name = "country",

            joinColumns={......)

    private List<Country> country;

    

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)

    @JoinTable(

            name = "department",

            joinColumns={.....})

    private List<Department> department

Employee entity:

@Entity

@TableGenerator(table="SEQUENCE", pkColumnName="SEQ_NAME", pkColumnValue="SEQ_GEN", valueColumnName="SEQ_COUNT", name="seqGen")

public class Employee {

    @Id

    @GeneratedValue(strategy= GenerationType.TABLE, generator="seqGen")

    @Column(name = "session__location_id", nullable=false)

    private Long id;

    @ObjectLockerIdentifier

    @Column(nullable = false, unique=false)

    //@Column(nullable = false, unique=true)

    private String name;

Department Entity:

@Entity

public class Department {

@Id

@GeneratedValue(strategy= GenerationType.TABLE, generator="seqGen")

     @Column(name = "session__subtype_id", nullable=false)

private Long id;

@ObjectLockerIdentifier

@Column(nullable = false, unique=false)

private String type

 and so on.

Now my requirement is:

i want to use querydsl such as i can dynamically create joins based on input from ui

so in method where i have defined 

new JPAQuery(getEntityManager()).from(entityPath)where(predicates),

 

I want to pass an extra parameter in method where above is defined. 

may be an array consisting of combination of various classes where i need to do query.

(Since i can not define all relation in predicates using any() from multiple table)

so that extra infor can contain (Company.Employee, Employee), (Company.Department, Department) etcc...

I have also Qclasses generated for my entity.

So in predicates[] we have defined simple query for each individual table, like getting name from employee table, getting category from category table.

for ex.(results.add(employee.name.in(CustomFilterClass.getEmployees)))

(results.add(department.typr.in(CustomFilterClass.getDepartment())))

these above things i want to join in below generic query after from with having a loop based on how many filters are selected from ui.

new JPAQuery(getEntityManager()).from(entityPath).join(EntityPath, Path).where(predicates),

So since i cann pass only EntitypTh or querydsl specific things in joins, how should i construct my combination information in extra parameter i am passing in method.

so will it be a list of<Entitypath> or??

How would i able to create Entity path base Object or Path builder object?

How this can be acheived dynamically??

tried using like:

 PathBuilder<QCompany> sessionsBuilder = new PathBuilder<>(QCompany.class, "employee.name"); or

 PathBuilder<QEmployee> employee= new PathBuilder<>(Employee.class, "name");

But does not seem to work.

How can this be handled so i can pass this info in extra param and then add number of joins based on number of filters.

please help.

On Saturday, January 30, 2016 at 12:52:47 AM UTC+5:30, timowest wrote:

Please provide minimal versions of the Company, Employee, Address and Department classes. 

If Product is not an entity, what type is it then?

Also what did you try, and how did it fail?

[timowest]

Hi

On Wednesday, February 3, 2016 at 5:49:35 PM UTC+2, java brain wrote:

I have slightly modified my requirement:

I want to fetch records from company entity based on following filter:

The predicates would be 

 

1. For a particular employee or list of employee AND

 

company.employees.any().eq(emp) or .in(emps)

2. For employees from a particular country or from a list of countries AND

company.country.eq(country) or .in(countries)

 

3. For employee from a particular department of list of department

I don't see a relation between Employee and Department.

Just put these predicates into the where part of your query:

query.select(company).from(company).where(...).fetch()

[java brain]

OK Thanks,  But say i have may be 3 conditions in predicate that are using any(), so doesnt it mean that it will be converted into a query where each any will be converted into its own Exists query and may be that result is not we want. right or??

Also in predicates filter, i am having a doubt.

Should i define all predicates in respect to Company Entity which records i am fetching like u mentioned below.

for example:

results.add(company.employees.any().name.in(filter.getEmployees));

or should i write predicates for all the involved entity like instead of above predicate for company.employye, i write as:

results.add(employee.name.in(filter.getEmployee)) where employee is from QEmployee.

My Objective is to filter data based on different filter selected together from UI(like Comapny A having employees with departments selected and also employee name selected )

so result is to provide filter data. Also due to design constraint, i have to prepare all parameters required in

JPAQuery(getEntityManager()).from(entityPath)where(predicates),  from another module and then pass these values to this statement directly.

So i am not sure about which strategy i should select. Any() can be aproblem if it is used in say 3 predicates., then how to handle this?

[java brain]

Just to add here, when i used different predicats filter on diff entity and addd to predicate array like:

results.add(employee.name.in(filter.getEmployee)) where employee is from QEmployee.

results.add(department.name.in(filter.getDepartment)) where departmentis from QDepartment

when i debug it, i got following kind of query:

select company

from Company company

  inner join company.employee as employee

  inner join company.department as department

where  employee.name = ?3 and department.type = ?4

But the result it fetches in strange. So it get me data of compnay which i selected, but since company has say linked to two dept. with one to many then it fetches data for both department.

So even if i pass department as IT, if one entry in company is related to many dept, it will fetch data for all dept. and not for onlly dept i passed in.

so it is just and addition to my previous query. Kindly have a look at both post.

Many thanks

[timowest]

On Thursday, February 4, 2016 at 12:09:39 PM UTC+2, java brain wrote:

OK Thanks,  But say i have may be 3 conditions in predicate that are using any(), so doesnt it mean that it will be converted into a query where each any will be converted into its own Exists query and may be that result is not we want. right or??

Yes, for these cases you should write the exists subquery explicitly. 

Also in predicates filter, i am having a doubt.

Should i define all predicates in respect to Company Entity which records i am fetching like u mentioned below.

for example:

results.add(company.employees.any().name.in(filter.getEmployees));

or should i write predicates for all the involved entity like instead of above predicate for company.employye, i write as:

results.add(employee.name.in(filter.getEmployee)) where employee is from QEmployee.

Instead of any() you can of course also use a join, but the query semantics will be different. 

query.from(company).innerJoin(company.employees, employee).where(employee...)

[timowest]

When using joins JPA won't aggregate the results based on the projection.

It will return a row for each company-department-employee match.

If you need disitinct results, a subquery works better.

[java brain]

Ok, Thank you very much.