A little löve

While kicking around game programming ideas, I decided to have a look at graphical game development.  I was a little skeptical of digging - I'm not a serious programmer, and I have zero experience with graphic development.  Plus I'm not really much of a visual artist.  But I looked around anyway.

And found löve.

Its a 2d framework for lua programming.  I've heard a lot about lua - tons of games that I play use it for their scripting, but I've barely glanced at it.

So I got my hands on it and began digging through tutorials and such.

Its a world of difference from PHP development.  For starters, well, its graphical.  Grizzled veterans would have chuckled to watch me trying to puzzle out the theory behind making things move from one location to another.  And I've gotten accustomed to SQL backends, so my realization that this variety of programming would rely on savegame files of some sort was startling.

Its odd too - I've grown fairly familiar with PHP, so starting over in terms of syntax, concept, theory and basic code principles seems much like having forgotten how to swim.

Joining JOINs

It has been suggested that I need to use a manyToMany approach to solve this problem.

http://readthedocs.org/docs/doctrine-orm/en/latest/reference/association-mapping.html#one-to-many-unidirectional-with-join-table

Here is the YAML from that page (with a corrected typo)

User:

  type: entity

  manyToMany:

    phonenumbers:

      targetEntity: Phonenumber

      joinTable:

        name: users_phonenumbers

        joinColumns:

          user_id:

            referencedColumnName: id

        inverseJoinColumns:

          phonenumber_id:

            referencedColumnName: id

            unique: true

I tried to do some conversion, but have run into some barriers, which I'll detail.  Its worth noting that this YAML does not work... now I'm trying to figure out if its meant to be stand alone, or if requires the other Entities to be declared on their own..

[thinks for a few minutes]

I am pretty sure they do need their own separate entities.

So we'll have Names and Matches, like normal, and this new entity is a Joining entity, so it just builds a bridge between the two.

So lets take it from the beginning.

Names should have {id} and {name},  Matches should have {id}, {name1} and {name2} or some permutation thereof.  

[types furiously]

Okay, so I have the following test YAML code:

ORM\Testing\Names:

  type: entity

  fields:

    id:

      type: integer

      id: true

      generator:

        strategy: AUTO

  manyToMany:

    tennis:

      targetEntity: Matches

      joinTable:

        name: names_to_matches

        joinColumns:

          name_id:

            referencedColumnName: character1

        inverseJoinColumns:

          matches_id:

            referencedColumnName: id

            unique: true

#

 works fine for generating entities and proxies, but when I try to generate the SQL schema I get:

Column name `character1` referenced for relation from ORM\Testing\Names towards ORM\Testing\Matches does not exist.

[still later]

Hm.  The example on the previously provided link seems to be to link just two unrelated fields.  Like if I had a firstNames entity and a lastNames entity, firstAndLast would use code much like in that example to link them together..

.... in progress...

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.

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  

I made a ton of progress week before last and did not stop to do updates here like I should have done.  I made some changes to the doctrine entities and my db schema, which I think I have well in hand.  The big thing I'm trying to do now is to figure out Association Mapping (like sql join), and I've posted here and here about it, hoping for some advice or a breakthrough.

Doctrine and CI make it incredibly easy to write code, and fast.  More soon, hopefully.

Boy do I have some catching up to do.

I was able to get a goodly bit of code written over this past weekend.  Sorted out a few problems and so forth.

One thing that I have made some progress on but still needs some research and time is session security at the page/function level.  More on this in another post.

more work on entities

A PHP Error was encountered

Severity: Warning
Message: require(applicationFolder/models/Entities/User.php) [function.require]: failed to open stream: No such file or directory
Filename: Common/ClassLoader.php
Line Number: 163

Fatal error: require() [function.require]: Failed opening required 'applicationFolder/models/Entities/User.php' (include_path='.:/usr/local/lib/php:/usr/local/php5/lib/pear') in/../applicationFolder/libraries/Doctrine/Common/ClassLoader.php on line 163

require ($this->includePath !== null ? $this->includePath . DIRECTORY_SEPARATOR : '')

               . str_replace($this->namespaceSeparator, DIRECTORY_SEPARATOR, $className)

               . $this->fileExtension;