Querydsl join on same table multiple times

java querydsl
Josh picture Josh · Feb 4, 2015 · Viewed 8.8k times · Source

Let's say I have two tables Task and Company. Company has columns id and name. Task has two columns customerId and providerId which link back to the id column for Company.

Using Querydsl how do I join on the Company table twice so I can get the name for each company specified by the customerId and providerId?

Code that maybe explains better what I'm trying:

Configuration configuration = new Configuration(templates);
JPASQLQuery query = new JPASQLQuery(this.entityManager, configuration);

QTask task = QTask.task;
QCompany customer = QCompany.company;
QCompany provider = QCompany.company;

JPASQLQuery sql = query.from(task).join(customer).on(customer.id.eq(task.customerId))
    .join(provider).on(provider.id.eq(task.providerId));

return sql.list(task.id, customer.name.as("customerName"), provider.name.as("providerName"));

Which generates SQL:

select task.id, company.name as customerName, company.name as providerName from task join company on company.id = task.customerId

And I'd really like it to be:

select task.id, customer.name as customerName, provider.name as providerName from task join company as customer on customer.id = task.customerId join company as provider on provider.id = task.providerId

I couldn't figure out how to alias the table I was joining so I could distinguish between customer and provider names. I tried doing new QCompany("company as provider") but that didn't work. Anyone know how one can do this?

Answer

Timo Westkämper picture Timo Westkämper · Feb 5, 2015

If you need to variables just do the following

QCompany customer = new QCompany("customer");
QCompany provider = new QCompany("provider");

Reassignment of the default variable QCompany.company doesn't help

Related questions

Dynamic spring data jpa repository query with arbitrary AND clauses

I'm using Spring data jpa repositories, Got a requirement to give search feature with different fields. Entering fields before search is optional.I have 5 fields say EmployeeNumber, Name, Married,Profession and DateOfBirth. Here i need to query only with the …

Read More
Eclipse issue with Maven build and JDK when generating Qclasses in Querydsl

When I add this code below in my pom.xml to support Querydsl <plugin> <groupId>com.mysema.maven</groupId> <artifactId>apt-maven-plugin</artifactId> <version>1.0.6</version> <executions&…

Read More
QueryDsl - subquery in collection expression

I'm using spring-data-jpa and querydsl (3.2.3) I have a scenario where I'm creating set of predicates based on user filer/input. All of these comes to BooleanExpression. My simplified model looks as following: @Entity public class Invoice { @ManyToOne private Supplier supplier; } @…

Read More