I"m using Doctrine for a website that has 3 tables to connect together. In simplest terms, they look like this
Contractor - id - name ContractorJob - id - contractor_id - job_id - status Job - id - start_date - end_date
I want to join Jobs to Contractors based on a date range, but you can see that the only way to do that is through the ContractorJob table. It means that a simple inner join between Contractor and ContractorJob is essentially random, and therefore the subsequent join between ContractorJob and Job is mostly useless.
How can I join Contractor to Job using start_date and end_date as conditions?
Pseudo query is as follows
select from contractor c left join ContractorJob cj on cj.contractor_id = c.id left join job j with j.contractor_job = cj.id and j.start_date < :start_date and j.end_date > :end_date where j.id is null
(find all contractors who do not have a job in the given date range)
*note: Doctrine is a web framework DB manager that handles low level query processing so that applications can use more high-level functions. It allows you to say
query.innerJoin('a.second_table', 'b') and have the correct join made