15

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?

Josh
  • 2,842
  • 8
  • 45
  • 51

1 Answers1

36

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

Timo Westkämper
  • 21,824
  • 5
  • 78
  • 111
  • Thanks Timo, but that creates a query with a table "customer" and a table "provider". Those tables don't exist. It's just one table called company. If it would do "company as customer" and "company as provider" that would work great. – Josh Feb 05 '15 at 17:16
  • If QCompany is an APT based generated class you query won't work, you will need to use JPAQuery for this case. JPASQLQuery is for SQL queries with Q-types generated using this approach http://www.querydsl.com/static/querydsl/3.6.1/reference/html/ch02s03.html#d0e668 – Timo Westkämper Feb 05 '15 at 17:30
  • Bummer, that sucks. Is this an intentional thing or something I can open a ticket for? – Josh Feb 05 '15 at 18:05
  • That's intentional, but you can open a ticket for it. At least it should be better documented. How did you end up picking JPASQLQuery? – Timo Westkämper Feb 05 '15 at 20:35
  • I'm using spring-data-jpa and so I have everything annotated, but I need to do some reporting which needs to pick from a bunch of things that don't necessarily connect in the way the JPAQuery wants them to be. So JPASQLQuery seemed a nice in between where I could use all my annotations to generate the classes but still join things with foreign keys in sql instead of creating a full entity graph in my code. And I could just use the EntityManager I already had setup instead of pulling the connection out of it to use SQLQuery. It all worked too except this lack of aliasing. – Josh Feb 05 '15 at 22:41