PHP ORMs: Doctrine vs. Propel

I'm starting a new project with symfony which is readily integrated with Doctrine and Propel, but I of course need to make a choice.... I was wondering if more experienced people out there have general pros and/or cons for going with either of these two?

Thanks a lot.

EDIT: Thanks for the all the responses, useful stuff. There's no truly correct answer to this question so I'll just mark as approved the one that got the most popular up-votes.

what is doctrine hydration? [closed]

I've read about hydration in doctrine's documentation but I still can't understand what it is.

Could someone please explain?

How to specify null value as filter in a Doctrine query?

I am using Doctrine 1.1 in Zend. I am trying to write a query that will return records that have a null value in a certain column.

    $q = Doctrine_Query::create()
    ->select('a.*')
    ->from('RuleSet a')
    ->where('a.vertical_id = ?', null);

    $ruleset_names_result = $q->execute(array(), Doctrine::HYDRATE_ARRAY);

I have three records in the ruleset table which have a NULL value in the vertical_id column yet the query doest not find these.

Appreciate the help.

Sid.

Symfony2 Use Doctrine in Service Container

How do I use Doctrine in a service container?

The Code just causes an error message "Fatal error: Call to undefined method ...::get()".

<?php

namespace ...\Service;

use Doctrine\ORM\EntityManager;
use ...\Entity\Header;

class dsdsf
{ 
    protected $em;

    public function __construct(EntityManager $em)
    {
        $this->em = $em;
    }

    public function create()
    {
        $id = 10;
        $em = $this->get('doctrine')->getEntityManager();
        $em->getRepository('...')->find($id);
    }
}

services.yml

service:
    site:
        class: ...\Service\Site

Doctrine - How to print out the real sql, not just the prepared statement?

We're using Doctrine, a PHP ORM. I am creating a query like this:

$q = Doctrine_Query::create()->select('id')->from('MyTable');

and then in the function I'm adding in various where clauses and things as appropriate, like this

$q->where('normalisedname = ? OR name = ?', array($string, $originalString));

Later on, before execute()-ing that query object, I want to print out the raw SQL in order to examine it, and do this:

$q->getSQLQuery();

However that only prints out the prepared statement, not the full query. I want to see what it is sending to the MySQL, but instead it is printing out a prepared statement, including ?'s. Is there some way to see the 'full' query?

Symfony2: how to get all entities of one type which are marked with "EDIT" ACL permission?

Can someone tell me how to get all entities of one type which are marked with "EDIT" ACL permission?

I would like to build a query with the Doctrine EntityManager.

Doctrine2: Best way to handle many-to-many with extra columns in reference table

I'm wondering what's the best, the cleanest and the most simply way to work with many-to-many relations in Doctrine2.

Let's assume that we've got an album like Master of Puppets by Metallica with several tracks. But please note the fact that one track might appears in more that one album, like Battery by Metallica does - three albums are featuring this track.

So what I need is many-to-many relationship between albums and tracks, using third table with some additional columns (like position of the track in specified album). Actually I have to use, as Doctrine's documentation suggests, a double one-to-many relation to achieve that functionality.

/** @Entity() */
class Album {
    /** @Id @Column(type="integer") */
    protected $id;

    /** @Column() */
    protected $title;

    /** @OneToMany(targetEntity="AlbumTrackReference", mappedBy="album") */
    protected $tracklist;

    public function __construct() {
        $this->tracklist = new \Doctrine\Common\Collections\ArrayCollection();
    }

    public function getTitle() {
        return $this->title;
    }

    public function getTracklist() {
        return $this->tracklist->toArray();
    }
}

/** @Entity() */
class Track {
    /** @Id @Column(type="integer") */
    protected $id;

    /** @Column() */
    protected $title;

    /** @Column(type="time") */
    protected $duration;

    /** @OneToMany(targetEntity="AlbumTrackReference", mappedBy="track") */
    protected $albumsFeaturingThisTrack; // btw: any idea how to name this relation? :)

    public function getTitle() {
        return $this->title;
    }

    public function getDuration() {
        return $this->duration;
    }
}

/** @Entity() */
class AlbumTrackReference {
    /** @Id @Column(type="integer") */
    protected $id;

    /** @ManyToOne(targetEntity="Album", inversedBy="tracklist") */
    protected $album;

    /** @ManyToOne(targetEntity="Track", inversedBy="albumsFeaturingThisTrack") */
    protected $track;

    /** @Column(type="integer") */
    protected $position;

    /** @Column(type="boolean") */
    protected $isPromoted;

    public function getPosition() {
        return $this->position;
    }

    public function isPromoted() {
        return $this->isPromoted;
    }

    public function getAlbum() {
        return $this->album;
    }

    public function getTrack() {
        return $this->track;
    }
}

Sample data:

             Album
+----+--------------------------+
| id | title                    |
+----+--------------------------+
|  1 | Master of Puppets        |
|  2 | The Metallica Collection |
+----+--------------------------+

               Track
+----+----------------------+----------+
| id | title                | duration |
+----+----------------------+----------+
|  1 | Battery              | 00:05:13 |
|  2 | Nothing Else Matters | 00:06:29 |
|  3 | Damage Inc.          | 00:05:33 |
+----+----------------------+----------+

              AlbumTrackReference
+----+----------+----------+----------+------------+
| id | album_id | track_id | position | isPromoted |
+----+----------+----------+----------+------------+
|  1 |        1 |        2 |        2 |          1 |
|  2 |        1 |        3 |        1 |          0 |
|  3 |        1 |        1 |        3 |          0 |
|  4 |        2 |        2 |        1 |          0 |
+----+----------+----------+----------+------------+

Now I can display a list of albums and tracks associated to them:

$dql = '
    SELECT   a, tl, t
    FROM     Entity\Album a
    JOIN     a.tracklist tl
    JOIN     tl.track t
    ORDER BY tl.position ASC
';

$albums = $em->createQuery($dql)->getResult();

foreach ($albums as $album) {
    echo $album->getTitle() . PHP_EOL;

    foreach ($album->getTracklist() as $track) {
        echo sprintf("\t#%d - %-20s (%s) %s\n", 
            $track->getPosition(),
            $track->getTrack()->getTitle(),
            $track->getTrack()->getDuration()->format('H:i:s'),
            $track->isPromoted() ? ' - PROMOTED!' : ''
        );
    }   
}

The results are what I'm expecting, ie: a list of albums with their tracks in appropriate order and promoted ones being marked as promoted.

The Metallica Collection
    #1 - Nothing Else Matters (00:06:29) 
Master of Puppets
    #1 - Damage Inc.          (00:05:33) 
    #2 - Nothing Else Matters (00:06:29)  - PROMOTED!
    #3 - Battery              (00:05:13) 

So what's wrong?

This code demonstrates what's wrong:

foreach ($album->getTracklist() as $track) {
    echo $track->getTrack()->getTitle();
}

Album::getTracklist() returns an array of AlbumTrackReference objects instead of Track objects. I can't create proxy methods cause what if both, Album and Track would have getTitle() method? I could do some extra processing within Album::getTracklist() method but what's the most simply way to do that? Am I forced do write something like that?

public function getTracklist() {
    $tracklist = array();

    foreach ($this->tracklist as $key => $trackReference) {
        $tracklist[$key] = $trackReference->getTrack();

        $tracklist[$key]->setPosition($trackReference->getPosition());
        $tracklist[$key]->setPromoted($trackReference->isPromoted());
    }

    return $tracklist;
}

// And some extra getters/setters in Track class

EDIT

@beberlei suggested to use proxy methods:

class AlbumTrackReference {
    public function getTitle() {
        return $this->getTrack()->getTitle()
    }
}

That would be a good idea but I'm using that "reference object" from both sides: $album->getTracklist()[12]->getTitle() and $track->getAlbums()[1]->getTitle(), so getTitle() method should return different data based on the context of invocation.

I would have to do something like:

 getTracklist() {
     foreach ($this->tracklist as $trackRef) { $trackRef->setContext($this); }
 }

 // ....

 getAlbums() {
     foreach ($this->tracklist as $trackRef) { $trackRef->setContext($this); }
 }

 // ...

 AlbumTrackRef::getTitle() {
      return $this->{$this->context}->getTitle();
 }

And that's not a very clean way.

Execute raw SQL using Doctrine 2

I want to execute raw SQL using Doctrine 2

I need to truncate the database tables and initialize tables with default test data.

How to re-save the entity as another row in Doctrine 2

Let's say I have entity $e. Is there any generic way to store it as another row, which would have the same entity data but another primary key?

Why I need this: I'm implementing some sort of Temporal Database schema and instead of updating the row I just need to create another one.

Symfony 2 + Doctrine 2 + PHPUnit 3.5: Serialization of closure exception

I tried to find something about this on Google but nothing came out. I have a TestCase class that inherits from WebTestCase, with some methods that I want to use in all my unit/functional tests:

<?php

namespace Application\FaxServerBundle\Test;

use Symfony\Bundle\FrameworkBundle\Test\WebTestCase;
use Doctrine\Common\DataFixtures\Loader;
use Doctrine\Common\DataFixtures\Executor\ORMExecutor;
use Doctrine\Common\DataFixtures\Purger\ORMPurger;

use Application\FaxServerBundle\DataFixtures\ORM\NetworkConfigurationData;

class TestCase extends WebTestCase
{
    protected $kernel;

    public function setUp()
    {
        parent::setUp();
    }

    public function getEm()
    {
        return $this->getService( 'doctrine.orm.entity_manager' );
    }

    public function getNetworkConfigurationRepository()
    {
        return $this->getEm()->getRepository( 'Application\FaxServerBundle\Entity\NetworkConfiguration' );
    }

    public function loadNetworkConfigurationFixtures()
    {
        $loader = new Loader();
        $loader->addFixture( new NetworkConfigurationData() );

        $this->loadFixtures( $loader );
    }

    public function loadFixtures( $loader )
    {
        $purger     = new ORMPurger();
        $executor   = new ORMExecutor( $this->getEm(), $purger );
        $executor->execute( $loader->getFixtures() );
    }

    protected function getService( $name, $kernel = null )
    {
        return $this->getBootedKernel()->getContainer()->get( $name );
    }

    protected function hasService( $name, $kernel = null )
    {

        return $this->getBootedKernel()->getContainer()->has( $name );
    }

    protected function getBootedKernel()
    {
        $this->kernel = $this->createKernel();

        if ( !$this->kernel->isBooted() ) 
        {
            $this->kernel->boot();
        }

        return $this->kernel;
    }

    public function generateUrl( $client, $route, $parameters = array() )
    {
        return $client->getContainer()->get( 'router' )->generate( $route, $parameters );
    }
}

Then, my unit test:

<?php

namespace Application\FaxServerBundle\Tests\Entity;

use Doctrine\ORM\AbstractQuery;

use Application\FaxServerBundle\Entity;
use Application\FaxServerBundle\Test\TestCase;

class NetworkConfigurationRepositoryTest extends TestCase
{
 public function setUp()
 {
  parent::setUp();

  $this->loadNetworkConfigurationFixtures();
 }

 public function testGetConfiguration()
 {
  $config = $this->getNetworkConfigurationRepository()->getConfigurationArray();

  $this->assertInternalType( 'array', $config );
  $this->assertEquals( 6, count( $config ) );
  $this->assertArrayHasKey( 'id', $config );
  $this->assertArrayHasKey( 'ip', $config );
  $this->assertArrayHasKey( 'gateway', $config );
  $this->assertArrayHasKey( 'subnetMask', $config );
  $this->assertArrayHasKey( 'primaryDns', $config );
  $this->assertArrayHasKey( 'secondaryDns', $config );
 }

 public function testGetConfigurationObject()
 {
  $config = $this->getNetworkConfigurationRepository()->getConfigurationObject();

  $this->assertInternalType( 'object', $config );
 }

 public function testGetConfigurationArray()
 {
  $config = $this->getNetworkConfigurationRepository()->getConfigurationArray();

  $this->assertInternalType( 'array', $config );
 }
}

It was working before, but, suddenly, after I updated my vendors (doctrine included), it began to throw this exception:

3) Application\FaxServerBundle\Tests\Entity\NetworkConfigurationRepositoryTest::testGetConfigurationArray
RuntimeException: PHP Fatal error:  Uncaught exception 'PDOException' with message 'You cannot serialize or unserialize PDO instances' in -:32
Stack trace:
#0 [internal function]: PDO->__sleep()
#1 -(32): serialize(Array)
#2 -(113): __phpunit_run_isolated_test()
#3 {main}

Next exception 'Exception' with message 'Serialization of 'Closure' is not allowed' in -:0
Stack trace:
#0 -(0): serialize()
#1 -(113): __phpunit_run_isolated_test()
#2 {main}
  thrown in - on line 0

I've found that the problem comes from the fixture loading. If I remove the code that loads fixtures, it works.

Does anyone know what could be wrong in my code? Is this the best way of loading fixtures?

Thanks!

Symfony2 $user->setPassword() updates password as plain text [DataFixtures + FOSUserBundle]

I'm trying to pre-populate a database with some User objects, but when I call $user->setPassword('some-password'); and then save the user object, the string 'some-password' is stored directly in the database, instead of the hashed+salted password.

My DataFixture class:

// Acme/SecurityBundle/DataFixtures/ORM/LoadUserData.php
<?php

namespace Acme\SecurityBundle\DataFixtures\ORM;

use Doctrine\Common\DataFixtures\FixtureInterface;
use Doctrine\Common\Persistence\ObjectManager;

use Acme\SecurityBundle\Entity\User;

class LoadUserData implements FixtureInterface
{
    public function load(ObjectManager $manager)
    {
        $userAdmin = new User();
        $userAdmin->setUsername('System');
        $userAdmin->setEmail('system@example.com');
        $userAdmin->setPassword('test');

        $manager->persist($userAdmin);
        $manager->flush();
    }
}

And the relevant database output:

id  username    email               salt                                password
1   System      system@example.com  3f92m2tqa2kg8cookg84s4sow80880g     test

Different inheritance types in the same schema

I'm using Doctrine 1.2 on a symfony project, and I'm considering mixing concrete and column aggregation inheritance types in my schema: column aggregation lets me query in a parent table and get both parent and child records, while concrete inheritance lets me get a cleaner schema. Plus, the mix will be in the same inheritance chain. How would I write the schema file? Like the following?

A:

B:
  inheritance:
    extends: A
    type: concrete

C:
  inheritance:
    extends: B
    type: column_aggregation
    keyField:         type
    keyValue:         1

Or like this perhaps:

A:

B:
  inheritance:
    extends: A
    type: concrete

C:
  inheritance:
    extends: B
    type: concrete
D:
  inheritance:
    extends: C
    type: column_aggregation
    keyField:         type
    keyValue:         1


E:
  inheritance:
    extends: C
    type: column_aggregation
    keyField:         type
    keyValue:         2

Are there any dangers/caveats ?

How do I delete an entity from symfony2

My first symfony2 project is a list of guests (invited in an event) stored in a database. I have

• created the entity class Guest with all variables for them (id, name, address, phone number etc.)
• created the schema in the mysql db
• created a route for "adding a guest" to a twig template
• created a formType

and finally a "createGuest" method in the Controller and everything works fine.

I can't manage to remove a guest from the database. I have read every tutorial in the web, including the official Symfony2 book; all that it says is :

Deleting an Object

Deleting an object is very similar, but requires a call to the remove() method of the entity manager:

$em->remove($product);
$em->flush();

It does not say anything more than that (even the "Update an object" section is missing documentation) on how to connect the controller deleteAction($id) with the twig template. What I want to do is to list all guests with a viewGuests action and a viewGuests twig template, having a delete icon next to every row, which you should click to delete an entry. Simple, but I cannot find any documentation and do not know where to start from.

public function deleteGuestAction($id)
    {
        $em = $this->getDoctrine()->getEntityManager();
        $guest = $em->getRepository('GuestBundle:Guest')->find($id);

        if (!$guest) {
            throw $this->createNotFoundException('No guest found for id '.$id);
        }

        $em->remove($guest);
        $em->flush();

        return $this->redirect($this->generateUrl('GuestBundle:Page:viewGuests.html.twig'));
    }

Complex WHERE clauses using the PHP Doctrine ORM

I'm using the PHP Doctrine ORM to build my queries. However, I can't quite seem to figure how to write the following WHERE clause using DQL (Doctrine Query Language):

WHERE name='ABC' AND (category1 = 'X' OR category2 = 'X' OR category3 = 'X') 
AND price > 10

How can I specify where the parentheses go?

What I currently have in my PHP code is this:

->where('name = ?', 'ABC')
->andWhere('category1 = ?', 'X')
->orWhere('category2 = ?', 'X')
->orWhere('category3 = ?', 'X')
->andWhere('price > ?', 10)

But this produces something like

WHERE name='ABC' AND category1 = 'X' OR category2 = 'X' OR category3 = 'X' 
AND price > 10

which, due to order of operations, doesn't return the intended results.

Also, is there a difference between the "where", "andWhere", and "addWhere" methods?

UPDATE Ok, it seems like you can't do complex queries using DQL, so I've been trying to write the SQL manually and use the andWhere() method to add it. However, I'm using WHERE..IN and Doctrine seems to be stripping out my enclosing parentheses:

$q->andWhere("(category1 IN $subcategory_in_clause
    		OR category2 IN $subcategory_in_clause 
    		OR category3 IN $subcategory_in_clause)");

How do I use a complex criteria inside a doctrine 2 entity's repository?

Lets say I have a table that holds information about festivals.
Each festival has a start and end date.

I want to select all the festivals that are live (that happen) on a given date.
Meaning, I want to select all the festivals that their start date is before or on a given date, and that their end date is after or on a the same given date.

So I went on to the repository class of the festival entity, and created a method to do just that.
But the criteria argument "findBy" expects is an array, which all the examples only treat as a simple criteria (eg. "array('name' => 'billy')" will select all the rows that have the value billy in their name column), which uses only the comparison operator.

How can I use other operators such as

>, <, !=, IN, NOT IN, LIKE    

and etc. ?

Thanks