Join table with vocabulary

A place for users and developers of the Xataface to discuss and receive support.

Join table with vocabulary

Postby 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.
Gwynnbleid1
 
Posts: 31
Joined: Thu Sep 20, 2012 3:02 pm

Re: Join table with vocabulary

Postby 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?
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Join table with vocabulary

Postby 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
Gwynnbleid1
 
Posts: 31
Joined: Thu Sep 20, 2012 3:02 pm

Re: Join table with vocabulary

Postby 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.
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Join table with vocabulary

Postby 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
 
Posts: 31
Joined: Thu Sep 20, 2012 3:02 pm

Re: Join table with vocabulary

Postby 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.
Gwynnbleid1
 
Posts: 31
Joined: Thu Sep 20, 2012 3:02 pm

Re: Join table with vocabulary

Postby shannah » Mon Dec 03, 2012 3:59 pm

I forgot to ask. What version of Xataface are you using?
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Join table with vocabulary

Postby Gwynnbleid1 » Tue Dec 04, 2012 1:59 am

Xataface 2.0alpha1
Gwynnbleid1
 
Posts: 31
Joined: Thu Sep 20, 2012 3:02 pm


Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 2 guests

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved