More JOINs

This has been confounding me for a few days now.


I am attempting what I call a double join.  Let me describe what I'm trying to do, then we'll dive into the meat of it.


I have a table called Matches that has three fields
id, character1_id, character2_id.
I have another table called charnames that has only two fields.
id, charname.


For example sake, let's say that this is to track tennis matches between people.  So in 'Charnames' I have a bunch of id's and a bunch of names.
1  Jeremy
2  Bob
3  Gandalf
4  Frodo


And 'Matches' pairs them up by id.
1  3  4
2  1  2


So if I queried this and joined it, I'd get something like this:
Match 1: Gandalf(3) and Frodo(4)
Match 2: Jeremy(1) and Bob(2)


(We'll tackle triple joins later!  ugh!)


And that's it.  The trick is that I have to join it twice, because 'Matches' contains two character fields, both of which tie back to the primary key index on 'Charnames'.


So here's the code involved at the moment (which is not working), and all the assorted details.


YAML for 'Charnames'

ORM\Testing\Charnames:
  type: entity
  table: test_charnames
  fields:
    id:
      type: integer
      id: true
      generator:
        strategy: AUTO
    charname:
      type: string
      length: 255

And the entity file for 'Charnames'

 <?php
namespace ORM\Testing;
use Doctrine\ORM\Mapping as ORM;
/**
 * ORM\Testing\Charnames
 */
class Charnames
{
    /**
     * @var integer $id
     */
    private $id;
    /**
     * @var string $charname
     */
    private $charname;

    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }
    /**
     * Set charname
     *
     * @param string $charname
     * @return Charnames
     */
    public function setCharname($charname)
    {
        $this->charname = $charname;
        return $this;
    }
    /**
     * Get charname
     *
     * @return string
     */
    public function getCharname()
    {
        return $this->charname;
    }
}

Table structure:

CREATE TABLE IF NOT EXISTS `test_charnames` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `charname` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ; 

And now 'Matches'.  YAML first.

 ORM\Testing\Matches:
  type: entity
  table: test_matches
  fields:
    id:
      type: integer
      id: true
      generator:
        strategy: AUTO
  manyToOne:
    character1:
      targetEntity: ORM\Testing\Charnames
    character2:
      targetEntity: ORM\Testing\Charnames

 Entity file:

<?php
namespace ORM\Testing;
use Doctrine\ORM\Mapping as ORM;
/**
 * ORM\Testing\Matches
 */
class Matches
{
    /**
     * @var integer $id
     */
    private $id;
    /**
     * @var ORM\Testing\Charnames
     */
    private $character1;
    /**
     * @var ORM\Testing\Charnames
     */
    private $character2;

    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }
    /**
     * Set character1
     *
     * @param ORM\Testing\Charnames $character1
     * @return Matches
     */
    public function setCharacter1(\ORM\Testing\Charnames $character1 = null)
    {
        $this->character1 = $character1;
        return $this;
    }
    /**
     * Get character1
     *
     * @return ORM\Testing\Charnames
     */
    public function getCharacter1()
    {
        return $this->character1;
    }
    /**
     * Set character2
     *
     * @param ORM\Testing\Charnames $character2
     * @return Matches
     */
    public function setCharacter2(\ORM\Testing\Charnames $character2 = null)
    {
        $this->character2 = $character2;
        return $this;
    }
    /**
     * Get character2
     *
     * @return ORM\Testing\Charnames
     */
    public function getCharacter2()
    {
        return $this->character2;
    }
}

Table Structure:

CREATE TABLE IF NOT EXISTS `test_matches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `character1_id` int(11) DEFAULT NULL,
  `character2_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_DA67C172F6129E9E` (`character1_id`),
  KEY `IDX_DA67C172E4A73170` (`character2_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ; 

Here is the data actually in my tables:
test_matches:

id    character1_id    character2_id
1          7                        2
2          8                         4
3          10                       3
4          2                        1

test_charnames:

1         Gandalf
2         Gollum
3         Bilbo
4         Frodo
5         Samwise
6         Gimili
7         Legolas
8         Thorin
9         Saurumon
10       Treebeard

 And now, some querying and code.

Here is my query at present:

$query = $this -> doctrine -> em -> createQuery ("SELECT a.id, b.charname, c.charname
            FROM ORM\Testing\Matches a
            JOIN a.character1 b
            JOIN a.character2 c
            ");

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

This returns the following data:

array(4) {
[0]=> array(2) { ["id"]=> int(1) ["charname"]=> string(6) "Gollum" }
[1]=> array(2) { ["id"]=> int(2) ["charname"]=> string(5) "Frodo" }
[2]=> array(2) { ["id"]=> int(3) ["charname"]=> string(5) "Bilbo" }
[3]=> array(2) { ["id"]=> int(4) ["charname"]=> string(7) "Gandalf" } } 

So it is returning the 2nd character name.  But not the first.

I also tried this query:

$query = $this -> doctrine -> em -> createQuery ("SELECT a, b, c
            FROM ORM\Testing\Matches a
            JOIN a.character1 b
            JOIN a.character2 c
            ");

And the output:

array(4) {
[0]=> object(stdClass)#42 (4) { ["__CLASS__"]=> string(19) "ORM\Testing\Matches" ["id"]=> int(1) ["character1"]=> string(21) "ORM\Testing\Charnames" ["character2"]=> string(21) "ORM\Testing\Charnames" }
[1]=> object(stdClass)#71 (4) { ["__CLASS__"]=> string(19) "ORM\Testing\Matches" ["id"]=> int(2) ["character1"]=> string(21) "ORM\Testing\Charnames" ["character2"]=> string(21) "ORM\Testing\Charnames" }
[2]=> object(stdClass)#72 (4) { ["__CLASS__"]=> string(19) "ORM\Testing\Matches" ["id"]=> int(3) ["character1"]=> string(21) "ORM\Testing\Charnames" ["character2"]=> string(21) "ORM\Testing\Charnames" }
[3]=> object(stdClass)#73 (4) { ["__CLASS__"]=> string(19) "ORM\Testing\Matches" ["id"]=> int(4) ["character1"]=> string(21) "ORM\Testing\Charnames" ["character2"]=> string(21) "ORM\Testing\Charnames" } }  

So, I *think* that the problem is with my query.  But its entirely possible that it is a problem with my entities or table structure.