add existing record blank on many-to-many relationship

[magnus]

hi there, just starting out with xataface.
it's working great so far except for one hiccup with many-to-many relationship.

i've got two tables and a relationship table for store the relationships
[videos]
auto_pkid int auto_increment PRIMARY
name varchar(255)

[models]
auto_pkid int auto_increment PRIMARY
name varchar(255)

[pcl]
auto_pkid int auto_increment PRIMARY
parent int
child int

inside the tables directory of videos i have a relationships.ini file that looks like

Code: Select all

[Models]
models.auto_pkid = pcl.child
pcl.parent = "$auto_pkid"

when looking at the models relationship i can se ethe options to
- add new models record
- add existing models record.

add new models record works fine.

add existing models record does not. i get a screen presenting me with a Select dropdown and a text field for the Name field. I assume select is supposed to populate with the ids of existing records from the models table but it is empty.

i feel like i'm missing something really simple here. please help.

thanks

[rdb]

Hi magnus,

(This is from one newbie to another, so consider the source!)

Try removing the auto_pkid from the link table (pcl), and use both parent and child in a compound primary key. I.e,

Code: Select all

CREATE TABLE IF NOT EXISTS 'pcl' (
  'parent' int(11) NOT NULL,
  'child' int(11) NOT NULL
  PRIMARY KEY ('child', 'parent')
);


Whether or not the order of the fields in the primary key is important or not is going to depend on the relationship-parsing logic of XF.

Hope this helps.

Cheers.[/i]

[magnus]

Hey rdb.

thanks for the reply. your solution worked so i'm much more newB than you apparently . A little bit pissed i didn't try that myself.

so now my pcl table is just
parent int
child int
and the two fields together make a compound primary key.

so that was interesting to learn. now i'm attempting to do the relationship via raw sql. so following the example in the getting started tutorial my relationship.ini file looks like

Code: Select all

[Models]
__sql__ = "SELECT * FROM pcl INNER JOIN models m ON pcl.child = m.auto_pkid WHERE pcl.parent = '$auto_pkid'"


When i click on the Models tab to see the relationships, it can see the existing relationships, but if i try to add a existing record to the relationship, i once again get the same problem where the select drop down is blank.

any ideas apprecieated. thanks.

[rdb]

I think you may be SELECT-ing the wrong thing. Your SQL statement is selecting data from the join table whereas what you really want is data from the child (model) table. I think the following might do that trick:

Code: Select all

SELECT * FROM model INNER JOIN pcl ON pcl.child = model.auto_pkid WHERE pcl.parent='$auto_pkid'


Does that make sense?

Cheers.

[magnus]

Thanks again rdb

heh,... i guess i should have tried that... mainly the reason i didn't was because i was going by the sample code int he tutorial which has the relationship table in the FROM clause instead of the "destination" table

http://xataface.com/documentation/tutor ... ationships

Code: Select all

[Courses]
__sql__ = "SELECT * FROM ProgramCourses pc INNER JOIN Course c ON pc.CourseID = c.CourseID WHERE pc.ProgramID = '$ProgramID'"

anyway, what i'm eventually getting at is being able to manage several types of relationships in a single relationship table.
To facilitate that i've added another field to the pcl table so it looks like
parent int PRIMARY KEY
child int PRIMARY KEY
linktype varchar(50) PRIMARY KEY

the relationships.ini table looks like

Code: Select all

[Models]
__sql__ = "SELECT * FROM models m INNER JOIN pcl ON pcl.child = m.auto_pkid and pcl.linktype = 'video2model' WHERE pcl.parent = '$auto_pkid'"


but apparently this doesn't work. Is this possible with xataface or is it too complicated?

thanks

[shannah]

Xataface uses a few heuristics to help it figure out how to work with relationships. You may want to try rewording the relationship to put the pcl.linktype = 'video2model' in the where clause instead of the join clause.

When you say that the existing query isn't working, what happens? Do you get an error?

[magnus]

Hey Steve Thanks for replying.

Xataface uses a few heuristics to help it figure out how to work with relationships. You may want to try rewording the relationship to put the pcl.linktype = 'video2model' in the where clause instead of the join clause.

that was what i tried first actually. didn't work.
is my syntax correct? I just assumed single quotes represented strings.

When you say that the existing query isn't working, what happens? Do you get an error?

To be clear, i mean i get the add existing screen with the select drop down but it is blank.
below it is a label and text box representing the only attribute in the models table, name.

no error is generated, at least that i noticed. looked all around the screen.

[shannah]

Can you look at the HTML source for the SELECT list on the "add existing" form to see what it looks like. I'm curious if it's actually picking up the correct IDs but not displaying a title.

[magnus]

Can you look at the HTML source for the SELECT list on the "add existing" form to see what it looks like. I'm curious if it's actually picking up the correct IDs but not displaying a title.

hey steve, this is wht it looks like

Code: Select all

<table border="0">
   <tbody><tr>
      <td valign="top" align="right"><b>Select</b></td>
      <td valign="top" align="left"><select name="select" class="record_selector">
</select></td>
   </tr>
   
   


looks like it's not picking it up. just so you know, when i click on the models tab, it looks like it is able to display the records that match the relationship fine

for reference my pcl table looks like

Code: Select all

parent int PRIMARY KEY
child int PRIMARY KEY
linktype varchar(50) PRIMARY KEY


and the relationships.ini file in videos folder looks like

Code: Select all

[Models]
;models.auto_pkid = pcl.child
;pcl.parent = "$auto_pkid"
__sql__ = "SELECT * FROM models m
         INNER JOIN pcl ON pcl.child = m.auto_pkid
         WHERE pcl.parent = '$auto_pkid'
         AND pcl.linktype = 'video2model'"


[shannah]

I'm going to try to set up a test app with this set up to see what I can see... I'll get back to you later today or tomorrow with results.

-Steve

[magnus]

thanks Steve. Much Appreceiated. Let me know if you need anything from me.

[magnus]

hey Steve,

any update on this? If Xataface doesn't really support this that is fine, i will probably just break out my relationships into seperate tables.

jsut let me know please. thanks.

[shannah]

Sorry. Been bogged down and haven't had a chance to set up a test scenario.

Xataface certainly does support this. There is likely just a problem with the way that your tables are defined/ relationships defined.... as I run many relationships similar to this in my apps.

[magnus]

i see ok.
do you have any idea on when you might be able to get back to me?

thanks

[shannah]

Change

Code: Select all

__sql__ = "SELECT * FROM models m ....


to

Code: Select all

__sql__ = "SELECT m.* FROM models m  ..."


So that Xataface knows that we are only relating to the models table and the join table is just for joining.

This seems to work.

I'll look into it more closely later to decide whether to include support for the other syntax.