Bug #7788

available fields not accurate when using custom query

Details

Submitted	2006-06-02 02:59 UTC
From	anarcat at koumbit dot org
Status	Bogus
Package	DB_DataObject
PHP Version	4.4.2
OS	Debian GNU/Linux "etch"
Roadmaps	(Not assigned)

Comments

[2006-06-02 02:59 UTC] anarcat at koumbit dot org

Description:
------------
When doing a custom query with a JOIN and a GROUP BY, I don't have access to the fields created in the query.
)

Test script:
---------------
<?php
$punches = new DB_DataObject_punch();

$punches->query('SELECT punch.id,start,end,uid,GROUP_CONCAT(raw_tag ORDER BY raw_tag SEPARATOR " ") as tags FROM `punch` INNER JOIN freetagged_objects AS f_rel ON f_rel.object_id = punch.id INNER JOIN freetags AS f ON f.id = f_rel.tag_id WHERE uid = \'' . $uid . '\' GROUP by punch.id;');

$count = $this->punches->find();

while($this->punches->fetch()) {
print_r($this->punches);
}

?>

Expected result:
----------------
I would have expected $this->punches->tags to be set in here.

Actual result:
--------------
Here is the result of the print_r:

db_dataobject_punch Object
(
[__table] => punch
[id] => 3
[uid] => 0
[start] => 0000-00-00 00:00:00
[end] => 0000-00-00 00:00:00
[_DB_DataObject_version] => 1.8.4
[N] => 7
[_database_dsn] =>
[_database_dsn_md5] => 462f63d3f928f7a535995f60c9995ae9
[_database] => time
[_DB_resultid] => 2
[_resultFields] =>
[_link_loaded] =>
[_join] =>
[_lastError] =>
)

Note how there's no tags field here.

[2006-06-02 03:04 UTC] anarcat at koumbit dot org

Sure, here's the dump.

db_dataobject_punch: query: QUERY DONE IN 0.00137901306152 seconds
db_dataobject_punch: 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:4:"time";s:8:"password";s:0:"";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:4:"time";}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:33:"SELECT *
FROM punch

";s:6:"result";i:0;s:11:"row_counter";N;s:9:"statement";N;}
db_dataobject_punch: find: CHECK autofetchd
db_dataobject_punch: find: DONE
db_dataobject_punch: FETCH: a:4:{s:2:"id";s:1:"2";s:5:"start";s:19:"0000-00-00 00:00:00";s:3:"end";s:19:"0000-00-00 00:00:00";s:3:"uid";s:1:"0";}
db_dataobject_punch: fetchrow LINE: id = 2
db_dataobject_punch: fetchrow LINE: start = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: end = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: uid = 0
db_dataobject_punch: fetchrow: punch DONE
db_dataobject_punch: FETCH: a:4:{s:2:"id";s:1:"3";s:5:"start";s:19:"0000-00-00 00:00:00";s:3:"end";s:19:"0000-00-00 00:00:00";s:3:"uid";s:1:"0";}
db_dataobject_punch: fetchrow LINE: id = 3
db_dataobject_punch: fetchrow LINE: start = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: end = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: uid = 0
db_dataobject_punch: fetchrow: punch DONE
db_dataobject_punch: FETCH: a:4:{s:2:"id";s:1:"4";s:5:"start";s:19:"0000-00-00 00:00:00";s:3:"end";s:19:"0000-00-00 00:00:00";s:3:"uid";s:1:"0";}
db_dataobject_punch: fetchrow LINE: id = 4
db_dataobject_punch: fetchrow LINE: start = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: end = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: uid = 0
db_dataobject_punch: fetchrow: punch DONE
db_dataobject_punch: FETCH: a:4:{s:2:"id";s:1:"5";s:5:"start";s:19:"0000-00-00 00:00:00";s:3:"end";s:19:"0000-00-00 00:00:00";s:3:"uid";s:1:"0";}
db_dataobject_punch: fetchrow LINE: id = 5
db_dataobject_punch: fetchrow LINE: start = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: end = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: uid = 0
db_dataobject_punch: fetchrow: punch DONE
db_dataobject_punch: FETCH: a:4:{s:2:"id";s:1:"6";s:5:"start";s:19:"0000-00-00 00:00:00";s:3:"end";s:19:"0000-00-00 00:00:00";s:3:"uid";s:1:"0";}
db_dataobject_punch: fetchrow LINE: id = 6
db_dataobject_punch: fetchrow LINE: start = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: end = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: uid = 0
db_dataobject_punch: fetchrow: punch DONE
db_dataobject_punch: FETCH: a:4:{s:2:"id";s:1:"7";s:5:"start";s:19:"0000-00-00 00:00:00";s:3:"end";s:19:"0000-00-00 00:00:00";s:3:"uid";s:1:"1";}
db_dataobject_punch: fetchrow LINE: id = 7
db_dataobject_punch: fetchrow LINE: start = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: end = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: uid = 1
db_dataobject_punch: fetchrow: punch DONE
db_dataobject_punch: FETCH: a:4:{s:2:"id";s:1:"8";s:5:"start";s:19:"0000-00-00 00:00:00";s:3:"end";s:19:"0000-00-00 00:00:00";s:3:"uid";s:1:"1";}
db_dataobject_punch: fetchrow LINE: id = 8
db_dataobject_punch: fetchrow LINE: start = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: end = 0000-00-00 00:00:00
db_dataobject_punch: fetchrow LINE: uid = 1
db_dataobject_punch: fetchrow: punch DONE
db_dataobject_punch: FETCH: N;
db_dataobject_punch: FETCH: Last Data Fetch'ed after 0.186735153198 seconds

[2006-06-02 03:16 UTC] anarcat at koumbit dot org

here's more info. here is the actual class:

class DB_DataObject_punch extends DB_DataObject {

// you can define these yourself

var $__table='punch'; // table name
var $id; // int primary_key
var $uid;
var $start;
var $end;

// these are usefull to be consistant with a autogenerated file.

/* Static get */
function staticGet($k,$v=NULL) { return DB_DataObject::staticGet('DataObjects_Punch',$k,$v); }

// now define your table structure.
// key is column name, value is type
function table() {
return array(
'id' => DB_DATAOBJECT_INT,
'uid' => DB_DATAOBJECT_INT,
'start' => DB_DATAOBJECT_STR + DB_DATAOBJECT_DATE + DB_DATAOBJECT_TIME,
'end' => DB_DATAOBJECT_INT + DB_DATAOBJECT_BOOL,
);
}

// now define the keys.
function keys() {
return array('id');
}

}

and the data structures:

CREATE TABLE `punch` (
`id` int(11) NOT NULL auto_increment,
`start` datetime NOT NULL default '0000-00-00 00:00:00',
`end` datetime NOT NULL default '0000-00-00 00:00:00',
`uid` int(100) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

CREATE TABLE freetags (
id int(10) unsigned NOT NULL auto_increment,
tag varchar(30) NOT NULL default '',
raw_tag varchar(50) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

--
-- Table structure for table `freetagged_objects`
--

CREATE TABLE freetagged_objects (
tag_id int(10) unsigned NOT NULL default '0',
tagger_id int(10) unsigned NOT NULL default '0',
object_id int(10) unsigned NOT NULL default '0',
tagged_on datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`tag_id`,`tagger_id`,`object_id`),
KEY `tag_id_index` (`tag_id`),
KEY `tagger_id_index` (`tagger_id`),
KEY `object_id_index` (`object_id`)
) TYPE=MyISAM;

i hope you can reproduce...

[2006-06-02 03:30 UTC] anarcat at koumbit dot org

Hum... i might have been missing somerhint here, but it seems to work in pma and mysql commandline.

mysql> SELECT punch.id,start,end,uid,GROUP_CONCAT(raw_tag ORDER BY raw_tag SEPARATOR " ") as tags FROM `punch` INNER JOIN freetagged_objects AS f_rel ON f_rel.object_id = punch.id INNER JOIN freetags AS f ON f.id = f_rel.tag_id WHERE uid = '1' GROUP by punch.id;
+----+---------------------+---------------------+-----+---------+
| id | start | end | uid | tags |
+----+---------------------+---------------------+-----+---------+
| 7 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 1 | bar foo |
| 8 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 1 | bar foo |
+----+---------------------+---------------------+-----+---------+
2 rows in set (0.00 sec)

[2006-06-02 03:39 UTC] anarcat at koumbit dot org

i'm sorry, i have to go, i'll try it first thing in the morning. if you have a code snippet i could just cut and paste, that would be useful.

thanks for the quick exchange! :)

[2006-06-02 11:59 UTC] anarcat at koumbit dot org

That's not it, DB behaves properly:

<?php

chdir('..');
require_once 'lib/conf.php';

$query = 'SELECT punch.id,start,end,uid,GROUP_CONCAT(raw_tag ORDER BY
raw_tag SEPARATOR " ") as tags FROM `punch` INNER JOIN
freetagged_objects AS f_rel ON f_rel.object_id = punch.id INNER JOIN
freetags AS f ON f.id = f_rel.tag_id WHERE uid = \'1\' GROUP by
punch.id;';

require_once 'DB.php';

$db =& DB::connect(_DSN);
if (PEAR::isError($db)) {
die($db->getMessage());
}

// Proceed with a query...
$res =& $db->query($query);

// Always check that result is not an error
if (PEAR::isError($res)) {
die($res->getMessage());
}

while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
print_r($row);
}

result:

Array ( [id] => 7 [start] => 0000-00-00 00:00:00 [end] => 0000-00-00 00:00:00 [uid] => 1 [tags] => bar foo ) Array ( [id] => 8 [start] => 0000-00-00 00:00:00 [end] => 0000-00-00 00:00:00 [uid] => 1 [tags] => bar foo ) Array ( [id] => 9 [start] => 0000-00-00 00:00:00 [end] => 0000-00-00 00:00:00 [uid] => 1 [tags] => avec aux bar bleus de foire foo plein poissons quux trous )

[2006-06-02 12:19 UTC] anarcat at koumbit dot org

oooooh!!!

when I remove the find() call, everything works out ok!

It turns out that that find() call clears the query and does a SELECT *... so that's probably where the bug is?