list join not using correct column

[skyrun]

i'm 90% sure that there is an issue with the new join code where it's not using the correct columns with the correct table when you build the join. when both are the same name, this doesn't show as being an issue... but when the columns in the join are named something else like 'element_id', then an SQL error is created with it looking for a column named 'from column' in the 'to' database... so looking for sf_score_element.id in the example below. works fine if both columns are named the same things, but doesn't work so well when they're not...

so if i change the from column to 'element_id' (which is what it should be in the activity score list), then i get a SQL error looking for sf_score_element.element_id which doesn't exist since element_id is in the 'from' table, not the 'to' table.

 

[cheesegrits]

Looking at this now.

-- hugh

 

[cheesegrits]

OK, I can't actually provoke that error, but I think I see what the problem is. You are trying to create a repeat join, from activity_score to score_element. So it's a one-to-many relationship, where each activity_score row can have multiple score_element rows associated with it. Yes? In other words, you should have a "child" table (score_element), with an FK (foreign key) pointing to the PK (primary key) of the "parent" table (activity_score)

But you are trying to use an FK on the "parent". What you need, instead of an element_id on the activity_score table pointing to the PK (id) on the score_elements, is a score_elements.activity_score_id pointing to the P (probably id) on activity_score.id.

Basically, when you set that repeat button, our code will assume that's what is going on. the wording of the columns is a little misleading, and Rob and I have to work out a better way of labeling those, as they don't really mean "from" and "to".

-- hugh

 

[skyrun]

actually... sf_activity_score is supposed to create a many-to-many relationship between activities and score_elements.

so each activity can have multiple score_elements by having the same activity_id and different element_ids and each score_element can be used on many activities when the element_id is the same and the activity_id differs.

the way i've done that in the past is to create a 'linking table' (don't know the actual term for it) that has 2 FKs pointing to the two tables. and the combination of activity_id+element_id (or the reverse) is unique. sf_activity_score is supposed to be that table (but with a few extra columns defining the intersection (like the score for that element for that activity and the scorer and comments)

that's what i'm going for at least. its possible i have it defined incorrectly of course... i have lots of these many-to-many linking tables set up on my F2.1 app, so i'll see if they're set up in a similar way.

 

[skyrun]

by re-factoring the app and my use of databasejoin elements and joins to lists... i have made it work as expected.