通过 CodeIgniter 活动记录查询从两个 LEFT JOINed 表获取非笛卡尔计数

Question

我有这样的代码，可以从数据库中提取一些数据：

$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();

我得到了一些奇怪的结果，下面是结果转储的照片，看起来像在 company_likes 表中有 0 条记录，在 company view 表中有 6 条记录，

Array
(
    [0] => Array
        (
            [job_id] => 1
            [employer_id] => 1
            [views] => 6
            [likes] => 0
            [logo] => 11d4df5e2f7db152cd9bcc3782dd03b0.jpg
            [company_name] => Test Company
        )
)

但是如果我在 company_views 表中有 6 条记录，在 company Likes 表中有 1 条记录，我得到以下信息，

Array
(
    [0] => Array
        (
            [job_id] => 1
            [employer_id] => 1
            [views] => 6
            [likes] => 6
            [logo] => 11d4df5e2f7db152cd9bcc3782dd03b0.jpg
            [company_name] => Test Company
        )
)

就好像视图和喜欢数成倍增加一样，我怎样才能使我得到的内容真实地代表了内容在 数据库？

这是相关数据和表格的导出，

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

您是否考虑过分解查询？您可以创建一个结合部分查询的临时表，然后使用第二个查询完成初始查询的其他部分！

我不相信活动记录类有任何东西可以创建临时表，因此您需要手动执行此操作。

我还可以看到查询被分解为更小的块，并使用 php 为您进行一些比较和计数。

否则，一些示例数据和表创建信息可能有用，因此我们也可以尝试一下查询。

Answer 2

由于 company_* 表连接，您确实在计数上遇到了笛卡尔效应。

为了防止计数相乘，您可以连接包含每个表计数的子查询。

$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();