DataAssist Pages wizard does not write the correct query code, "ambiguous error".
It appears that the DataAssist Pages wizard does not write the correct query code. Firstly, it is not written consistently. Sometimes with `` sometimes without. Secondly, it sometimes misses the table name before the column name, which becomes an "amigous" error message.
When I let Querybuilder fix the problem, orders will not be the way I want it. It appears that query builder is based on the order in the database itself. But I have changed this in the DA Pages wizard.
So I have to correct the code, which is a little frustrating to do every time I create new CRUD files using DA.
Below is the code from both Detail page and Results page. Both the wrong code that DA creates and the code that I corrected.
I hope you can add this to your "to-do list" to the next update.
Detail page, DA code with error from browser:
"SELECT id, `llSubject`.`menu_name` AS llSubject_menu_name, llCountry.country AS llCountry_country, `llCategory`.`content` AS llCategory_content, content, landmarkFeature, link1, link2, date, date_update, user_id FROM llPage LEFT JOIN llSubject ON llSubject.id = llPage.subject_id LEFT JOIN llCountry ON llCountry.code = llPage.countryCode LEFT JOIN llCategory ON llCategory.id = llPage.category_id WHERE id = ?
Column 'id' in field list is ambiguous"
--
Detail page, my correct code:
"SELECT `llPage`.`id`, `llSubject`.`menu_name` AS llSubject_menu_name, `llCountry`.`country` AS llCountry_country, `llCategory`.`content` AS llCategory_content, `llPage`.`content`, `llPage`.`landmarkFeature`, `llPage`.`link1`, `llPage`.`link2`, `llPage`.`date`, `llPage`.`date_update`, `llPage`.`user_id` FROM llPage LEFT JOIN llSubject ON llSubject.id = llPage.subject_id LEFT JOIN llCountry ON llCountry.code = llPage.countryCode LEFT JOIN llCategory ON llCategory.id = llPage.category_id WHERE `llPage`.`id` = ?"
-----
Results page, DA code with error from browser:
SELECT id, link2, `llSubject`.`menu_name` AS llSubject_menu_name, llCountry.country AS llCountry_country, `llCategory`.`content` AS llCategory_content, content, date_update FROM llPage LEFT JOIN llSubject ON llSubject.id = llPage.subject_id LEFT JOIN llCountry ON llCountry.code = llPage.countryCode LEFT JOIN llCategory ON llCategory.id = llPage.category_id
--
Results page, my correct code:
SELECT `llPage`.`id`, `llPage`.`link2`, `llSubject`.`menu_name` AS llSubject_menu_name, `llCountry`.`country` AS llCountry_country, `llCategory`.`content` AS llCategory_content, `llPage`.`content`, `llPage`.`date_update` FROM llPage LEFT JOIN llSubject ON llSubject.id = llPage.subject_id LEFT JOIN llCountry ON llCountry.code = llPage.countryCode LEFT JOIN llCategory ON llCategory.id = llPage.category_id