Error calculating the number of related records

A place for users and developers of the Xataface to discuss and receive support.

Error calculating the number of related records

Postby NikVoelker » Tue Jun 15, 2010 4:13 am

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
 
Posts: 2
Joined: Tue Jun 15, 2010 3:29 am

Re: Error calculating the number of related records

Postby NikVoelker » Tue Jun 15, 2010 6:22 am

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
NikVoelker
 
Posts: 2
Joined: Tue Jun 15, 2010 3:29 am

Re: Error calculating the number of related records

Postby dckc » Mon Sep 26, 2011 8:46 pm

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..."
dckc
 
Posts: 4
Joined: Mon Sep 26, 2011 8:31 pm


Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 13 guests

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved