relationship between tables

Archived from the Xataface Users forum.

kevinwen — Mon Feb 08, 2010 1:17 pm

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 — Mon Feb 08, 2010 1:21 pm

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

kevinwen — Mon Feb 08, 2010 3:48 pm

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 — Mon Feb 08, 2010 3:56 pm

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 — Mon Feb 08, 2010 6:04 pm

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 — Mon Feb 08, 2010 6:08 pm

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 — Tue Feb 09, 2010 12:12 pm

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 — Tue Feb 09, 2010 12:20 pm

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

kevinwen — Tue Feb 09, 2010 3:48 pm

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

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

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 — Tue Feb 09, 2010 3:59 pm

What version of Xataface are you using?

kevinwen — Tue Feb 09, 2010 5:09 pm

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

shannah — Tue Feb 09, 2010 5:13 pm

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

kevinwen — Wed Feb 10, 2010 11:01 am

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

shannah — Wed Feb 10, 2010 11:15 am

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

kevinwen — Wed Feb 10, 2010 11:49 am

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.