[eluser]vbrtrmn[/eluser]
I've got a query which I use two left joins and several inner joins, it consistently returns a connection reset error in firefox and no error message in the apache logs.
If I remove the 'left' arguments the same error does not occur, if I run the SQL from $this->db->last_query() directly in the DB adding the LEFT joins back in, it comes back properly (either in phpMyAdmin or commandline).
The "inner" arguments don't need to be passed, I put them in there to experiment.
Code:
$this->db->select('votes.uid AS voterUID, comments.uid AS voteeUID, voteTypes.id AS voteTypeID, userratingVoter.level AS voterLevel, userratingVotee.level AS voteeLevel');
$this->db->from('votes');
# Join the comments table on comments.id = votes.cid
$this->db->join('comments', 'comments.id = votes.cid', 'inner');
# Join the userauth table for admin WHERE clause below
$this->db->join('userauth', 'userauth.uid = votes.uid', 'inner');
# Join the votesTypes table for the WHERE IN clause below
$this->db->join('voteTypes', 'voteTypes.id = votes.voteid', 'inner');
# Join the userrating table twice once for the Voter once for the Votee
$this->db->join('userrating AS userratingVotee', 'userratingVotee.uid = comments.uid', 'left');
$this->db->join('userrating AS userratingVoter', 'userratingVoter.uid = votes.uid', 'left');
# Where in valid Vote Types
# Valid Vote Types: helpful, interesting, correct, incorrect
# 5 4 2 7
$validVoteTypes = array(5,4,2,7);
$this->db->where_in('votes.voteid', $validVoteTypes);
# Where vote is active.
$this->db->where('votes.active',1);
# Where timestamp is greater than the last run timestamp.
$this->db->where('votes.timestamp >',$lastrun);
# Only standard user votes count, may have to update this if we get other authids
$this->db->where('userauth.authid',2);
Broken up SQL from $this->db->last_query()... works in phpMyAdmin and from commandline.
Code:
SELECT `votes`.`uid` AS voterUID, `comments`.`uid` AS voteeUID, `voteTypes`.`id` AS voteTypeID, `userratingVoter`.`level` AS voterLevel, `userratingVotee`.`level` AS voteeLevel
FROM (`votes`)
INNER JOIN `comments` ON `comments`.`id` = `votes`.`cid`
INNER JOIN `userauth` ON `userauth`.`uid` = `votes`.`uid`
INNER JOIN `voteTypes` ON `voteTypes`.`id` = `votes`.`voteid`
LEFT JOIN `userrating` AS userratingVotee ON `userratingVotee`.`uid` = `comments`.`uid`
LEFT JOIN `userrating` AS userratingVoter ON `userratingVoter`.`uid` = `votes`.`uid`
WHERE `votes`.`voteid` IN (5, 4, 2, 7)
AND `votes`.`active` = 1
AND `votes`.`timestamp` > '2012-03-01 00:00:00'
AND `userauth`.`authid` = 2
Further if I use a standard query instead of the active record syntax, it produces the same results.
Code:
$sql = "SELECT `votes`.`uid` AS voterUID, `comments`.`uid` AS voteeUID, `voteTypes`.`id` AS voteTypeID, `userratingVoter`.`level` AS voterLevel, `userratingVotee`.`level` AS voteeLevel FROM (`votes`) INNER JOIN `comments` ON `comments`.`id` = `votes`.`cid` INNER JOIN `userauth` ON `userauth`.`uid` = `votes`.`uid` INNER JOIN `voteTypes` ON `voteTypes`.`id` = `votes`.`voteid` LEFT JOIN `userrating` AS userratingVotee ON `userratingVotee`.`uid` = `comments`.`uid` LEFT JOIN `userrating` AS userratingVoter ON `userratingVoter`.`uid` = `votes`.`uid` WHERE `votes`.`voteid` IN (5, 4, 2, 7) AND `votes`.`active` = 1 AND `votes`.`timestamp` > '2012-03-01 00:00:00' AND `userauth`.`authid` = 2";
$query = $this->db->query($sql);
If I use a standard JOIN instead of LEFT JOIN for either the error does not occur, though it doesn't return the data I need.
Server Info:
Apache/2.2.14 (Ubuntu)
PHP/5.3.2
MySQL 5.1.63
CodeIgniter 2.1.2
application/config/database.php
Code:
$active_group = 'default';
$active_record = TRUE;
$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'some_user';
$db['default']['password'] = 'some_pass';
$db['default']['database'] = 'some_db';
$db['default']['dbdriver'] = 'mysqli';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;