php - Mysql - Codeigniter 事件记录帮助

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条记录和companyviews表中有6条记录时结果转储的截图，

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

)

但是，如果我在company_views表中有6条记录，在companylikes表中有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

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

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

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

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