PHP ORMs and optimized relationship queries

Question

I need a PHP ORM to work with relations well. Please consider code below in Zend:

$persons = new Persons();
$person = $persons->find(5)->current();
echo 'Name: '.$person->fullname;

$phones = $person->findDependentRowset('Phones');
foreach($phones as $phone)
    echo 'Phone: '.$phone->phonenumber; 

Or code below in xPDO:

$person = $xpdo->getObject('Persons', 5);
echo 'Name: '.$person->get('fullname');

$phones = $person->getMany('Phones');
foreach($phones as $phone)
    echo 'Phone: '.$phone->get('phonenumber');

in both scripts, ORMs executes two queries as below:

SELECT * FROM persons WHERE id=5;
SELECT * FROM phones WHERE person=5;

It means one query for main object and one query for each relation but what i need is using ONE query for main object and its relations! xPDO can do it as below:

$person = $xpdo->getObjectGraph('Persons', '{"Phones":{}}', 5);
echo 'Name: '.$person->get('fullname');

foreach($person->Phones as $phone)
    echo 'Phone: '.$phone->get('phonenumber');

which executes this query:

SELECT * FROM persons
LEFT JOIN phones ON phones.person=persons.id
WHERE persons.id=5

This is great but its not possible to set fields to get from tables! it means in this case xPDO use "SELECT * " so if i get an object and its 4 relations, i will get all fields of all these tables!

So i need an ORM to execute query below for example above:

SELECT persons.fullname , phones.phonenumber FROM persons
LEFT JOIN phones ON phones.person=persons.id
WHERE persons.id=5

Doctrine can do it via DQL but i think Doctrine is not good for personal projects. Is there any PHP ORM to do this?

Thanks AHHP

Answer 1

Oh Man,

I have been eating this dog food for about 2 months, and I love it. RED BEAN.

http://www.redbeanphp.com/

Nested beans, which is their words for having one object as a property of another.

http://www.redbeanphp.com/manual/nested_bean

Whole file is pretty small. Works with SQL. I am using it on a massive project and loving how quickly I can get things done.

John.

Answer 2

xPDO can do it. You just have to adjust your thinking and your request.

Remember the objectGraph utilizes the class name of the object, where as the relationships in the graph are used in the graph and query.

 $criteria = $this->xpdo->newQuery('prefixClient');
            if (!empty($limit))
                $criteria->limit($limit);
            $criteria->where(array('Child.planid' => $this->getPrimaryKey(),
                                   'Child.active' => true,
                                   'GrandChild.someAttribute' => true,
                                   'GreatGrandChild.someOtherAttribute' => true,
                                   'suspended' => false
                             ));

            $out = $this->xpdo->getCollectionGraph('prefixClient', '{"Child":{"GrandChild":{"GreatGrandChild":{}}}}', $criteria);

You set the WHERE on any aspect of the relation, including the current object as show in the suspended => false line.

My book might help you a little in establishing your schema and relations. Objects should always be singular in nomenclature, whereas their relations can be plural (1:M) or singular (1:1).

Answer 3

There are several ways that Gacela can handle automatically fetching related information:

1) Inheritance relationship

In an example like the following:

CREATE TABLE wizards (
    wizardId INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    fname VARCHAR(255) NOT NULL,
    lname VARCHAR(255) NOT NULL,
    ROLE ENUM('teacher', 'student') NULL,
    addressId INT UNSIGNED NULL,
    CONSTRAINT fk_address_wizard
    FOREIGN KEY (addressId)
    REFERENCES addresses(addressId)
    ON DELETE SET NULL
) ENGINE = Innodb;

CREATE TABLE students (
    wizardId INT UNSIGNED NOT NULL PRIMARY KEY,
    houseId INT UNSIGNED NOT NULL,
    isDAMembmer BOOL NOT NULL DEFAULT 0,
    CONSTRAINT fk_wizard_student
    FOREIGN KEY (wizardId)
    REFERENCES wizards(wizardId)
    ON DELETE CASCADE,
    CONSTRAINT fk_house_students
    FOREIGN KEY (houseId)
    REFERENCES houses(houseId)
    ON DELETE RESTRICT
) ENGINE = Innodb;

The students table has the same primary key as the wizards table and because of the foreign key relationship definition, Gacela will detect that students inherits all of the fields from wizards.

2) Dependent relationship

This is probably closer to what you're looking for:

CREATE TABLE addresses (
    addressId INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    locationName VARCHAR(255) NOT NULL
) ENGINE = Innodb;

CREATE TABLE wizards (
    wizardId INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    fname VARCHAR(255) NOT NULL,
    lname VARCHAR(255) NOT NULL,
    ROLE ENUM('teacher', 'student') NULL,
    addressId INT UNSIGNED NULL,
    CONSTRAINT fk_address_wizard
        FOREIGN KEY (addressId)
        REFERENCES addresses(addressId)
        ON DELETE SET NULL
) ENGINE = Innodb;

However, this example still differs slightly from your example because the addressId is in the wizards table thus creating a belongsTo relationship rather than a hasMany which is what your example reflects.

There is a third option available in Gacela, however I would first suggest that you consider that while it is often preferable to fetch data eagerly, there are real performance implications in always fetching eagerly rather than lazy loading data. It is for this reason that Gacela (and basically every other Data Mapper or ORM) by default does not eagerly fetch data from hasMany relationships. That said, you could write something like the following:

class Mapper extends \Gacela\Mapper 
{

    public function find($id)
    {
        $query = $this->_source()->query();

        $query->from('persons', array('fullName'))
            ->join('phones', 'phones.person=persons.id', array('phonenumber'), 'left')
            ->where('persons.id = :id', array(':id' => $id);

        $data = $this->_source->query($query);

        return $this->_load($data);
    }
}