CODEWITHSUNDEEP
joindoctrine-orm
Shrujan Shetty
Shrujan Shetty

Reputation: 2394

Joining multiple tables in Doctrine2

I have the following scenario:

project -----> company <----- clientdetails

I have three tables: project, clientdetails, and company.

The following is the relations between them:

project is related to company (via companyid).
clientdetails is related to company (via companyid).

project name is stored in project table.
company name is stored in company table.
client name is stored in clientdetails table.

How can I retrieve the project name from project table along with client name stored in clientdetails table and company name from company table for the respective project using doctrine 2?

Upvotes: 1

Views: 1054

Answers (1)

Michael De Keyser
Michael De Keyser

Reputation: 797

That is actually impossible with these mappings. You lack at least one: Project should know clientdetails as well.

Let's say you have one project's ID, you can easily retrieve company's name but there's simply no way you can find which clientdetails row is related to that project.

Let me give you a SQL SELECT that will help me explain:

SELECT p.name, c.name, cd.name FROM project p, company c, clientdetails cd WHERE p.id = GIVEN_ID AND p.companyid = c.id AND cd.companyid = c.id

So, in that query you would replace GIVEN_ID with your project's ID. Now it will be easy to find company's name as this is a simple join on companyid but let's look at the other join:

cd.companyid = c.id

It looks simple but in fact, any clientdetails that is mapped to this company's ID will be a successful match and therefore there's absolutely no guarantee (and not many chances) that you get the correct client's name. As there will be more than one match (if you have more than one client mapped to this company), it will take first match it finds and return that client's name. This mean you will always have the same client name.

But, if you add a ManyToOne mapping from Project to Clientdetails you will be able to replace cd.companyid = c.id with cd.id = p.clientdetailsid and this will return the correct names.

You will also notice that this kind of query will be useless when you are using doctrine2 as you would do this easily like that:

$projectId = 1; //this is an example
$project = App_Entities_Project::find($projectId);
$projectName = $project->getName();
$companyName = $project->getCompany()->getName();
$clientdetailsName = $project->getClient()->getName();

Cheers.

Upvotes: 1

Related Questions