[eluser]Unknown[/eluser]
Hello,
I have this code that pulls out some data from a database,
Code:
$this->db->select('job_id, jobs.employer_id, COUNT(company_job_id) AS views, COUNT(like_job_id) AS likes, logo, company_name')
->from('jobs')
->join('company_likes', 'company_likes.like_job_id = jobs.employer_id', 'left')
->join('company_views', 'company_views.company_job_id = jobs.employer_id', 'left')
->join('employers', 'employers.employer_id = jobs.employer_id', 'left')
->group_by('company_views.company_job_id');
$query = $this->db->get();
return $query->result_array();
I am getting some strange results, below is shot of the dump of results looks like when there are 0 records in the company_likes table and 6 records in the company views table,
Code:
Array
(
[0] => Array
(
[job_id] => 1
[employer_id] => 1
[views] => 6
[likes] => 0
[logo] => 11d4df5e2f7db152cd9bcc3782dd03b0.jpg
[company_name] => Test Company
)
)
However if I have 6 records in the company_views table and 1 record in the company likes table I get the following,
Code:
Array
(
[0] => Array
(
[job_id] => 1
[employer_id] => 1
[views] => 6
[likes] => 6
[logo] => 11d4df5e2f7db152cd9bcc3782dd03b0.jpg
[company_name] => Test Company
)
)
It is as if views and likes are getting multiplied or something, how can I make so what I get is a true representation of the what is in the database?
Here is an export of the data and tables in question,
Code:
--
-- Table structure for table `company_likes`
--
CREATE TABLE IF NOT EXISTS `company_likes` (
`like_id` int(10) NOT NULL AUTO_INCREMENT,
`like_job_id` int(11) NOT NULL,
PRIMARY KEY (`like_id`),
KEY `fk_company_likes_jobs1` (`like_job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `company_likes`
--
INSERT INTO `company_likes` (`like_id`, `like_job_id`) VALUES
(1, 1);
-- --------------------------------------------------------
--
-- Table structure for table `company_views`
--
CREATE TABLE IF NOT EXISTS `company_views` (
`view_id` int(10) NOT NULL AUTO_INCREMENT,
`company_job_id` int(11) NOT NULL,
PRIMARY KEY (`view_id`),
KEY `fk_company_views_jobs1` (`company_job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `company_views`
--
