Propel ORM - One-to-many relationship with parent name

Question

Is it possible to create a parent name field into one-to-many relationship in Propel ORM.

This type of relationship uses in CRM systems.

Just imagine that we have a Task List. So, we created a Task #1 and related it to a Project. Task #2 is related to Account (e.g. create a contract). Task #3 is related to Bug Tracker (e.g. fix a bug). So, we have the following relationships:

     task_name    | parent_name  | parent_id
--------------------------------------------------
Start a project   | Project      | <project_id>
Create a contract | Account      | <account_id>
Fix a bug         | Bug Tracker  | <bug_id>

Is it possible to implement in Propel. If no, could you recommend me another ORM with this feature.

The main purpose is to get a list of records with all relationship values.

For my example, it should look like (in JSON):

{
    "Task_0":{"Id":1,"Name":"Start a project","ParentId":1,"ParentName":"Project","Project":{"Id":1,"Name":"Project-1","Tasks":{"Task_0":"*RECURSION*"}}},
    "Task_1":{"Id":1,"Name":"Create a contract","ParentId":1,"ParentName":"Account","Account":{"Id":1,"Name":"Account-1","Tasks":{"Task_0":"*RECURSION*"}}},
    "Task_2":{"Id":1,"Name":"Fix a bug","ParentId":1,"ParentName":"Bug","Bug":{"Id":1,"Name":"Bug-1","Tasks":{"Task_0":"*RECURSION*"}}}
}

Does anyone help me?

Not sure what your question is exactly, is parent_name tied to something externally? Why can't you create that column? — Jordan Kasper
Yes, the parent name is something externally. I modified the question. I think now it should be clear to understand. — user2539253
I think you would have to do something special to get this done in Propel. The foreign key relationship is not set up to add fields other than the parent_id. I guess the question would be why do this versus simply using $task->getParent()->getName(); in your code? — Jordan Kasper

twigmac twigmac · Accepted Answer · 2013-11-11T19:26:17

The output you have shown looks as if the toArray function has been used on the Propel objects and then the json_encode function. This should work if you define foreign keys mutually in Propel's schema.xml.

Since project tasks, account tasks and bug tracker tasks all have something in common, they all are tasks :), I would organize them as sub classes of a more general task entity.

You will end up with a collection of tables like this:

Table "task"

id  | name
------------------------
1   | Start a project
2   | Create a contract
3   | Fix a bug
4   | Start another project
5   | Fix another bug

---------------------------------------

Table "bugtrack_task"

id  | id_task
---------------
1   | 3
2   | 5

---------------------------------------

Table "project_task"

id  | id_task
---------------
1   | 1
2   | 4

---------------------------------------

Table "account_task"

id  | id_task
---------------
1   | 2

In the end, you would define a view in the schema.xml. This could look something like this:

<table name="view_task" phpName="ViewTask" skipSql="true" readOnly="true" description="All my tasks together for display">...</table>

Note that the skipSql attribute has been set to true. This will skip this view table when generating the SQL code. Propel will generate the classes for you but won't touch your database. You can now manually define the view yourself putting into it whatever you desire.

Of course you'd have to put some effort into creating this view but it pays off as you will be able to use the Propel classes like so for instance:

$tasks = ViewTask::create()->find();
$result = array();
foreach($tasks as $task) {
    $result[] = $task->toArray();
}
return json_encode($result);

This isn't a complete answer but I hope you see the idea! Good luck :-)

Propel ORM - One-to-many relationship with parent name

1 Answers