Doctrine JOINs

Lets talk about JOINs in Doctrine.

We'll use a simple example.  Lets say that we want to record first and last names, but since we'll be doing entire families, there will be overlap in last names: Joe Smith, Sally Smith, Bobby Smith, and so on.  We'll have a table for first names, and a table for last names.  The first names should all link back to their corresponding last name.
So our tables might look like this:

Firstnames:

id	first_name	mylastname
1	Joe	15
2	Sally	15
3	Lisa	15
4	Mark	16
5	Betty	16
6	Henry	16

Lastnames:

id	last_name
15	Smith
16	Jones

So when we execute a query after its JOINed we'll get: Joe Smith, Sally Smith, Lisa Smith, Mark Jones, Betty Jones, Henry Jones.

As I'm currently understanding, this is considered a manyToOne unidirectional relationship.  mylastname should be a foreign key that references the id field in the Lastnames table.

Lets look at my YAML entity files:

ORM\Testing\Firstnames:
  type: entity
  table: test_firstnames
  fields:
    id:
      type: integer
      id: true
      generator:
        strategy: AUTO
    firstname:
      type: string
      column: first_name
  manyToOne:
    mylastname:
      targetEntity: ORM\Testing\Lastnames

       

ORM\Testing\Lastnames:
  type: entity
  table: test_lastnames
  fields:
    id:
      type: integer
      id: true
      generator:
        strategy: AUTO
    lastname:
      type: string
      column: last_name

This generates the following SQL schema:


CREATE TABLE test_lastnames (id INT AUTO_INCREMENT NOT NULL, last_name VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = InnoDB;

CREATE TABLE test_firstnames (id INT AUTO_INCREMENT NOT NULL, mylastname_id INT DEFAULT NULL, first_name VARCHAR(255) NOT NULL, INDEX IDX_23D7305696EC0FA4 (mylastname_id), PRIMARY KEY(id)) ENGINE = InnoDB;

ALTER TABLE test_firstnames ADD CONSTRAINT FK_23D7305696EC0FA4 FOREIGN KEY (mylastname_id) REFERENCES test_lastnames (id)

If I put some data into the tables and then run the following:

$whichFamily = '2';

$query = $this -> doctrine -> em -> createQuery ("SELECT a.firstname, b.lastname FROM ORM\Testing\Firstnames a JOIN a.mylastname b WHERE b.id = $whichFamily");

$result = $query->getResult();
\Doctrine\Common\Util\Debug::dump($result); 

it returns

array(3) { [0]=> array(2) { ["firstname"]=> string(5) "Sarah" ["lastname"]=> string(5) "Jones" } [1]=> array(2) { ["firstname"]=> string(5) "Bobby" ["lastname"]=> string(5) "Jones" } [2]=> array(2) { ["firstname"]=> string(6) "Martha" ["lastname"]=> string(5) "Jones" } }

Which looks muddled, but its exactly what I want!

 Then I add

foreach ($result as $val1 => $val2) {
//echo $val2;
foreach ($val2 as $val3 => $val4) {
echo $val4;
echo " ";
}
echo "</br>";
}

and I get:

Sarah Jones
Bobby Jones
Martha Jones