Querydsl join on same table multiple times
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?
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