Problem with relationship

[shannah]

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.

[00Davo]

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.

[shannah]

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.

[00Davo]

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

[shannah]

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

[00Davo]

/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]

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.

[shannah]

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


[00Davo]

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.

[shannah]

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/download

I haven't really run this version through the battery of tests, but initial tests looked good.

[rugcutter]

Incidentally we ran into this exact issue. We ended up downgrading PHP from 5.3.1 to 5.2.8.