Why will Doctrine ORM will not create SQL query for my 1-to-many model?

Question

I know this has to have an easy answer, but I cannot figure it out. After tears, I am hoping someone here can help.

Here is my YML model:

Identity:
  columns:
    id:
      type:           integer(10)
      primary:        true
      autoincrement:  true
    username:
      type:           string(255)

Profile:
  columns:
    id:
      type:           integer(10)
      primary:        true
      autoincrement:  true
    identity_id:
      type:           integer(10)
    profiletype_id:
      type:           integer(10)
    name:
      type:           string(255)
  relations:
    Identity:
      local:          identity_id
      foreign:        id
      class:          Identity
      foreignAlias:   Profiles
    Profiletype:
      local:          profiletype_id
      foreign:        id
      class:          Profiletype
      foreignAlias:   Profiles

Profiletype:
  columns:
    id:
      type:           integer(10)
      primary:        true
      autoincrement:  true
    type:
      type:           string(255)

As you can see, 3 linked tables are generated:

Identity
- can have multiple Profiles

Profile
- has one Identity
- has one Profiletype

Profiletype
- can have multiple Profiles

Now, In my code, I can perform queries on the generated models for Identity and Profiletype.

For example:

        $q = Doctrine_Query::create()
          ->select('i.*')
          ->from('Identity i');
        echo $q->getSqlQuery();

will work and produce:

SELECT i.id AS i__id, i.username AS i__username FROM identity i

However, when I go to run any query on the Profile table, it will not work. Even a simple query such as

        $q = Doctrine_Query::create()
          ->select('p.*')
          ->from('Profile p');
        echo $q->getSqlQuery();

fails. I have tried changing the class name in the FROM to 'Profiles' instead of 'Profile'. Still nothing.

Any ideas on what I'm doing wrong.

hmm... can't reproduce your error. what kind of error are you getting? I assume you're using mysql. have you tried a less verbose schema definition, i.e: don't define the ids for your models and only define the foreignAlias for your relations. shouldn't matter, but worth a go. — Darragh Enright, Aug 18 '10 at 08:25
The thrown error would be usefull. Is there any custom code in the ´Base` models (or somewhere else) that might get called? — DrColossos, Aug 18 '10 at 16:45
@Darragh: yes, am using mysql. Have tried various schemas. I usually am much less verbose in my definition, however, given that I was tearing my hair out I tried spelling things out fully in the schema. Any other ideas? — So Over It, Aug 18 '10 at 23:37
@DrColossos: there is no custom code in the 'Base' models. I am using Doctrine in the context of the CodeIgniter framework. However, I have had models running in CI/Doctrine in the past - so I was thinking it would be more my model rather than CI causing the problem (especially given that I can call simple queries on Identity and Profiletype). — So Over It, Aug 18 '10 at 23:40
That's wirded, I use Doctrine with Codeiginiter myself but never had a problem. Have you examinded the created tables in the database if they are correctly setup? — DrColossos, Aug 19 '10 at 05:58

score 0 · Accepted Answer · answered Aug 20 '10 at 06:08

After changing every possible variable and tracing through my model line-by-line, I came to an embarrassing discovery:

The controller I was calling the Doctrine Query from was called profile.php.

profile.php

<?php
class Profile extends Controller {

    function Profile()     {
        parent::Controller();
    }

    function index()     {
        $q = Doctrine_Query::create()
            ->from('Profile');
        echo $q->getSqlQuery();
    }

}

Ass soon as I named the controller something else other than 'profile', it all suddenly worked. Eg, naming it to docile.php:

docile.php

<?php
class Docile extends Controller {

    function Docile()     {
        parent::Controller();
    }

    function index()     {
        $q = Doctrine_Query::create()
            ->from('Profile');
        echo $q->getSqlQuery();
    }

}

'Profile' does not appear to be a reserved word in CodeIgniter (link text). It does appear, however, that you cannot call a doctrine 'table class' from a controller class of the same name.

Thanks for your input guys. Hope this saves someone else similar hassles.

Why will Doctrine ORM will not create SQL query for my 1-to-many model?

1 Answers1