Add new related record for table with calculated fields

[ppd83]

Hi,

I have a table (lets call it table1). In fields.ini for that table I have a rather complicated query (with a couple of subqueries) for a number of calculated fields (http://xataface.com/documentation/how-t ... tails-view).

I have another table (called table2) which is in a relatiosnhip with table1. In relationships.ini for table2 I have:

Code: Select all

[table1]
__sql__ = "select * from table1 where table1.id='$id'"

The problem is that when I want to add a new related table1 record from table2 view I get the following warning (and no input fields are shown - just the "save" button):

Code: Select all

Warning: reset() [function.reset]: Passed variable is not an array or object in /opt/lampp/htdocs/ule/xataface/Dataface/ShortRelatedRecordForm.php on line 274

I tried 2 things.

1. Change relatiosnhip.ini for table2 into:

Code: Select all

[table1]
__sql__ = "<<the same complicated query from fields.ini for table1... where table1.id='$id'>>'"

But the parser failed to interpret that.

2. Created a view for my complicated query and changed relatiosnhip.ini for table2 into

Code: Select all

[table1]
__sql__ = "select * from my_view where my_view.id='$id'"

But this way I get an error saying that the view has no primary key.

How can I get around this problem?

Thanks.

Paul.

[shannah]

What version os PHP,Xataface, MySQL are you using?

[ppd83]

Xataface 1.2.3b2
PHP 5.2.8
MySQL 5.1.30

[shannah]

Can you post the relevant table definitions and the associated fields.ini and relationships.ini files so I can try to reproduce the error?

[ppd83]

Hi Steve

I've created a small example. This is the setup.

The database:

Code: Select all

--
-- Table structure for table `class`
--

CREATE TABLE IF NOT EXISTS `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `class`
--

INSERT INTO `class` (`id`, `name`) VALUES
(1, 'Class1');

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

--
-- Table structure for table `student`
--

CREATE TABLE IF NOT EXISTS `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_id` int(11) NOT NULL,
  `field1` int(11) NOT NULL,
  `field2` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `student`
--

INSERT INTO `student` (`id`, `class_id`, `field1`, `field2`) VALUES
(1, 1, 1, 1);

The conf.ini:

Code: Select all

;;Configuration settings for application
title="test"

[_database]
   host="localhost"
   name="test"
   user="root"
   password=""
   
[_tables]
class="classes"
student="students"

relationships.ini from tables/class:

Code: Select all

[student]
  __sql__="select * from student where student.class_id='$id'"

fields.ini from tables/student:

Code: Select all

__sql__="select id, class_id, field1, field2, (field1+field2) as somesum from student"

Regards,

Paul.

[ppd83]

Did you manage to reproduce the error?

[shannah]

Your example seems to work well in my development version. If you update to 1.2.3b1 it should work for you too.

[shannah]

OK.. I have posted a fix for this:
http://weblite.ca/svn/dataface/core/tru ... /Table.php


Index: Table.php
===================================================================
--- Table.php (revision 1726)
+++ Table.php (revision 1727)
@@ -2044,6 +2044,7 @@
foreach ( $data['columns'] as $col ){
if ( $col['type'] != 'glob' ){
$alias = ( $col['alias'] ? $col['alias'] : $col['value']);
+ if ( isset($this->_fields[$alias]) ) continue;
$this->_grafted_fields[$alias] = $this->_newSchema('varchar(32)', $alias);
$this->_grafted_fields[$alias]['grafted']=1;
if ( isset($this->_atts[$alias]) and is_array($this->_atts[$alias]) ){

[ppd83]

Thank you very much, Steve!

[ADobkin]

FYI, I am getting the exact same error (ShortRelatedRecordForm.php line 274) in version 1.2.6. I noticed it came up after making some changes to the fields.ini for a new table, so I started backing out my changes and found that the problem lies with the group setting in this particular field:

[contactStatus]
group = internal

If I take out the group option for this field, then it does not display this error. I cannot figure out an explanation for why this is happening, since there are other fields in that group and this field is just a simple varchar(64).

The table that this field has a many-to-many relationship, and the error occurs when adding a new related record from the other table. If I create the record directly in this table, the error does not occur. Very odd....

Alan

[ADobkin]

I've spent some time trying to troubleshoot this error further. I still don't know why it is happening, but I have a better understanding of when it happens.

First, it has nothing to do with the particular field I mentioned earlier, or even that table. Apparently, the error occurs in my other tables that have many-to-many relationships as well when I use the "add new (related) record" option from the related table tab. The solution in each case is to disable the group option for the last field in the last fieldgroup definition in fields.ini. All I have to do is comment out the "group = xxx" line and the error goes away. So, it seems like this may be a bug in Xataface where it is not parsing the last fieldgroup field properly.