Get non-Cartesian count from two LEFT JOINed tables via CodeIgniter active record query

Question

I have this code that pulls out some data from a database:

$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,

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,

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,

--
-- 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`
--

INSERT INTO `company_views` (`view_id`, `company_job_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 1);

-- --------------------------------------------------------

--
-- Table structure for table `employers`
--

CREATE TABLE IF NOT EXISTS `employers` (
  `employer_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(80) NOT NULL,
  `company_summary` text NOT NULL,
  `logo` varchar(60) NOT NULL,
  `alternative_ads` varchar(100) DEFAULT NULL,
  `facebook_url` varchar(100) DEFAULT NULL,
  `twitter_url` varchar(100) DEFAULT NULL,
  `user_id` int(10) NOT NULL,
  PRIMARY KEY (`employer_id`),
  KEY `fk_employers_users` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `employers`
--

INSERT INTO `employers` (`employer_id`, `company_name`, `company_summary`, `logo`, `alternative_ads`, `facebook_url`, `twitter_url`, `user_id`) VALUES
(1, 'Test Company', 'Test company is excatly what it says it is a test company, we have created this test company so that we can see that moovjob is functioning as it should be and that everything is upload, saving, applying and generally saving as we would expect.', '11d4df5e2f7db152cd9bcc3782dd03b0.jpg', 'http://www.simonainley.info/alternative', 'http://www.facebook.com/simon.ainley', 'http://www.twitter.com/simonainley', 2),
(2, 'Test Company', 'Test company summary', '006474cf842654eb28deebec7e4dcbb9.png', 'http://www.simonainley.info/alternative', 'http://www.facebook.com/simon.ainley', 'http://www.twitter.com/simonainley', 5);

-- --------------------------------------------------------

--
-- Table structure for table `jobs`
--

CREATE TABLE IF NOT EXISTS `jobs` (
  `job_id` int(11) NOT NULL AUTO_INCREMENT,
  `job_title` varchar(80) NOT NULL,
  `sectors` varchar(255) NOT NULL,
  `salary` varchar(20) NOT NULL,
  `retrain` enum('yes','no') NOT NULL,
  `bonuses_available` enum('yes','no') NOT NULL,
  `bonus_description` text,
  `job_summary` text NOT NULL,
  `job_description` text NOT NULL,
  `employer_id` int(11) NOT NULL,
  PRIMARY KEY (`job_id`),
  KEY `fk_jobs_employers1` (`employer_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `jobs`
--

INSERT INTO `jobs` (`job_id`, `job_title`, `sectors`, `salary`, `retrain`, `bonuses_available`, `bonus_description`, `job_summary`, `job_description`, `employer_id`) VALUES
(1, 'Test Jobtitle', 'Sector 1', '£25,000', 'no', 'yes', 'Bonus Description', 'Job Summary', 'Job Description', 1);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `company_views`
--
ALTER TABLE `company_views`
  ADD CONSTRAINT `company_views_ibfk_1` FOREIGN KEY (`company_job_id`) REFERENCES `jobs` (`employer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `employers`
--
ALTER TABLE `employers`
  ADD CONSTRAINT `fk_employers_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `jobs`
--
ALTER TABLE `jobs`
  ADD CONSTRAINT `fk_jobs_employers1` FOREIGN KEY (`employer_id`) REFERENCES `employers` (`employer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Answer 1

You are indeed experiencing a Cartesian effect on the counts because of the company_* table joins.

To prevent the multiplication of counts, you can join subqueries which contain the counts per table.

$likes = $this->db
    ->select('like_job_id, COUNT(like_job_id) likes')
    ->group_by('like_job_id')
    ->get_compiled_select('company_likes');

$views = $this->db
    ->select('company_job_id, COUNT(company_job_id) views')
    ->group_by('company_job_id')
    ->get_compiled_select('company_views');

return $this->db->select([
        'jobs.job_id',
        'jobs.employer_id',
        'employers.logo',
        'employers.company_name',
        'COALESCE(likes_sq.likes, 0) likes',
        'COALESCE(views_sq.views, 0) views',
    ])
    ->join('employers', 'employers.employer_id = jobs.employer_id')
    ->join("($likes) likes_sq", 'jobs.employer_id = likes_sq.like_job_id', 'LEFT')
    ->join("($views) views_sq", 'jobs.employer_id = views_sub.company_job_id', 'LEFT')
    ->group_by([
        'jobs.job_id',
        'jobs.employer_id',
        'employers.logo',
        'employers.company_name',
    ])
    ->get('jobs')
    ->result_array();

Answer 2

Have you thought of breaking the query down? You could create a temporary table combining part of your query and then finish up the other portion of the initial query with a second query!

I do not believe the active record class has anything to create a temporary table so you would need to do that by hand.

I could also see the query being broken down into smaller chunks and using php to do some of the comparison and counting for you.

Otherwise some sample data and table creation info might be useful so we can give the query a try as well.