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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With