Doing a "SELECT ... as" via CodeIgniter?

[eluser]FuturShoc[/eluser]
I have most of my MySQL working well right now, but I need to add some additional data at the end of my result set. This additional info has to come from another, related table outside the original result set table.

Typically, using raw MySQL, I would do something like a subselect and add that as a new column onto my orginal result set using the "as" keyword.

Does anyone have any experience using subselects and the "as" keyword within the CI Active Record scenario?

Any guidance is greatly appreciated.

[eluser]Michael Wales[/eluser]
CodeIgniter can accept AS... within it's select() method, for example:

Code:

$this->db->select('users.*, avatars.url AS avatar_url');

What exactly is the scenario you are working with - I must admit I've never used a subselect within MySQL but it sounds awfully similar to a JOIN statement.

Code:

$this->db->select('posts.*, users.username AS username');
// Tells us how to make the relationship between the posts and users table
$this->db->join('users', 'posts.author_id = users.id');
$this->db->get('posts');

[eluser]leber[/eluser]
Hi dudes

Sub-Selects have nothing to do with joins...but i must also admit that i have never seen a sub-query in a context other than WHERE or FROM (which will not 'add' anything to the result)..at least not for mysql-querys.

have a look on http://www.unixreview.com/documents/s=8989/ur0407e/ for sub-querys in general.

I guess that sub-querys are not possible if using CIs active-records...but should work fine if using the "normal" query function and mysql >= 5 (mysql 4.xx doesn't support sub-querys)

[eluser]hvalente13[/eluser]

I guess that sub-querys are not possible if using CIs active-records

I'm guessing that's because CI is multi database driven... (read: it has multiple database drivers) and not all of database engines have the ability to do the same as MYSQL

[eluser]jcopling[/eluser]
I use sub-queries pretty regularly along with the CI AR. However, the implementation does go slightly off the beaten path.

Code:

$this->db->select('tblSomething.*, (SELECT SUM(FieldName) FROM tblSomethingElse WHERE tblSomethingElse.SomethingID = tblSomething.SomethingID) AS SomethingElse');
// so on and so forth

Obviously this isn't utilizing the AR exclusively but it's the only way I get sub-queries to work.

Another way that I have found to 'extend' my queries with CI is doing something like this:

Code:

$dealer = $this->db->get('tblDealer');
        
$i=0;
if($dealer->num_rows()>0){
    foreach($dealer->result() as $row){
        $dealer->result_object[$i]->contracts = $this->get_contracts($row->DealershipID);
        $i++;
    }
}

Which can then be accessed by doing:

Code:

foreach($dealer as $row){
        //something with the dealer
        foreach($row->contract as $contract){
              //something with the dealer's contract
        }
}

Hope some of that helps.

[eluser]Christian Haller[/eluser]
Hi,
this subselect is working (MySQL 5, CI 1.6.3)

Code:

//get Blogentry by Primary Key with prev and next Entry_IDs, ordered chronological
function get_entry($id){
        
        $this->db->from('entry');
        
        $this->db->select('entry.*, (SELECT p.entry_id FROM entry p WHERE p.created < entry.created ORDER BY p.created DESC LIMIT 1) AS prev,(SELECT n.entry_id FROM entry n WHERE n.created > entry.created ORDER BY n.created ASC LIMIT 1) AS next');
        $this->db->where('entry_id', $id);
        
           return $this->db->get()->row();
    }

Perhaps it's helping.

[eluser]pacifika[/eluser]
Old topic, however I keep finding this in the google search results.
Have a look at the following:

SubSelect in CodeIgniter

Code:

// Generate the subquery
$this->db->select('count(*)');
$this->db->from('users');

// Render the subquery to a string
$subQuery = $this->db->_compile_select();

// Reset active record
$this->db->_reset_select();

// Generate the primary query and include the subquery
$this->db->select('users.id as userId, users.fullname as userName');
$this->db->select("($subQuery) as userCount");
$this->db->where('users.status', 'active');