ambiguous when two tables in a form

Questions related to using nuBuilder Forte.

ambiguous when two tables in a form

Postby dmajwool » Thu Dec 13, 2018 6:54 am

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.
dmajwool
 
Posts: 4
Joined: Thu Dec 13, 2018 4:49 am

Re: ambiguous when two tables in a form

Postby admin » Thu Dec 13, 2018 12:47 pm

David,

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

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


Steven
admin
Site Admin
 
Posts: 3309
Joined: Mon Jun 15, 2009 9:53 am

Re: ambiguous when two tables in a form

Postby dmajwool » Fri Dec 14, 2018 4:48 am

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.
dmajwool
 
Posts: 4
Joined: Thu Dec 13, 2018 4:49 am

Re: ambiguous when two tables in a form

Postby kev1n » Fri Dec 14, 2018 6:49 pm

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   
kev1n
 
Posts: 838
Joined: Mon Oct 15, 2018 2:13 am

Re: ambiguous when two tables in a form

Postby dmajwool » Sun Dec 16, 2018 9:02 pm

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.
dmajwool
 
Posts: 4
Joined: Thu Dec 13, 2018 4:49 am

Re: ambiguous when two tables in a form

Postby kev1n » Mon Dec 17, 2018 7:18 pm

As long as records are displayed, sorting and searching in the form work, everything is fine.
kev1n
 
Posts: 838
Joined: Mon Oct 15, 2018 2:13 am

Re: ambiguous when two tables in a form

Postby dmajwool » Mon Dec 17, 2018 9:15 pm

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.
dmajwool
 
Posts: 4
Joined: Thu Dec 13, 2018 4:49 am


Return to General