Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 1.8.12

Request #9389 JoinAdd / SelectAs overwriting each other
Submitted: 2006-11-19 17:36 UTC Modified: 2006-11-20 21:29 UTC
From: Bill at Explosivo dot com Assigned:
Status: Open Package: DB_DataObject (version 1.8.4)
PHP Version: 4.3.10 OS: Linux / Windows
Roadmaps: (Not assigned)    
Subscription  
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes. If this is not your bug, you can add a comment by following this link. If this is your bug, but you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: Bill at Explosivo dot com
New email:
PHP Version: Package Version: OS:

 

 [2006-11-19 17:36 UTC] Bill at Explosivo dot com (Bill Chmura)
Description: ------------ When I joinAdd two tables, and use selectAs to keep the fields from one from clashing with the other, they simply overwrite the contents of the first field. $BUSINESS = DB_DataObject::factory("Business"); $BUSINESS->setId($buid); $PERSON = DB_DataObject::factory("Person"); $BUSINESS->joinadd($PERSON); $BUSINESS->selectAs($PERSON,'person_%s'); With this example, if I have: BUSINESS.CITY = "BC" PERSON.CITY = "PC" I will get results containing: CITY= "PC" person_city = "PC" My Query runs like: QUERY: SELECT *, `person`.`id` as `person_id` , `person`.`firstname` as `person_firstname` , `person`.`lastname` as `person_lastname` , `person`.`address1` as `person_address1` , `person`.`address2` as `person_address2` , `person`.`city` as `person_city` , `person`.`state` as `person_state` , `person`.`zipcode` as `person_zipcode` , `person`.`latitude` as `person_latitude` , `person`.`longitude` as `person_longitude` , `person`.`gcscore` as `person_gcscore` , `person`.`contactphone` as `person_contactphone` , `person`.`birthdate` as `person_birthdate` , `person`.`role` as `person_role` , `person`.`activeind` as `person_activeind` , `person`.`email` as `person_email` , `person`.`updatedate` as `person_updatedate` , `person`.`accessdate` as `person_accessdate` , `person`.`passwordx` as `person_passwordx` , `person`.`passphrase` as `person_passphrase` FROM `business` INNER JOIN `findwellnesspros`.`person` ON `findwellnesspros`.`person`.`id`=`business`.`customerid` WHERE ( `business`.`id` = 77590 ) Which if I cut it out and run it manually returns the data correctly (appears so). Anyway, this comes back from debug level 5 dataobjects_business: RESULT: O:9:"db_result":11:{s:8:"autofree";b:0;s:3:"dbh";O:8:"db_mysql":8:{s:10:"autocommit";b:1;s:8:"dbsyntax";s:5:"mysql";s:3:"dsn";a:9:{s:7:"phptype";s:5:"mysql";s:8:"dbsyntax";s:5:"mysql";s:8:"username";s:8:"fwpuser1";s:8:"password";s:13:"working101dog";s:8:"protocol";s:3:"tcp";s:8:"hostspec";s:9:"localhost";s:4:"port";b:0;s:6:"socket";b:0;s:8:"database";s:16:"findwellnesspros";}s:8:"features";a:7:{s:5:"limit";s:5:"alter";s:8:"new_link";s:5:"4.2.0";s:7:"numrows";b:1;s:8:"pconnect";b:1;s:7:"prepare";b:0;s:3:"ssl";b:0;s:12:"transactions";b:1;}s:9:"fetchmode";i:1;s:22:"fetchmode_object_class";s:8:"stdClass";s:7:"options";a:8:{s:16:"result_buffering";i:500;s:10:"persistent";b:0;s:3:"ssl";b:0;s:5:"debug";i:0;s:14:"seqname_format";s:6:"%s_seq";s:8:"autofree";b:0;s:11:"portability";i:0;s:8:"optimize";s:11:"performance";}s:13:"was_connected";b:1;}s:9:"fetchmode";i:1;s:22:"fetchmode_object_class";s:8:"stdClass";s:11:"limit_count";N;s:10:"limit_from";N;s:10:"parameters";a:0:{}s:5:"query";s:1045:"SELECT *, `person`.`id` as `person_id` , `person`.`firstname` as `person_firstname` , `person`.`lastname` as `person_lastname` , `person`.`address1` as `person_address1` , `person`.`address2` as `person_address2` , `person`.`city` as `person_city` , `person`.`state` as `person_state` , `person`.`zipcode` as `person_zipcode` , `person`.`latitude` as `person_latitude` , `person`.`longitude` as `person_longitude` , `person`.`gcscore` as `person_gcscore` , `person`.`contactphone` as `person_contactphone` , `person`.`birthdate` as `person_birthdate` , `person`.`role` as `person_role` , `person`.`activeind` as `person_activeind` , `person`.`email` as `person_email` , `person`.`updatedate` as `person_updatedate` , `person`.`accessdate` as `person_accessdate` , `person`.`passwordx` as `person_passwordx` , `person`.`passphrase` as `person_passphrase` FROM `business` INNER JOIN `findwellnesspros`.`person` ON `findwellnesspros`.`person`.`id`=`business`.`customerid` WHERE ( `business`.`id` = 77590) ";s:6:"result";i:0;s:11:"row_counter";N;s:9:"statement";N;} Then when I try to get the data: dataobjects_business: FETCH: a:63:{s:2:"id";s:1:"2";s:10:"customerid";s:1:"2";s:4:"name";s:13:"My Big Muscle";s:9:"activeind";s:1:"0";s:5:"email";s:18:"bill@explosivo.com";s:10:"websiteurl";s:24:"http://www.explosivo.com";s:8:"address1";s:8:"74 UpUrs";s:8:"address2";s:0:"";s:4:"city";s:11:"New Britian";s:5:"state";s:2:"CT";s:7:"zipcode";s:5:"06489";s:8:"latitude";s:8:"0.000000";s:9:"longitude";s:8:"0.000000";s:7:"gcscore";s:1:"0";s:12:"contactphone";s:10:"8606215380";s:10:"contactfax";s:0:"";s:9:"photofile";s:0:"";s:10:"updatedate";N;s:15:"servicelocation";s:6:"office";s:10:"traveldist";s:1:"0";s:13:"referencesind";s:1:"0";s:12:"callbackdays";s:1:"0";s:11:"hoursmonbeg";s:1:"0";s:11:"hoursmonend";s:1:"0";s:11:"hourstuebeg";s:1:"0";s:11:"hourstueend";s:1:"0";s:11:"hourswedbeg";s:1:"0";s:11:"hourswedend";s:1:"0";s:11:"hoursthubeg";s:1:"0";s:11:"hoursthuend";s:1:"0";s:11:"hoursfribeg";s:1:"0";s:11:"hoursfriend";s:1:"0";s:11:"hourssatbeg";s:1:"0";s:11:"hourssatend";s:1:"0";s:11:"hourssunbeg";s:1:"0";s:11:"hourssunend";s:1:"0";s:9:"firstname";s:4:"Bill";s:8:"lastname";s:6:"Chmura";s:9:"birthdate";s:10:"1969-08-08";s:4:"role";s:13:"administrator";s:10:"accessdate";N;s:9:"passwordx";s:0:"";s:10:"passphrase";s:0:"";s:9:"person_id";s:1:"2";s:16:"person_firstname";s:4:"Bill";s:15:"person_lastname";s:6:"Chmura";s:15:"person_address1";s:8:"74 UpUrs";s:15:"person_address2";s:0:"";s:11:"person_city";s:11:"New Britian";s:12:"person_state";s:2:"CT";s:14:"person_zipcode";s:5:"06489";s:15:"person_latitude";s:8:"0.000000";s:16:"person_longitude";s:8:"0.000000";s:14:"person_gcscore";s:1:"0";s:19:"person_contactphone";s:10:"8606215380";s:16:"person_birthdate";s:10:"1969-08-08";s:11:"person_role";s:13:"administrator";s:16:"person_activeind";s:1:"0";s:12:"person_email";s:18:"bill@explosivo.com";s:17:"person_updatedate";N;s:17:"person_accessdate";N;s:16:"person_passwordx";s:0:"";s:17:"person_passphrase";s:0:"";} dataobjects_business: fetchrow LINE: id = 2 dataobjects_business: fetchrow LINE: customerid = 2 dataobjects_business: fetchrow LINE: name = My Big Muscle dataobjects_business: fetchrow LINE: activeind = 0 dataobjects_business: fetchrow LINE: email = bill@explosivo.com dataobjects_business: fetchrow LINE: websiteurl = http://www.explosivo.com dataobjects_business: fetchrow LINE: address1 = 74 UpUrs dataobjects_business: fetchrow LINE: address2 = dataobjects_business: fetchrow LINE: city = New Britian dataobjects_business: fetchrow LINE: state = CT dataobjects_business: fetchrow LINE: zipcode = 06489 dataobjects_business: fetchrow LINE: latitude = 0.000000 dataobjects_business: fetchrow LINE: longitude = 0.000000 dataobjects_business: fetchrow LINE: gcscore = 0 dataobjects_business: fetchrow LINE: contactphone = 8606215380 dataobjects_business: fetchrow LINE: contactfax = dataobjects_business: fetchrow LINE: photofile = dataobjects_business: fetchrow LINE: updatedate = dataobjects_business: fetchrow LINE: servicelocation = office dataobjects_business: fetchrow LINE: traveldist = 0 dataobjects_business: fetchrow LINE: referencesind = 0 dataobjects_business: fetchrow LINE: callbackdays = 0 dataobjects_business: fetchrow LINE: hoursmonbeg = 0 dataobjects_business: fetchrow LINE: hoursmonend = 0 dataobjects_business: fetchrow LINE: hourstuebeg = 0 dataobjects_business: fetchrow LINE: hourstueend = 0 dataobjects_business: fetchrow LINE: hourswedbeg = 0 dataobjects_business: fetchrow LINE: hourswedend = 0 dataobjects_business: fetchrow LINE: hoursthubeg = 0 dataobjects_business: fetchrow LINE: hoursthuend = 0 dataobjects_business: fetchrow LINE: hoursfribeg = 0 dataobjects_business: fetchrow LINE: hoursfriend = 0 dataobjects_business: fetchrow LINE: hourssatbeg = 0 dataobjects_business: fetchrow LINE: hourssatend = 0 dataobjects_business: fetchrow LINE: hourssunbeg = 0 dataobjects_business: fetchrow LINE: hourssunend = 0 dataobjects_business: fetchrow LINE: firstname = Bill dataobjects_business: fetchrow LINE: lastname = Chmura dataobjects_business: fetchrow LINE: birthdate = 1969-08-08 dataobjects_business: fetchrow LINE: role = administrator dataobjects_business: fetchrow LINE: accessdate = dataobjects_business: fetchrow LINE: passwordx = dataobjects_business: fetchrow LINE: passphrase = dataobjects_business: fetchrow LINE: person_id = 2 dataobjects_business: fetchrow LINE: person_firstname = Bill dataobjects_business: fetchrow LINE: person_lastname = Chmura dataobjects_business: fetchrow LINE: person_address1 = 74 UpUrs dataobjects_business: fetchrow LINE: person_address2 = dataobjects_business: fetchrow LINE: person_city = New Britian dataobjects_business: fetchrow LINE: person_state = CT dataobjects_business: fetchrow LINE: person_zipcode = 06489 dataobjects_business: fetchrow LINE: person_latitude = 0.000000 dataobjects_business: fetchrow LINE: person_longitude = 0.000000 dataobjects_business: fetchrow LINE: person_gcscore = 0 dataobjects_business: fetchrow LINE: person_contactphone = 8606215380 dataobjects_business: fetchrow LINE: person_birthdate = 1969-08-08 dataobjects_business: fetchrow LINE: person_role = administrator dataobjects_business: fetchrow LINE: person_activeind = 0 dataobjects_business: fetchrow LINE: person_email = bill@explosivo.com dataobjects_business: fetchrow LINE: person_updatedate = dataobjects_business: fetchrow LINE: person_accessdate = dataobjects_business: fetchrow LINE: person_passwordx = dataobjects_business: fetchrow LINE: person_passphrase = dataobjects_business: fetchrow: business DONE The business city should be returned as southington. $BUSINESS = DB_DataObject::factory("Business"); $BUSINESS->setId($buid); $PERSON = DB_DataObject::factory("Person"); $BUSINESS->joinadd($PERSON); $BUSINESS->selectAs($PERSON,'person_%s'); $BUSINESS->selectAs($BUSINESS,'business_%s'); Now, if I do this... I get back the original data as shown above, the person table, and the business_ also. The Person_ and the Business_ both show correct info, but the orignal business data fields are overwritten. Anything I can do to help, or if you can tell me where to look at the code, or if I am an idiot... let me know Expected result: ---------------- SelectAs fields should not overwrite the default fields

Comments

 [2006-11-19 20:37 UTC] Bill at Explosivo dot com
Just doing a selectAs on a single table, without joins, produces the duplication of field names also... orginalfields renamed_orignalfields
 [2006-11-20 00:32 UTC] alan_k (Alan Knowles)
I guess technically we should do some kind of warning if you do a selectAs() on a table that has a select value of "*" the fix for your code is to do add a selectAdd() call. $BUSINESS->joinadd($PERSON); $BUSINESS->selectAdd(); $BUSINESS->selectAs($PERSON,'person_%s'); or $BUSINESS->joinadd($PERSON); $BUSINESS->selectAs(); $BUSINESS->selectAs($PERSON,'person_%s'); (which will default to adding all the buisness columns as their real name). unfortunatly, changing anything runs the risk of breaking existing applications. (which while may be illustrating a bug in the code, still work..) I'm changing it to a feature request for DataObject2... which I really must get on writing..
 [2006-11-20 16:30 UTC] Bill at Explosivo dot com
Thank you for the rapid response on this. I think it is definately in order to add to the documentation for the joinAdd that this behavior exists and refer to the selectAll() statement as the solution. So I have two objects (BUSINESS and PERSON) and I join them. Can I have all the fields as BUSINESS just as they are and the PERSON ones renamed? $BUSINESS->joinadd($PERSON); $BUSINESS->selectAdd(); $BUSINESS->selectAs($BUSINESS,'%s'); $BUSINESS->selectAs($PERSON,'person_%s'); I am not near the code for a little while and the suspense on if that will work is killing me. If I can help by writing a patch to joinAdd docs let me know and I will submit it. No problem on dbdataobjects2 ... I do cpmFetch http://coppermine-gallery.net/forum/index.php?board=87.0 and have had my v 2 release on the table for months :)
 [2006-11-20 21:29 UTC] alan_k (Alan Knowles)
Actually, this is the ideal way to do that. - selectAs() with no arguments, removes the *, and adds all the objects select items for you. $BUSINESS->joinadd($PERSON); $BUSINESS->selectAs(); $BUSINESS->selectAs($PERSON,'person_%s');