Creating Active Record Query

[eluser]xtremer360[/eluser]
I'm trying to figure out how I can come up with this query to match the active record functions.

Code:

+---------------+               +---------------+               +---------------+
|   Title       |               | Title_champion|               |   Roster      |
+---------------+               +---------------+               +---------------+
|  id           | ------------< |  title_id     |      +------- |  id           |
|  title_name   |               |  contender_no |      |        |  roster_name  |
+---------------+               |  roster_id    | >----+        +---------------+
                                +---------------+              

SELECT t.title_name,
       CASE WHEN c.contender_no = 0 THEN 'Champion' ELSE 'Contender' END as champion,
       r.contender_no,
       r.roster_name
FROM title t
       INNER JOIN title_champions c ON t.id = c.title_id
       INNER JOIN roster r ON c.roster_id = r.id

Here's what I have so far.

Code:

/**
  * Get all title champions and contenders
  *
  * @return object
  */
    function get_title_champions()
    {
        $this->db->select($this->titles_table.'.title_name');
        $this->db->select($this->titles_table.'.id');
        $this->db->select($this->roster_table.'.roster_name');
        $this->db->select($this->titles_table.'.id');
        $this->db->from($this->titles_table);
        $this->db->join($this->roster_table, $this->titles_table.'.roster_id ='. $this->roster_table.'.id', 'inner');
        $this->db->join($this->title_champions_table, $this->titles_table.'.id ='. $this->title_champions_table.'.title_id', 'inner');
        $query = $this->db->get();
        
        if ($query->num_rows() > 0) return $query->result();
  return array();
    }

[eluser]Karman de Lange[/eluser]
try

Code:

$this->db->select($this->titles_table.'.title_name');
$this->db->select($this->roster_table.'.condender_no');

$this->db->select($this->roster_table.'.roster_name');
        $sl = "CASE WHEN $this->title_champions_table.contender_no = 0 THEN 'Champion' ELSE 'Contender' END as champion"
$this->db->select($sl,false);

$this->db->from($this->titles_table);
$this->db->join($this->roster_table, $this->titles_table.'.roster_id ='. $this->roster_table.'.id', 'inner');
$this->db->join($this->title_champions_table, $this->titles_table.'.id ='. $this->title_champions_table.'.title_id', 'inner');
      
$query = $this->db->get();

just make sure you escape all correctley as the false sayd don't escape.

L:

[eluser]CroNiX[/eluser]
This part of the query should all be wrapped in double quotes. All of those single quotes within single quotes will break it:

Code:

$this->db->select("CASE WHEN c.contender_no = 0 THEN ‘Champion’ ELSE ‘Contender’ END as champion", FALSE);

[eluser]Karman de Lange[/eluser]
corrected my answer.. had few mistakes.. probebly some left as well..

[eluser]xtremer360[/eluser]
I tried the following and have an invalid ut8 character string "champion" error

Code:

$this->db->select($this->titles_table.'.title_name');
        $this->db->select($this->roster_table.'.condender_no');

        $this->db->select($this->roster_table.'.roster_name');
        $this->db->select("CASE WHEN c.contender_no = 0 THEN ‘Champion’ ELSE ‘Contender’ END as champion", FALSE);
        $this->db->select($sl,false);

        $this->db->from($this->titles_table);
        $this->db->join($this->roster_table, $this->titles_table.'.roster_id ='. $this->roster_table.'.id', 'inner');
        $this->db->join($this->title_champions_table, $this->titles_table.'.id ='. $this->title_champions_table.'.title_id', 'inner');
      
        $query = $this->db->get();  
        if ($query->num_rows() > 0) return $query->result();
  return array();

[eluser]Karman de Lange[/eluser]
Try changing your qoutes from ‘’ to ''

[eluser]CroNiX[/eluser]
The forum does that to single quotes. Most likely they are correct in the original code.

[eluser]xtremer360[/eluser]
Is there a better way to write this query altogether?

So it comes out like this?

Code:

[ 0 ] Title One name
     [champion] Jeff
     [contender1] kevin
     [contentder2] brian
     [contender3] will

[1] Title Two name
     [champion] Jeff
     [contender1] kevin
     [contentder2] brian
     [contender3] will

[2] Title Three name
     [champion] Jeff
     [contender1] kevin
     [contentder2] brian
     [contender3] will

[eluser]xtremer360[/eluser]
Just wondering if there's a better way to create this query?