Many to Many Relationships

[rtresidd]

Hi
I'm just starting out with Xataface and I'm wondering if it has the capability to handle a particular type of display.
Sorry if this is a bit long winded..
I have 2 tables joined via a table holding keys to both of the other indexes. eg standard many to many / junction table et al..
One table contains information regarding a specific Board (BoardItem) and the other table contains modifications to be applied to specific revisions of a board (BoardModifications).
There is also a Revision Table (BoardRevisions) which is a field of both the BoardItem and the Board Modification..

Now a modification is to be applied to all BoardItems that are of a certain revision.

Now what I'd like is when I go into the relationships for the BoardItems<->BoardModifications join I see a list of the modifications filtered for the currents boards revision.
And a field stating if it has been done or not (in this case a date field that is part of the join table or say a null entry indicating that no join exists between the mod and this particular board.)

The select statement to view such a list is something like this:
SELECT BI.Id, BI.fk_BoardRevisionId As Board_Revision, BM.Id, BM.Title As ModificationTitle,
(SELECT BMBI.DateCompleted FROM BoardItems_BoardModifications BMBI
WHERE ((BMBI.fk_BoardItemId = BI.Id) AND (BMBI.fk_BoardModificationId = BM.Id))
) AS DateCompleted
FROM BoardItems BI, BoardModifications BM
WHERE (BI.fk_BoardRevisionId = BM.fk_BoardRevId)

would give:
"Id","Board_Revision","Id","ModificationTitle","DateCompleted"
1,1,1,"Test",""
1,1,2,"Test2","2012-08-20 01:44:00"

showing a null entry for DateCompleted where the join didn't exist..

However such a statement doesn't allow you to create the join..

Clicking on the row in Xataface should bring up the information in the join table so the user can say they completed that modification for that board and add any additional notes etc specifically for that change...

I'm not sure if it is even possible to do this in the framework..?

I've added a diagram showing the relationships..

Thanks
and sorry if this doesn't make sense at all..

[rtresidd]

Ok
I think I've worked out a way to do this.
From what I can see I will have to create another tab for a record, something like the relationships tab but it will load a custom view from the database.
This can simply list the modifications related to this board type that have not yet been completed (eg don't have a matching join entry).
With this list I can add a custom action button, like "completed" which will in the action create the Join on the items selected in the list..

I can still use the normal relationships tab for modifications that have been completed.

I'll see how I go.

[rtresidd]

ok Got all this going. (except filtering the value list in the add existing, see end of this post..)
Created a standard relationships entry for "Completed_Modifications"

and also created another relationships entry for the "Outstanding_Modifications"
This used a custom sql statement to perform a couple of joins in such a way that I got every board mod that should be applied to that particular board revision and found if the "join" table didn't contain a corresponding entry.

[OutStanding_Modifications]
__sql__ = "SELECT * FROM BoardModifications BM JOIN BoardItems BI ON (BI.fk_BoardRevisionId = BM.fk_BoardRevId) LEFT JOIN BoardItems_BoardModifications BIBM ON (BM.id_BoardModifications = BIBM.fk_BoardModificationId AND BI.id_BoardItems = BIBM.fk_BoardItemId) WHERE BI.id_BoardItems = '$id_BoardItems' AND BIBM.fk_BoardItemId is null"
visibility:id_BoardModifications=hidden
visibility:id_BoardItems_BoardModifications=hidden
visibility:fk_BoardRevId=hidden
visibility:fk_BoardModificationId=hidden
visibility:fk_BoardItemId=hidden
visibility:DateCompleted=hidden
visibility:Who=hidden
visibility:Notes=hidden

Note I had to modify the primary keys to all have different names from the above diagram.. otherwise the links for the list view wouldn't work correctly..

When doing an add existing however I can't filter that list so that it only shows the outstanding modifications for the current boards revision...
The best I can do is limit it to showing modifications that haven't been completed across all boards of that mods type..
sql:
SELECT BM.Id_BoardModifications, CONCAT(BM.Title, ' - ', BM.CreationDate) AS Modification FROM BoardModifications BM JOIN BoardItems BI ON (BI.fk_BoardRevisionId = BM.fk_BoardRevId) LEFT JOIN BoardItems_BoardModifications BIBM ON (BM.id_BoardModifications = BIBM.fk_BoardModificationId AND BI.id_BoardItems = BIBM.fk_BoardItemId) WHERE BIBM.fk_BoardItemId is null

I really need to be able to get the valuelist to also have a variable passed into it to limit/filter this list but how????

Either that or I need to somehow be able to select the checkbox next to the row of the mod I want to "complete" on the "Outstanding_Modifications" tab and have a button that allows me to add existing with that specific mod selected in the drop down box ready for the user to fill out some other info.

Thanks
Richard

[rtresidd]

Go figure 5 minutes later...
Think I might have found an answer to my problem for giving a filtered view in the value list.
Found a discussion in the forum regarding using the valuelist__valuelistname delegate function http://xataface.com/forum/viewtopic.php?t=3961
With that info I think I'll be able to tweak the sql statement that generates the valuelist for this particular problem.

Cheers
Richard

[rtresidd]

Another issue I've come across..
When using the auto many to many join method eg
'val' = y.a
y.b = z.b
in the relationships.ini file.
The record columns associated with the parent aren't made available as entries in the result from what I can determine.
I added some debug code into the relatedList.php file that printed out the available keys for the $cols and none of the columns from the parent are in the result..

Ok fair enough I thought its not really required usually.. so I created an __sql__ staement to perform the join manually and provide every column.
However I then ran into an issue with the portal-context query parameter..
from what I can see there is an additional value relating the the parents id which seems to cause the issue..

-- query for clicking on a row in the related record list using "auto" join method
https://xx.xx.xx.xx/equipment2/index.php?
-action=browse
&-portal-context=BoardItems%2FCompleted_Modifications%3Fid_BoardItems%3D6
%26Completed_Modifications%253A%253Aid_BoardModifications%3D8
%26Completed_Modifications%253A%253Aid_BoardItems_BoardModifications%3D19
&-table=BoardModifications&id_BoardModifications=%3D8

-- query for clicking on a row in the related record list using the sql statement
https://xx.xx.xx.xx/equipment2/index.php?
-action=browse
&-portal-context=BoardItems%2FCompleted_Modifications%3Fid_BoardItems%3D6
%26Completed_Modifications%253A%253Aid_BoardModifications%3D8
%26Completed_Modifications%253A%253Aid_BoardItems_BoardModifications%3D19
%26Completed_Modifications%253A%253Aid_BoardItems%3D6
&-table=BoardModifications&id_BoardModifications=%3D8

I tried a couple of different ways to do the join:

SELECT * FROM BoardModifications BM JOIN BoardItems_BoardModifications BIBM ON (BM.id_BoardModifications = BIBM.fk_BoardModificationId) JOIN BoardItems BI ON (BI.id_BoardItems = BIBM.fk_BoardItemId AND BI.id_BoardItems = '$id_BoardItems')

SELECT * FROM BoardModifications BM JOIN BoardItems_BoardModifications BIBM ON (BM.id_BoardModifications = BIBM.fk_BoardModificationId) JOIN BoardItems BI ON (BI.id_BoardItems = BIBM.fk_BoardItemId) WHERE BI.id_BoardItems = '$id_BoardItems'

Both resulted in the same issue..

I would like the full join to be able to compare a value in the parent table with a value in the related table to format the css style for the row.

Basically I just get a connection closed error in the web browser when trying to click on one of these rows when using the sql statement to do the join.
I can remove the extra value in the portal context and it works.. or simply remove the entire portal context and it works..

Help??

[rtresidd]

meh
Gave up on trying to get the sql path to work..
Added another parameter to the css__relatedTableRowClass_RELATIONSHIP_NAME function I'd added in that accepted the $this->_record parameter from the RelationedList class..
then extracted the appropriate values directly..

Seems to be working..