A place for users and developers of the Xataface to discuss and receive support.
by shannah » Tue Jan 05, 2010 8:35 pm
Currently Xataface works best if a relationship doesn't have duplicate column names - and if they do have duplicate column names, that the two columns are meant to be bound so that their values can be used interchangeably. The simple syntax for relationships unfortunately can't decide which column to treat as the correct column when there are duplicates, so it results in somewhat undefined behavior. If you use SQL to define your relationship you can be much more specific and it will work correctly. e.g. - Code: Select all
[Children] __sql__ = "select student.* from student inner join family on student.ID=family.StudentID and family.ParentID='$ID'"
Hopefully in future versions I'll be able to refine Xataface's intelligence further to be able to deal with duplicate column names.
-
shannah
-
- Posts: 4457
- Joined: Wed Dec 31, 1969 5:00 pm
by 00Davo » Tue Jan 05, 2010 8:40 pm
This is ridiculous. I'm STILL getting problems. I converted those relationships to raw SQL, and now receive an error like this whenever I try to declare the relationship: - Code: Select all
Fatal error: [pear_error: message="Could not add existing related record 'Tennant, David' because it can only belong to a single relationship and it already belongs to one." code=0 mode=return level=notice prefix="" info=""] in C:\xampp\xampp\htdocs\newypt\xataface\Dataface\Application.php on line 1607
The weirdest bit is that I didn't actually select the record 'Tennant, David' as the related record, yet it's complaining about it anyway.
-
00Davo
-
- Posts: 55
- Joined: Sat Jan 02, 2010 9:02 pm
by shannah » Tue Jan 05, 2010 9:21 pm
I thought that might have been a workaround for the duplicate column name limitation but evidently it won't work. The solution, then is to either: a. Don't use duplicate field names in a relationship. (e.g. Instead of calling your ID column just "ID", call it "FamilyID, StudentID, and ParentID" respectively. b. If you cannot change your table column names, create views for your tables that don't have duplicate names and use those instead.
-
shannah
-
- Posts: 4457
- Joined: Wed Dec 31, 1969 5:00 pm
by 00Davo » Tue Jan 05, 2010 10:09 pm
This is very, very annoying. I've uniquifyed all the ID fields, fixed up the .inis to match, and I'm still getting the same error (although it's at least citing the correct record now!) - Code: Select all
Fatal error: [pear_error: message="Could not add existing related record 'McLean, Robyn' because it can only belong to a single relationship and it already belongs to one." code=0 mode=return level=notice prefix="" info=""] in C:\xampp\xampp\htdocs\newypt\xataface\Dataface\Application.php on line 1607
-
00Davo
-
- Posts: 55
- Joined: Sat Jan 02, 2010 9:02 pm
by shannah » Tue Jan 05, 2010 11:47 pm
I have set up your exact same table/relationship structure.. and I have set up a test environment on PHP 5.3.0 and I am not getting these errors. What does your relationship definition/table defs look like now?
-Steve
-
shannah
-
- Posts: 4457
- Joined: Wed Dec 31, 1969 5:00 pm
by 00Davo » Wed Jan 06, 2010 12:36 am
/tables/Student/relationships.ini - Code: Select all
[Parents] __sql__ = "select parent.* from parent inner join family on parent.ParentID=family.ParentID and family.StudentID='$StudentID'" [Class] class.ClassID = "$Class"
/tables/Parent/relationships.ini - Code: Select all
[Children] __sql__ = "select student.* from student inner join family on student.StudentID=family.StudentID and family.ParentID='$ParentID'"
PHPMyAdmin table definitions: - Code: Select all
-- phpMyAdmin SQL Dump -- version 3.2.4 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Jan 06, 2010 at 06:46 PM -- Server version: 5.1.41 -- PHP Version: 5.3.1
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- -- Database: `ypt` --
-- --------------------------------------------------------
-- -- Table structure for table `student` --
CREATE TABLE IF NOT EXISTS `student` ( `StudentID` int(11) NOT NULL AUTO_INCREMENT, `LName` varchar(50) DEFAULT NULL, `FName` varchar(50) DEFAULT NULL, `DOB` date DEFAULT NULL, `HouseNo` tinyint(4) DEFAULT NULL, `Street` varchar(50) DEFAULT NULL, `Suburb` varchar(50) DEFAULT NULL, `Postcode` int(11) DEFAULT NULL, `Phone` varchar(20) DEFAULT NULL, `Mobile` varchar(20) DEFAULT NULL, `Email` varchar(50) DEFAULT NULL, `EnrolmentStatus` enum('WL','E','WD') DEFAULT NULL, `SchoolYear` year(4) DEFAULT NULL, `WaitingListPlace` int(11) DEFAULT NULL, `WaitingListDate` date DEFAULT NULL, `ClassPlacementDate` date DEFAULT NULL, `ClassPlacementAcceptance` tinyint(1) DEFAULT NULL, `ClassEnrolmentDate` date DEFAULT NULL, `EnrolmentYear` year(4) DEFAULT NULL, `Class` int(11) DEFAULT NULL, `Status` enum('S','A') DEFAULT NULL, `FeesPaid` double DEFAULT NULL, `ReceiptNo` int(11) DEFAULT NULL, `PaymentMethod` enum('dd','cc','c','cqe') DEFAULT NULL, `Productions` text, `Involvement` text, PRIMARY KEY (`StudentID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
-- -- Table structure for table `parent` --
CREATE TABLE IF NOT EXISTS `parent` ( `ParentID` int(11) NOT NULL AUTO_INCREMENT, `LName` varchar(50) DEFAULT NULL, `FName` varchar(50) DEFAULT NULL, `HouseNo` int(11) DEFAULT NULL, `Street` varchar(50) DEFAULT NULL, `Suburb` varchar(50) DEFAULT NULL, `Postcode` int(11) DEFAULT NULL, `Phone` varchar(20) DEFAULT NULL, `Mobile` varchar(20) DEFAULT NULL, `Email` varchar(50) DEFAULT NULL, `YPTReceiptNo` int(11) DEFAULT NULL, `LSPReceiptNo` int(11) DEFAULT NULL, `YPTFeePaid` double DEFAULT NULL, `LSPFeePaid` double DEFAULT NULL, `Involvement` text, PRIMARY KEY (`ParentID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
-- -- Table structure for table `family` --
CREATE TABLE IF NOT EXISTS `family` ( `FamilyID` int(11) NOT NULL AUTO_INCREMENT, `ParentID` int(11) DEFAULT NULL, `StudentID` int(11) DEFAULT NULL, PRIMARY KEY (`FamilyID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;
-
00Davo
-
- Posts: 55
- Joined: Sat Jan 02, 2010 9:02 pm
by 00Davo » Wed Jan 06, 2010 12:42 am
shannah wrote:I have set up a test environment on PHP 5.3.0 and I am not getting these errors.
Um - mine is PHP 3.5.1, since I upgraded to solve a previous problem getting this DB working.
-
00Davo
-
- Posts: 55
- Joined: Sat Jan 02, 2010 9:02 pm
by shannah » Wed Jan 06, 2010 1:16 am
I have made some changes that should allow xataface to support duplicate columns in relationships. Preliminary testing looks promising... I'll be uploading a dev version later today. With respect to your relationships, now that you have changed your column names, you should change your relationship definitions back to the way they were originally (except with proper case in table names). E.g. here are my relationships: tables/student/relationships.ini - Code: Select all
[Parents] parent.ParentID = family.ParentID family.StudentID = "$StudentID"
tables/parent/relationships.ini - Code: Select all
[Children] family.ParentID = "$ParentID" student.StudentID = family.StudentID
-
shannah
-
- Posts: 4457
- Joined: Wed Dec 31, 1969 5:00 pm
by 00Davo » Wed Jan 06, 2010 1:20 am
Changing my relationships.inis as you just described, the student-parent relationship now seems to work perfectly. That, somehow, seems to have solved the issue. Thanks for all the assistance.
-
00Davo
-
- Posts: 55
- Joined: Sat Jan 02, 2010 9:02 pm
by shannah » Wed Jan 06, 2010 1:44 am
On a side note, I have uploaded version 1.2.3b2 which adds preliminary support for duplicate keys (i.e. with this version your relationships would have worked without having to change anything... https://sourceforge.net/projects/datafa ... z/downloadI haven't really run this version through the battery of tests, but initial tests looked good.
-
shannah
-
- Posts: 4457
- Joined: Wed Dec 31, 1969 5:00 pm
by rugcutter » Fri Feb 19, 2010 1:27 pm
Incidentally we ran into this exact issue. We ended up downgrading PHP from 5.3.1 to 5.2.8.
-
rugcutter
-
- Posts: 11
- Joined: Thu Apr 23, 2009 9:43 pm
Return to Xataface Users
Who is online
Users browsing this forum: No registered users and 25 guests
|