Joining a table with itself in form source

[kkatsaros]

Hi there! Great work!

I'm new to nuBuilderPro. I'm trying to join a table with itself within a form SQL source, but I get errors with it. Here's the code:

Code: Select all

SELECT trainees.firstname, trainees.lastname, payers.lastname AS 'payer'
FROM clients trainees
INNER JOIN clients payers ON trainees.payerid = payers.id
WHERE (trainees.dou = '0000-00-00' or trainees.dou IS NULL) and trainees.class IS NOT NULL

and the error I get when I try to run the form is:

Code: Select all

===USER==========
globeadmin
===PDO MESSAGE=== 
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous
===SQL=========== 
SELECT  id,IFNULL(firstname,''),IFNULL(lastname,''),IFNULL(REPLACE(FORMAT(occupationid,0), ",", ""),''),IFNULL(class,''),IFNULL(REPLACE(FORMAT(payerid,0), ",", ""),''),IFNULL(address,''),IFNULL(DATE_FORMAT(dob,"%d-%b-%Y"),''),IFNULL(DATE_FORMAT(dor,"%d-%b-%Y"),''),IFNULL(DATE_FORMAT(dou,"%d-%b-%Y"),''),IFNULL(phone,''),IFNULL(mobile,''),IFNULL(notes,'')  FROM clients trainees INNER JOIN clients payers ON trainees.payerid = payers.id  WHERE (trainees.dou = '0000-00-00' or trainees.dou IS NULL) and trainees.class IS NOT NULL   
===BACK TRACE====
/var/www/dev-hapkidonet/nuapi.php - line 1943 (nuRunQuery)
/var/www/dev-hapkidonet/nuapi.php - line 1619 (nuGetBrowseRecords)
/var/www/dev-hapkidonet/nuapi.php - line 183 (nuGetBrowseForm)

The "id" that is complained to be ambiguous is set as the Primary Key in both the table 'clients' and the form.
I have tried to replace "...FROM clients trainees INNER JOIN clients payers..." with "...FROM clients AS trainees INNER JOIN clients AS payers...", but no luck.
The reason of this "complex" design is that a trainee can be a payer at the same time. So, to avoid duplicate records, I made a table 'clients' and put both trainees and payers in there.
The same SQL query runs without a problem in phpMyAdmin and gives results.

Any thoughts on this?
Thanx!

[kkatsaros]

So, am I standing before the conclusion that joining a table with itself cannot happen in nuBuilder? Do I have to change my design?

[massiws]

kkatsaros,
in that way nuBuilder don't work, because the 'id' field is automatically added by nuBuilder (without alias).
You could try to run your query using nuRunQuery() in Custom Code -> Before Browse; then in SQL field you should insert something like this:

Code: Select all

SELECT * FROM #TABLE_ID# 

@Steven: in nuBuilder v.2 there was a feature to run queries from BeforeBrowse and catch the result in a temp table using #beforeBrowse# hash variable: I can't find the same in nuBuilderPro documentation.
Is this feature been removed in nuBuilderPro? Can we use #TABLE_ID# instead of #browseTable# ?

Max.

[kkatsaros]

Hi Max,

Given the limited available time for this project, I decided to split my tables and go with MySQL trigger actions in order to have the duplicated records synchronized. When I have the time needed (I'm not experienced enough with php and how it is "merged" into nuBuilderpro), I'll try your solution and report back.

Thanx for replying!

[kkatsaros]

I gave it a shot (what the hell, another gracefully lost Sunday afternoon...).
Works as expected. Let me put it together:

Created the table 'test' with fields 'test_id', 'test_name' and 'test_relative', as follows:

Code: Select all

test_id				test_name		test_relative
5455e3c959f3da7 	Kostas			5455e33fe907b21
5455e33fe907b21 	RelOfKostas		NULL

With Form Wizard I created the form 'test' based on the table. After creation, I went to form's properties and in 'SQL' field (in 'General' tab) I entered:

Code: Select all

select Name, Relative from #TABLE_ID#

Then in tab 'Custom Code' I selected 'Before Browse' and entered:

Code: Select all

nuRunQuery("
CREATE TABLE #TABLE_ID#
SELECT name.test_id, name.test_name AS 'Name', relative.test_name AS 'Relative'
FROM test AS name
INNER JOIN test AS relative
ON name.test_relative = relative.test_id
");

Finally, in 'Browse' tab I changed 'test_name' and 'test_relative' to 'Name' and 'Relative' respectively. Now when I open the form 'test' in Browse mode the long-awaited result is appearing:

Code: Select all

Name		Relative
Kostas		RelOfKostas

Now I have to revert my database design back to my original plans , but my position is definitely better than before.

Thank you so much Max for the hint!

[massiws]

kkatsaros,
- in Before Browse tab:

Code: Select all

$sql = "CREATE #TABLE_ID#
  SELECT trainees.firstname, trainees.lastname, payers.lastname AS 'payer'
  FROM clients trainees
  INNER JOIN clients payers ON trainees.payerid = payers.id
  WHERE (trainees.dou = '0000-00-00' or trainees.dou IS NULL) and trainees.class IS NOT NULL";
nuRunQuery($sql); 

- in General tab:

Code: Select all

SELECT * FROM #TABLE_ID#

and you should get what you need.

[kkatsaros]

Exactly Max. Problem solved, system rocks!

A minor error in your last post:

Code: Select all

$sql = "CREATE #TABLE_ID#

should be:

Code: Select all

$sql = "CREATE TABLE #TABLE_ID#

Thanx again!

[massiws]

Ok!