many-to-many relationship

[fantomasdm]

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]

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]

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

[fantomasdm]

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]

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]

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

[fantomasdm]

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]

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]

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

Code: Select all

[iddirectories]
Key=PRI
Extra = auto_increment

Now all works!

[shannah]

Ahh.... thanks for posting this.