Join table with vocabulary

Archived from the Xataface Users forum.

Gwynnbleid1 — Tue Nov 27, 2012 1:11 am

Hi there!

I have a strange problem. I have two three tables defined like this:

Code: Select all
CREATE TABLE IF NOT EXISTS `komputery` (   `Id` int(11) NOT NULL AUTO_INCREMENT,   `Oddzial` int(11) DEFAULT NULL,   `Dzial` int(11) DEFAULT NULL,   `Lokalizacja` int(11) DEFAULT NULL,   `Typ` enum('Stacjonarny','Laptop') COLLATE utf8_polish_ci DEFAULT NULL,   `Nazwa` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,   `Model` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,   `Procesor` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,   `Ram` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,   `Dysk` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,   `Grafika` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,   `Naped` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,   `NumerInwentarzowy` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,   `PN` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,   `SN` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,   `Dostawca` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,   PRIMARY KEY (`Id`),   KEY `fk_Komputery_Oddzial1_idx` (`Oddzial`),   KEY `fk_Komputery_Dzial1_idx` (`Dzial`),   KEY `fk_Komputery_Lokalizacja1_idx` (`Lokalizacja`) ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=3 ;
Code: Select all
`` CREATE TABLE IF NOT EXISTS komputery_oprogramowanie (
  Id int(11) NOT NULL AUTO_INCREMENT,
  KomputerId int(11) DEFAULT NULL,
  OprogramowanieId int(11) DEFAULT NULL,
  PRIMARY KEY (Id),
  UNIQUE KEY OprogramowanieId (OprogramowanieId),
  KEY fk_Komputery_Oprogramowanie_Komputery1_idx (KomputerId),
  KEY fk_Komputery_Oprogramowanie_Oprogramowanie1_idx (OprogramowanieId)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=3 ;

–
– Ograniczenia dla zrzutów tabel
–

–
– Ograniczenia dla tabeli komputery_oprogramowanie
–
ALTER TABLE komputery_oprogramowanie
  ADD CONSTRAINT fk_Komputery_Oprogramowanie_Komputery1 FOREIGN KEY (KomputerId) REFERENCES komputery (Id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT fk_Komputery_Oprogramowanie_Oprogramowanie1 FOREIGN KEY (OprogramowanieId) REFERENCES oprogramowanie (Id) ON DELETE NO ACTION ON UPDATE NO ACTION; ``

Code: Select all
`` CREATE TABLE IF NOT EXISTS oprogramowanie (
  Id int(11) NOT NULL AUTO_INCREMENT,
  Nazwa varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  Rodzaj int(11) DEFAULT NULL,
  Typ varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  Bity varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  Klucz varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  Cal varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  PN varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  PRIMARY KEY (Id),
  KEY fk_Oprogramowanie_Oprogramowanie_Rodzaj1_idx (Rodzaj)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=3 ;

–
– Ograniczenia dla zrzutów tabel
–

–
– Ograniczenia dla tabeli oprogramowanie
–
ALTER TABLE oprogramowanie
  ADD CONSTRAINT fk_Oprogramowanie_Oprogramowanie_Rodzaj1 FOREIGN KEY (Rodzaj) REFERENCES oprogramowanie_rodzaj (Id) ON DELETE NO ACTION ON UPDATE NO ACTION; ``

Then in Komputery folder I have a relationship.ini file:

Code: Select all
[Oprogramowanie]    Komputery_Oprogramowanie.KomputerId="$Id"    Komputery_Oprogramowanie.OprogramowanieId=Oprogramowanie.Id    action:label="Oprogramowanie"    vocabulary:existing="Komp_Opro"

And in Oprogramowanie folder i have a valuelist.ini file:

Code: Select all
`[Komputer]
   sql = “SELECT Id,Nazwa from Komputery ORDER BY Nazwa”

[Rodzaj]
   sql = “select Id, Rodzaj from Oprogramowanie_Rodzaj”
   
[Komp_Opro]
   sql = “SELECT a.Id,a.Nazwa from Oprogramowanie a WHERE (SELECT COUNT(‘x’) FROM Komputery_Oprogramowanie b WHERE a.Id=b.OprogramowanieId)=0 ORDER BY a.Nazwa”`

Vocabulary is designed in such a way to prevent from adding Oprogramowanie record to more than one Komputery record. All seems to work at first glance.

Problem is that although vocabulary returns correct data (lets say Oprogramowanie with id 2) it tries to add to komputery record a Oprogramowanie record with Id 1 which was previously inserted in another record. I looked into source of page and I can clearly see id 2 there. Why it tries to add id 1 instead.

shannah — Fri Nov 30, 2012 4:45 am

I haven’t had a chance to test this but have added an issue to the issue tracker.
http://bugs.weblite.ca/view.php?id=1195

Are you saying that the record that you select in the add existing record form is being ignored, and another record is added to the relationship instead?

Gwynnbleid1 — Fri Nov 30, 2012 6:35 am

Exactly.

Lets say I have:
Computer(Komputer) with id 1.
Software (Oprogramowanie) with name for ex. first and id 1
Software (Oprogramowanie) with name for ex. second and id 2
Nothing is added so far to Computer.
Valuelist shows both Software records and when I add software with id 1 to computer then all is fine.
When I try to add second software to computer then valuelist shows correctly list with only one existing software
with name second (which is correct). Also in source I can see that this reaming software has a id 2 (also correct).
But when I try to actually add it then nothing happens. Nothing new is added and software with id 2 is still available.
Funny thing is that I do get message that record is added successfully.

I hope that everything above is still clear

Regards and big thanks !!
Gwynnbleid1

shannah — Mon Dec 03, 2012 11:45 am

Yes. This definitely sounds like a bug. It is possible that it is tripping on the fact that the name of the id field is same for each table… but it *should* be able to still work despite that. I will need to test this out on my end, but don’t know when I’ll have a chance to do that.

Gwynnbleid1 — Mon Dec 03, 2012 12:29 pm

shannah wrote:Yes. This definitely sounds like a bug. It is possible that it is tripping on the fact that the name of the id field is same for each table… but it *should* be able to still work despite that. I will need to test this out on my end, but don’t know when I’ll have a chance to do that.

I will try with diffrent id then and post the results. Thanks for looking into this

Gwynnbleid1 — Mon Dec 03, 2012 3:18 pm

I just confirmed that problem lies(as you thought) in Id column names being identical. Changing those solved problem, but bug remains.

shannah — Mon Dec 03, 2012 3:59 pm

I forgot to ask. What version of Xataface are you using?

Gwynnbleid1 — Tue Dec 04, 2012 1:59 am

Xataface 2.0alpha1