Metafields:order = "ORDER" error.

[Aoirthoir]

If I try the order fields, I get an error as follows:

Fatal error: Cannot use object of type PEAR_Error as array in /ourpath/libraries/dataface/SQL/Parser/wrapper.php on line 272

Just checkin in case that's an error on my end or still something to wait for. No rush..thanks bro.

[shannah]

Can you post the relevant portions of the relationships.ini file so I can take a look?

-Steve

[Aoirthoir]

I can bro. I will have to do it tomorrow when I am more awake. Been workin all day. Might work some tomorrow also. I could also give you access directly if you would like. Might make pokin around at some of this stuff a bit easier.

[Aoirthoir]

I have upgraded to Dataface 0.6.3r2

Ok, here is the current state of the tables and relationships.ini I am trying to set up. I had a Projects table and a Tasks table, as well as a many to many relationships table set up. However as we examined it, we realized the projects were really just tasks. So I am trying to create task dependencies. I will have to do some code that wont let a task be set as finished if it has child tasks that are not done yet. But that will come later. So now I am trying to set up a Self Mapping table. However, the same basic code also was not working when I was using the Projects table as well. Thus I will show you how it was set up with the three tables first. (Projects, tasks, relations). Then it is basically the same relationships.ini for a tasks/relations table set up. So within the tables/D_TASK/relationships.ini I have a [D_TASK] section.

Here are the tables and relationships.ini files:

--------------------------------------
CREATE TABLE `D_PROJECT` (
`ID` mediumint( 8 ) unsigned zerofill NOT NULL auto_increment,
`TITLE` varchar( 40 ) NOT NULL,
`DESCRIPTION` varchar( 80 ) default NULL,
`COMMENT` text,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
---------------------------------------

The tasks table looks like this:

---------------------------------------
CREATE TABLE `D_TASK` (
`ID` mediumint( 8 ) unsigned zerofill NOT NULL auto_increment,
`TITLE` varchar( 40 ) NOT NULL,
`DESCRIPTION` varchar( 80 ) default NULL,
`PRIORITY` enum('URGENT','High','Medium','Low','Whenever','Just a Brain Storm','Nevermind') default NULL,
`STATUS` enum('Assigned','Under Consideration','Started','Halfway','Almost Done','Completed','On Going','Repeating','Delayed','Requires Outside Help','Postponed','Cancelled') default NULL,
`ID_USER_CREATED` mediumint( 8 ) unsigned zerofill default NULL,
`ID_USER_ASSIGNED` mediumint( 8 ) unsigned zerofill default NULL,
`DATE_PROJECTED_START` date default NULL,
`DATE_PROJECTED_FINISH` date default NULL,
`DATE_ACTUAL_START` date default NULL,
`DATE_ACTUAL_FINISH` date default NULL,
`COMMENT` text,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
--------------------------------------

While the Relationships table looks like this:

--------------------------------------
CREATE TABLE `R_` (
`PID` mediumint( 8 ) unsigned zerofill NOT NULL,
`CID` mediumint( 8 ) unsigned zerofill NOT NULL,
`ORDER` mediumint( 8 ) unsigned zerofill NOT NULL,
PRIMARY KEY (`PID`,`CID`) [or PRIMARY KEY ( `PID`,`CID`,`ORDER` )]
) ENGINE=MyISAM DEFAULT CHARSET=latin1
--------------------------------------
While my task to task relation file looks like this:
--------------------------------------
CREATE TABLE `R_2` (
`PID` mediumint( 8 ) unsigned zerofill NOT NULL,
`CID` mediumint( 8 ) unsigned zerofill NOT NULL,
`ORDERTASK` mediumint( 8 ) unsigned zerofill NOT NULL,
PRIMARY KEY (`PID`,`CID`) [or PRIMARY KEY ( `PID`,`CID`,`ORDER` )]
) ENGINE=MyISAM DEFAULT CHARSET=latin1
--------------------------------------

tables/D_PROJECT/relationships.ini:
--------------------------------------
[D_TASK]
action:label = Child Tasks
action:description = Other tasks which must be completed before this task/project can be marked done.
D_TASK.ID=R_.CID
R_.PID="$ID"
metafields: order = "ORDER" (space after the colon to prevent smilies)
-------------------------------------
tables/D_TASK/relationships.ini
-------------------------------------
[D_TASK]
action:label = Child Tasks
action:description = Other tasks which must be completed before this task/project can be marked done.
D_TASK.ID=R_2.CID
R_2.PID="$ID"
metafields: order = "ORDERTASK" (space after the colon to prevent smilies)
-------------------------------------

Ok, now if I am on a project table accessing the tasks tab, and I add a task to the relationship or, once Ive added a task and I try to view the relationships, I get this error:
-------------------------------------
Fatal error: Cannot use object of type PEAR_Error as array in /home/.chase/riohtrioa/gwenyfer.com/amss.gwenyfer.com/apps.amss.gwenyfer.com/libraries/dataface/SQL/Parser/wrapper.php on line 272
-------------------------------------

Also of course it displays the ORDER field. If I use widget:type = hidden then I will get a pop up stating that the field is required. If I set the value to null, it allows me to add the record with the fatal error listed above. If I set the value to not null and the default to zero I get the same fatal error. And lastly if I set the default to one I get the same fatal error. Also if I set the ORDER field to be a component of the primary key I get the same fatal error.

Now all of that is if I am running from D_PROJECT to D_TASK. If I am running a self mapping relationship it is the same fatal error. However, I do not get the link, add an existing record to this relationship. I only get add a new record. When I click it I get no fields, just the save button. When I click the save button I get the above error.

The error is the same no matter the order of my relationships.ini file.

Also I tested each of the independantly, with each turned off by using ; when I was testing the other. Just to make sure there were no conflicts.

Changing the name of the fields or the table names ( R_ for instance) makes no difference)

So the gist is, I can do many to many relationships with the D_PROJECT to D_TASK by way of R_. However I cannot do ordered relationships in that manner. And I cannot do self mapping relationships at all.

Lastly, when I examine the records for the R_ and R_2 tables, the relationships were added accurately.

And if it is possible to use one set of R_ for all of my relationships using the Table names method you used before and also having an order, that would be fantastic. So the relationships.ini would become something like:

---------------------------------
[D_TASK]
action:label = Child Tasks
action:description = Other tasks which must be completed before this task/project can be marked done.
R_PNAME="D_PROJECT"
R_CNAME="D_TASK"
D_TASK.ID=R_.CID
R_.PID="$ID"
metafields: order = "ORDERTASK" (space after the colon to prevent smilies)
---------------------------------
With the appropriate fields having been added to the R_ table structure.


Hope this provides some useful information.


Thank you kindly for your consideration

[shannah]

Hi Aoirthoir,

Looks like it's objecting to the name of the order field being 'order' as this is a reserved word in the mysql parser. (certainly this shouldn't be a problem so it is a bug).

I am onto this error, and will try to fix it for the next release. For now, if it isn't too much trouble to rename the order field, that should get you going.

Best regards

Steve

[Aoirthoir]

Ok I changed the ORDER field to OID. Which makes sense considering the naming convention I am using already. I set the default to 1. Nulls not allowed. And in /tables/R_/fields.ini I set [OID] widget:type hidden.

I tested it with both setups. That is to say the D_PROJECT -> D_TASK setup. Also the D_TASK -> D_TASK setup. It worked fine with the D_PROJECT -> D_TASK setup. The D_TASK -> D_TASK setup did not provide a ADD CURRENT to the relationship link. So for now I will stick with one table to another. If there is a way to do self mapping many to many relationships, I will do that. Also, if there is such a way, I would like to create two tabs. One for child relations. The other for parent relations. So the tables/D_TASK/relationships.ini would look like this: (this isnt a dataface thing yet, just a thought)

---------------------------------------------------------------
[D_TASK]
action:label = Child Tasks
ACTION:RELATIONSHIP = CHILD ??? (something like that)
action:description = Other tasks which must be completed before this task/project can be marked done.
D_TASK.ID=R_.CID
R_.PID="$ID"
metafields: order = "OID" (space after the colon to prevent smilies)

[D_TASK]
action:label = Parent Tasks
ACTION:RELATIONSHIP = PARENT ??? (something like that)
action:description = Other tasks which must be completed before this task/project can be marked done.
D_TASK.ID=R_.PID >>> Notice the change for the fields here....from R_.CID to R_.PID
R_.CID="$ID" >>> Notice the change for the fields here....from R_.PID to R_.CID
metafields: order = "OID" (space after the colon to prevent smilies)
-------------------------------------------------------

I dont know if this is possible in a future update or not. Actually I do not know if self mapping relationships are possible.

Also a thought for the future, what if Dataface has permissions to create a table? We could make the relationships as simple as:

------------------------------------------------------
[D_TASK}
relationship:type = manytomany/onetomany/onetoone/manytoone
relationship:parenttab = true/false
relationship:childtab = true/false
relationship: order = true/false (space to prevent smilies
------------------------------------------------------

Then dataface could handle create the needed relationship table and manage it. This I assume is a ways away.

[Aoirthoir]

Ok, good news and not good news. Not really bad though. I added two fields to my R_ table, PNAME and CNAME (P will be Parent and C child in the R_ relationship tables). Thus the table create look slike this:

-----------------------------------------------------
CREATE TABLE `R_` (
`PNAME` varchar( 10 ) NOT NULL,
`CNAME` varchar( 10 ) NOT NULL,
`PID` mediumint( 8 ) unsigned zerofill NOT NULL,
`CID` mediumint( 8 ) unsigned zerofill NOT NULL,
`OID` mediumint( 8 ) unsigned zerofill NOT NULL default '00000001',
PRIMARY KEY (`PNAME`,`CNAME`,`PID`,`CID`,`OID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
------------------------------------------------------

tables/D_PROJECT/relationships.ini looks like this:
------------------------------------------------------
[D_TASK]
action:label = Child Tasks
action:description = Other tasks which must be completed before this task can be marked done.

__sql__ = "
SELECT * FROM R_, D_TASK
WHERE R_.PNAME='D_PROJECT'
AND R_.CNAME='D_TASK'
AND D_TASK.ID = R_.CID
AND R_.PID='$ID'"

metafields: order = "OID"
-------------------------------------------------------

tables/D_TASK/relationships.ini looks like this:
------------------------------------------------------
[D_TASK]
action:label = Child Tasks
action:description = Other tasks which must be completed before this task can be marked done.

__sql__ = "
SELECT * FROM R_, D_TASK
WHERE R_.PNAME='D_TASK'
AND R_.CNAME='D_TASK'
AND D_TASK.ID = R_.CID
AND R_.PID='$ID'"

metafields: order = "OID"
-------------------------------------------------------

So that little bit of SQL allows for many to many relationships really from any table, to any table. However it does not automatically generate the OID (order ID) needed for dataface to move items up and down in a list. And while I like that feature, honestly, being able to have self mapping tables, makes me pretty happy.