Error calculating the number of related records

[NikVoelker]

Hello,

first of all thanks a lot for the work on this tool which really eases a good part of our daily data handling. Keep up the great work!

I have a table #1 which has a relationship with table #3 through the junction table #2 (many to many).

It's defined in my relationships.ini by

Code: Select all

__sql__ = "SELECT `table_3_column` FROM `table3` INNER JOIN `table2` ON (`table2.table3_id` = `table3.id`) WHERE `table2.table1_id` = '$id'"

As for the display of the relationship all works fine (and even deeper ramifications i.e. table3 relates to table5 through table4 and so on are correctly displayed in the relationship tree on the left) but when it gets to removing or updating the relationship XF comes up with the following error message:

Code: Select all

Fatal error: Error calculating the number of related records there are for the relationship 'Region' in the table 'table1'. There was a problem performing the sql query 'SELECT COUNT(*) as num from `table3` INNER join `table2` on (`table2`.`table3_id` = `table3`.`id`) where `table2`.`table1_id` = '2' and `id` = '1''. The MYSQL error returned was 'Column 'id' in where clause is ambiguous'.


The query should work fine when it would have "... AND `table3`.`id` = '1'" instead of "... AND `id` = '1'" at the end. But how do I update/tell XF to perform the right query?

Thanks a lot for your hints/help! Cheers, Nik

[NikVoelker]

I have now replaced the relationship SQL by using the following XF notation:

Code: Select all

table3.id = table2.table3_id
table2.table1_id = "$id"

It seems to map the same relation with full functionality...

Cheers & greetings from sunny Germany! Nik

[dckc]

Wow... I thought I was stumped, but a little searching turned up this clue.

Is this join notation documented? Ah, yes, it's right there...
http://xataface.com/documentation/tutor ... ationships


Xataface is really quite amazing. After dabbledb closed up shop and
we ran out of records on the cheap plan at Zoho, I'm having a lot of fun
with Xataface. I've had to scratch my head a few times, but each time, the
answer is in there somewhere.

I don't know why I didn't find it before. Perhaps because
I was too much of a python biggot and wasn't willing to look at php solutions.


fwiw, the source of the app that I'm building is https://bitbucket.org/DanC/hh-office

"hh-office is a record keeping app for a small counseling practice.

by Dan Connolly, madmode

It's the Nth generation of this app:

1. a spreadsheet and a great big table for sorting [hardcopy reports]
2. a filemaker db
3. a web-hosted dabbledb app
3. a Zoho Creator app

This is the 1st generation to be all open source, though.

It's built using xataface, a php/mysql framework..."