ambiguous when two tables in a form

[dmajwool]

Hi,

I hope you can point me at where I'm going wrong on my first use of nuBuilder.
I have made a Fast Form pointing at one of my tables of my existing phpbb database. It works as expected
However, when I edit the Fast Form using the SQL builder and add a related phpbb table (in preparation to add some fields from a 2nd table to the form) I no longer see any records displayed in the form and I get a nuRunQuery error.

Here is my edited Fast Form SQL

Code: Select all

SELECT
    phpbb_profile_fields_data.*,
    phpbb_users.*

FROM
    phpbb_profile_fields_data
        JOIN phpbb_users ON phpbb_profile_fields_data.user_id = phpbb_users.user_id

And here is the nuRunQuery error

Code: Select all

[0] : 
===USER==========

globeadmin

===PDO MESSAGE=== 

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'user_id' in field list is ambiguous

===SQL=========== 

SELECT user_id,pf_firstname,pf_lastname,pf_yearjoined,pf_paymentmethod
 FROM     phpbb_profile_fields_data         JOIN phpbb_users ON phpbb_profile_fields_data.user_id = phpbb_users.user_id
WHERE 1 LIMIT 0, 20

===BACK TRACE====

/var/sites/s/<mySite>/public_html/nuBuild/nuform.php - line 892 (nuRunQuery)

/var/sites/s/<mySite>/public_html/nuBuild/nuform.php - line 322 (nuBrowseRows)

/var/sites/s/<mySite>/public_html/nuBuild/nuapi.php - line 40 (nuGetFormObject)

In my form I have 4 columns - pf_firstname,pf_lastname,pf_yearjoined,pf_paymentmethod. I don't have a column for user_id.

The thing that I don't understand is that the SQL in the error message is different from the SQL in the form design. The form design in the nuBuilder UI doesn't have me SELECTing the user_id. So I can't see the ambiguity.

Might there be another object (called user_id) somewhere in nuBuilder that I've previously created that I need to delete?

It's not an option for me to rename the table fields and make them unique because they are existing fields created by phpbb.

Thanks for your help, David.

[admin]

David,

A Form (Edit Form) can only save to 1 table.

A Browse Form, on the other hand, can use multiple tables.


Steven

[dmajwool]

Thanks Steven.

Thanks for the reply, but I think there must be something else going on in my application...

Since your reply I have created a new Browse-Only form and this form displays a results set when only one table is in the SQL.

But when I add a second table (using the SQL builder) and esablish a join but include NO fields from the 2nd table in the form, I no longer get any results displayed in the form.

Instead I get an SQL error of the same format as posted earlier.

Code: Select all

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'user_id' in field list is ambiguous

===SQL=========== 

SELECT user_id,phpbb_users.user_id,phpbb_users.username,phpbb_users.user_email
 FROM     phpbb_users         JOIN phpbb_profile_fields_data ON phpbb_users.user_id = phpbb_profile_fields_data.user_id
WHERE 1 LIMIT 0, 20

The strange thing is the part of the error message SQL "SELECT user_id,"

I find this strange because in the nuBuilder UI I have not selected the field "user_id". I have only columns selected for "phpbb_users.user_id, phpbb_users.username, phpbb_users.user_email," .

Code: Select all

SELECT
    phpbb_users.user_id,
    phpbb_users.username,
    phpbb_users.user_email,

FROM
    phpbb_users
        JOIN phpbb_profile_fields_data ON phpbb_users.user_id = phpbb_profile_fields_data.user_id

Many thanks, David.

[kev1n]

nuBuilder adds the primary key automatically. Since both tables contain user_id you'll see an error "Column 'user_id' in field list is ambiguous".
To come around this issue, use an alias for user_id together with a sub select.

Code: Select all

SELECT user_id2, username, user_email FROM (
SELECT
    phpbb_users.user_id as user_id2,
    phpbb_users.username,
    phpbb_users.user_email,

FROM
    phpbb_users
        JOIN phpbb_profile_fields_data ON phpbb_users.user_id = phpbb_profile_fields_data.user_id
) X	

[dmajwool]

Hi Kevin, Thanks for the reply.

I'm afraid I got myself tied up in knots trying to use nested sub-queries as you suggest - getting different SQL errors at every turn. The SQL got very complicated very quickly.

What did work for me though, was what may be a much simpler approach. I changed the primary key in the Main tab of the nuBuilder form definition to be more "fully qualified" (if that's the correct term).

Instead of user_id, I entered phpbb_users.user_id

So when nuBuilder automatically enters this string into the sql, it is not ambiguous.

Are there any knock-on problems that will arise by using this approach?

Cheers, David.

[kev1n]

As long as records are displayed, sorting and searching in the form work, everything is fine.

[dmajwool]

Thanks Kevin. Appreciate that, but it feels a bit like I'm doing a workaround.

Perhaps the script that nuBuilder automatically includes the first Table's PK into the SQL select might be worth visiting in development plans?

instead of automatically putting "PK" into the SQL, would it work if we put "FirstTable.PK"?

Is that something I could try here? Which files should I try editing?

Thanks, David.