many-to-many relationship

Archived from the Xataface Users forum.

fantomasdm — Mon May 31, 2010 6:18 am

Hi, it possible to use an relationship many to many on same tables?
I have a table:

Code: Select all

CREATE TABLE `directories` (   `iddirectories` bigint(20) NOT NULL AUTO_INCREMENT,   `datacreazione` datetime DEFAULT NULL,   `utentecreazione` int(11) DEFAULT NULL,   `datamodifica` datetime DEFAULT NULL,   `utentemodifica` int(11) DEFAULT NULL,   `directory_name` varchar(64) COLLATE latin1_general_ci DEFAULT NULL,   `note` text COLLATE latin1_general_ci,   PRIMARY KEY (`iddirectories`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

and realtionship.ini:

Code: Select all

[Sotto Cartelle] dirdir.padre="$iddirectories" dirdir.figlio=directories.iddirectories

and in fields.ini:

Code: Select all

[SottoCartelle] transient=1 relationship="Sotto Cartelle" widget:type=checkbox

---

shannah — Mon May 31, 2010 4:26 pm

Currently the relationship code doesn’t like relationships to the same table (they work to a certain extent but it’s a little flaky when it comes to adding records to those relationships). One workaround I often use is to create a view on the table, then have the table relate to that view, instead of the table itself.

---

fantomasdm — Tue Jun 01, 2010 1:09 am

HI, Thanks! I have create view for directories table and in fields.ini add
[iddirectories]
Key=PRI
for primary key!

---

fantomasdm — Tue Jun 01, 2010 3:34 am

Hi! When I try to add new relative record or Add Existing Related Record, nothing was added!!
the code is:
relationship.ini:

Code: Select all

`[Files]
dirfile.dirpadre=”$iddirectories”
dirfile.filefiglio=files.idfiles

[Sotto Cartelle]
dirdir.padre=”$iddirectories”
dirdir.figlio=directoriesV.iddirectoriesV`

table:

Code: Select all

`` CREATE TABLE directories (
  iddirectories bigint(20) NOT NULL AUTO_INCREMENT,
  datacreazione datetime DEFAULT NULL,
  utentecreazione int(11) DEFAULT NULL,
  datamodifica datetime DEFAULT NULL,
  utentemodifica int(11) DEFAULT NULL,
  directory_name varchar(64) COLLATE latin1_general_ci DEFAULT NULL,
  note text COLLATE latin1_general_ci,
  PRIMARY KEY (iddirectories)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW directoriesv AS (
select  directories.iddirectories AS iddirectoriesV,  directories.datacreazione AS datacreazione,  directories.utentecreazione AS utentecreazione,  directories.datamodifica AS datamodifica,  directories.utentemodifica AS utentemodifica,  directories.directory_name AS directory_name,  directories.note AS note from directories)

CREATE TABLE dirdir (
  padre int(11) NOT NULL DEFAULT ‘0’,
  figlio int(11) NOT NULL DEFAULT ‘0’,
  PRIMARY KEY (padre,figlio)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE dirdir (
  padre int(11) NOT NULL DEFAULT ‘0’,
  figlio int(11) NOT NULL DEFAULT ‘0’,
  PRIMARY KEY (padre,figlio)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ``

Relationship [Files] works good! Only relationship [Sotto Cartella] doesn’t work!

---

fantomasdm — Thu Jun 03, 2010 3:27 am

Hi, I try to change relationships.ini in:

Code: Select all

[Sotto Cartelle] __sql__ = "SELECT * FROM directoriesv INNER JOIN dirdir  ON dirdir.figlio = directoriesv.iddirectoriesV WHERE dirdir.padre = '$iddirectories'"

but don’t woks again!!

remove working…

---

fantomasdm — Thu Jun 03, 2010 5:33 am

an other specification function beforeAddNewRelatedRecord(&$record) and function afterAddRelatedRecord(&$record) is never call.
P.S. Sorry for my English, but I’m Italian!

---

fantomasdm — Fri Jun 04, 2010 1:44 am

HI, again I!!
I have enable transaction log on mysql, but when add new relative record, or new existing relative record nothing was logging!!
(all other operations is visible!)
Sorry for my English, but isn’t my fault, I’m Italian!!

---

fantomasdm — Tue Jun 08, 2010 3:57 am

Hi!! I found the problem, in relationship.ini if the name of relation have space, add new or add exist relative record don’t work!!
But now I have a new problem, when add new relative record on a views, for simulate a many to many on same table, system can’t find primary key,
I have add in fields.ini directive:

Code: Select all

[iddirectories] Key=PRI

the error is:
Catchable fatal error: Object of class Dataface_Relationship_ForeignKey could not be converted to string in C:\Program Files\wamp\www\xataface-1.2.4\Dataface\QueryBuilder.php on line 411

but field iddirectories of new relative record form isn’t filler with parent id!!
relationship.ini:

Code: Select all

[SottoCartelle] dirdir.padre = "$iddirectories" dirdir.figlio = directoriesV.iddirectories

I hope someone can understand what I have write!!

---

fantomasdm — Wed Jun 09, 2010 3:37 am

Hi, I have found solution, in fieds.ini:

Code: Select all

[iddirectories] Key=PRI Extra = auto_increment

Now all works!

---

shannah — Wed Jun 09, 2010 8:57 am

Ahh…. thanks for posting this.