relationship between tables

[kevinwen]

Hi,

I have a situation where we need to have 2 tables with on-to-many relationship point to each other in the tab that next to 'edit'. However, it doesn't work quite well. The following is the example (assuming a_id appears in both tables, and is primary key in table_A and foreign key in table_B):

records in table A can have many records in table B. In table A's relationships.ini, I defined the __sql__ as follow:

Code: Select all

__sql__ = "select * from table_A where a_id = '$a_id' "

clicking the other_references tab will show all other_references for a specific change_memo. However, when I defined the __sql__ in tabe B's relationships.ini, clicking the change_memos tab shows nothing:

Code: Select all

__sql__ = "select * from table_A where a_id = '$a_id' "

Does somebody know what is wrong with it?

[shannah]

The relationship in tableA appears to be related to itself (it has no reference for tableB).

[kevinwen]

My mistake. the queries in to relationships.ini should be as follow:

Both tableA and tableB have a column 'a_id'. 'a_id' in tableA is the primary key, while 'a_id' in tableB is a foreign key referencing tableA.

tableA relationships.ini:

Code: Select all

__sql__ = "select * from table_B where a_id = '$a_id' "

tableB relationships.ini:

Code: Select all

__sql__ = "select * from table_A where a_id = '$a_id' "

Can you look at this again and tell me what goes wrong? Thanks.

[shannah]

One observation off the bat is that your relationship from tableB to tableA will have either 0 or 1 records (no more than one). Is this intended?

[kevinwen]

Maybe the queries make things confused (This may be the cause that our program doesn't work). Here's the thing we want to accomplish:

tableA has a unique key 'a_id', meaning there is only 1 record in tableA with 'a_id' = 3, for example. On the other hand, tableB has a key 'a_id' that allows 0/1/many records with 'a_id' = 3. This is what I call 1-to-many relationship.

relationship in tableA to tableB works:

Code: Select all

__sql__ = "select * from table_B where a_id = '$a_id' "

, but relationship in tableB back to tableA doesn't work:

Code: Select all

__sql__ = "select * from table_A where a_id = '$a_id' "

[shannah]

Yes. But consider this query from your tableB relationships.ini file:

Code: Select all

__sql__ = "select * from table_A where a_id = '$a_id' "


Since table_A has only one record matching any particular value for a_id (as you just stated), this query can return 1 record at most. Is this your intention?

[kevinwen]

Yes. This is what I want to do. The problem is there is no record returned when clicking the tableB tab (next to 'edit'), as it should show 1 record for tableA.

[shannah]

Can you show me result for the tableB->tableA relationship of the mysql query as performed in PHPMyAdmin (or directly on the database)?

[kevinwen]

Steve, I really appreciate your time on it. Here's the detail:

__sql__ = "select * from change_memos where change_memo_id = '$change_memo_id' order by short_description"; change_memos is atacully tableA, and change_memo_id is a_id.

the query I ran is "select * from change_memos where change_memo_id = '545' '' order by short_description" returns a row as query_result_1.jpg

query_result_1.jpg (95 KiB) Viewed 2936 times

In change_memos table view, I found there is a query on the dataface__view as in query_result_1_2.jpg:

SELECT COUNT(*) as num from `dataface__view_change_memos_77d091165d9b9bb8301c720b6686f3c4` as `change_memos` where `change_memo_id` = '545' order by `short_description` asc

query_result_1_2.jpg (241.92 KiB) Viewed 2936 times

You can see that there is a change_memos(tableA) tab next to edit tab, and on left side of the view page shows this (related) change_memo record.

However, when I click on the change_memos tab, the following query is ran:

SELECT COUNT(*) as num from `dataface__view_change_memos_77d091165d9b9bb8301c720b6686f3c4` as `change_memos` where `change_memo_id` = '' order by `short_description` asc as query_result_3.jpg:

there is no change_memo_id passed into the query. the link under change_memos tab looks like this:
http://rsi-kwen/tax_and_reg_2/index.php ... ange_memos


I hope this can help. thanks.

[shannah]

What version of Xataface are you using?

[kevinwen]

I opened the /xataface/version.txt and it says 1.2.2 1616.

[shannah]

I think this problem may have been fixed in 1.2.3beta. Try upgrading to that and see if it fixes it.

[kevinwen]

I looked up the xataface web site and didn't find 1.2.3beta. How do I get it?

[shannah]

I have a link to it in this thread:
viewtopic.php?f=4&t=5101

[kevinwen]

I tried 1.2.3b2 but it doesn't work either. When I click new_related_record in tableA and created the related record (tableB), I can see this related record in tableA, but when I go to this related record in tableB I can see the tableA record from which this record in tableB was created, but when click on the tableA tab, it shows nothing, same as version 1.2.2.

Can you tell me in which file the fug was found and fixed? Thanks.