Only JOIN the related record with the highest column value using Codeigniter

Question

I have three tables

1.article (id,article_title)

2.article_status(id,article_id,stage_id)

3.article_stages(id,stage_name)

Each article have multiple insertion in table 2. I want to fetch the last value which indicate the current stage using join query

article

id article_title

1   article1
2   article2

article_status

id   article_id   stage_id
1     1             1
2     1             2
3     1             3
4     2             1

article_stages

id  stage_name  
1    Stage1            
2    Stage2  
3    Stage3

I need the result like

id  article_title  stage_name
1   article1       Stage3
2   article2       Stage1

Here is my query

"SELECT `article`.`article_title` as id,`article`.`article_title`,`article_stages`.`stage_name`, MAX(`article_status`.`stage_id`) AS stage_id FROM (`article`)
JOIN `article_status` ON `article_status`.`article_id`=`article`.`id`
JOIN `article_stages` ON `article_stages`.`id` = (SELECT MAX(`stage_id`) FROM `article_status` )
GROUP BY `article_status`.`article_id`"

I tried this

public function getAllArticleforIssue($condition = array())
{
    $table1 = 'article';
    $table2 = 'article_status';
    $table3 ='article_stages';      
    $this->db->select($table1 . '.id as id,' . $table1 . '.article_title, ' . $table3 . '.stage_name');
    $this->db->select_max($table2 . '.stage_id');
    $this->db->from('rsgp_article');
    $this->db->join($table2, $table2 . '.article_id=' . $table1 . '.id');
    $this->db->join($table3, $table3 . '.id = ' . $table2 . '.stage_id'); 
    if (count($condition) > 0)
        $this->db->where($condition);
    $this->db->group_by($table2 . ".article_id"); 
    $query = $this->db->get();
    return $query->result_array();
}

I would suggest you to first group your article status table and then join to article and article_stages. Grouping table first reduces joining time. — Fahad Anjum
– Fahad Anjum, Commented Oct 3, 2017 at 10:36
Hi! You have now given us lots of details of what you're trying to do, and what you've tried, but I still don't see a description of what problem you are encountering - does your current code give an error? Or does it give a different result than you wanted? Be as specific as you can. — IMSoP
– IMSoP, Commented Oct 3, 2017 at 10:48

Fahad Anjum · Accepted Answer · 2017-10-03 11:00:56Z

1

Query Explanation:

Find article_id and current_stage_id ( see subquery groupedTable)
join with article and article_stages table in order to get
description.

SQL:

select article_id, article_title, stage_name  
from ( select article_id , max(stage_id) as current_Stage_ID
       from article_status
       group by article_id ) groupedTable 
join article on article.id = groupedTable.article_id
join article_stages on article_stages.id = groupedTable.current_Stage_ID

PHP:

function function_name() { 
       $sql = "select article_id, article_title, stage_name  
       from ( select article_id , max(stage_id) as current_Stage_ID
       from article_status
       group by article_id ) groupedTable join article on article.id = groupedTable.article_id
       join article_stages on article_stages.id = groupedTable.current_Stage_ID";
       $query = $this -> db -> query($sql);
       $result = $query -> result_array();
       return $result;
    }

edited Oct 3, 2017 at 11:00

answered Oct 3, 2017 at 10:49

Fahad Anjum

1,2561 gold badge10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Fahad Anjum Over a year ago

@Ann Have you tried above query. is it helpful or not ?

Ann Over a year ago

i need to include where condition also

Fahad Anjum Over a year ago

Could you please explain about your condition as well ?

Ann Over a year ago

there are more colums in article table, like issue_no , journal_code,accode etc i need to fetch the data with condition like $condition = array("issue_no " => NULL,"journal_code"=>$jnl); I need to send condition as argument from controller

Fahad Anjum Over a year ago

You just have to make condition and put it in proper place. You can also accept the answer if it solves your problem.

Roshan Dandgavhal · Accepted Answer · 2017-10-03 10:53:37Z

0

Use this query

SELECT    a.article_title,s.stage_name
FROM      article a
JOIN      (
              SELECT    MAX(stage_id) max_id, article_id 
              FROM      article_status 
              GROUP BY  article_id
          ) b ON (b.article_id = a.article_id)
JOIN      article_stages s ON (s.id = b.max_id)

answered Oct 3, 2017 at 10:53

Roshan Dandgavhal

5593 silver badges12 bronze badges

Comments

khairulbahri · Accepted Answer · 2017-10-03 11:15:49Z

0

try this query

$this->db->select('a.id, a.article_title, c.stage_name');
$this->db->from('article a');
$this->db->join('article_status b','b.article_id=a.id','left');
$this->db->join('article_stage c','c.id=b.article_status', 'left');
$this->db->group_by('a.article_name');
$this->db->get()->result_array();

answered Oct 3, 2017 at 11:15

khairulbahri

173 bronze badges

Comments

Pankaj Pawar · Accepted Answer · 2017-10-03 12:29:32Z

0

You can use this query

SELECT    a.id,a.artcle_title,s.stage_name
FROM      article a
JOIN      (
              SELECT    MAX(stage_id) max_id, article_id 
              FROM      article_status 
              GROUP BY  article_id
          ) b ON (b.article_id = a.id)
JOIN      article_stages s ON (s.id = b.max_id)

With ID Column...

modified answer of - @Roshan Dandgavhal

edited Oct 3, 2017 at 12:29

answered Oct 3, 2017 at 11:02

Pankaj Pawar

11 silver badge2 bronze badges

1 Comment

Pankaj Pawar Over a year ago

@RoshanDandgavhal - there is some error in your ans, and some more result column added.

Collectives™ on Stack Overflow

Only JOIN the related record with the highest column value using Codeigniter

4 Answers 4

5 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related