Home » Database » DB_DataObject » Bug #9389

[2006-11-19 22:36 UTC] Bill at Explosivo dot com

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

[2006-11-20 01: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 21: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 :)