Active Record doesn't handle 'NULL' columns? [SOLVED]

[eluser]Jaketoolson[/eluser]
I'm running a UNION on 2 SELECT queries. One of the select creates a fictitious column, setting the value to NULL (i.e. NULL AS sub_cat). This is done because when the 2 queries are combined, the other select query has values for this column. When running this across multiple platforms using various methods (MS Access, MySQL Query Browser, phpMyAdmin, I have no problems except when I use the active record method in CI.

Code:

$sql = "sub.model_id, sub.sub_cat
FROM (

SELECT models.model_id, models_sub_categories.category_name AS sub_cat
FROM models
INNER JOIN models_sub_categories ON models.sub_cat_id = models_sub_categories.sub_cat_id
WHERE models.is_active =1

UNION

SELECT models.model_id, NULL AS sub_cat
FROM models
INNER JOIN models_categories ON models.parent_cat_id = models_categories.parent_id
WHERE models.is_active =1
) AS sub   ";    
        
$this->sess_db->select($sql);
$query = $this->sess_db->get();
foreach ($query->result() as $row)
{    
    print_r($row);
}

I receive the following error:

A Database Error Occurred

Error Number: 1054

Unknown column 'null' in 'field list'

When I switch CI query methods:

Code:

//from this
$this->sess_db->select($sql);
$query = $this->sess_db->get();

//to this
$this->sess_db->query($sql);

The correct results are produced:

Code:

model_id    sub_cat
4             Outages
3             Electric
5             Gas
6             Gas
1             NULL
2             NULL

[eluser]Phil_B[/eluser]
Hi Jake,

You have all your SQL in the select portion of the ActiveRecord query. This may or may not work but try this instead:

Code:

$from = '(
    SELECT models.model_id, models_sub_categories.category_name AS sub_cat
    FROM models
    INNER JOIN models_sub_categories ON models.sub_cat_id = models_sub_categories.sub_cat_id
    WHERE models.is_active =1

    UNION

    SELECT models.model_id, NULL AS sub_cat
    FROM models
    INNER JOIN models_categories ON models.parent_cat_id = models_categories.parent_id
    WHERE models.is_active =1
) AS sub';

$query = $this->sess_db
    ->select('sub.model_id, sub.sub_cat')
    ->from($from)
    ->get();

[eluser]jmadsen[/eluser]
why not just skip ActiveRecord, since you aren't actually using it?

Code:

$query = $this->sess_db->query($sql);
foreach ($query->result() as $row)
{    
    print_r($row);
}

Active Record is just a query building library. You don't have to use it.

http://ellislab.com/codeigniter/user-gui...mples.html

[eluser]Jaketoolson[/eluser]

why not just skip ActiveRecord, since you aren’t actually using it?

I was just being consistent with the other functions in the class I'm writing and put no thought into whether or not I need to use ActiveRecord. Also, for debugging purposes, I begin with a "full query" that I know works and chain it down.

Breaking my query apart into a very simple SELECT using ActiveRecord, while still forcing a NULL, still produces the same error:

Code:

$this->sess_db->select("models.model_id, NULL AS sub_cat");
$this->sess_db->from('models');
$this->sess_db->join("models_categories","models_categories.parent_id = models.parent_cat_id", "inner");
$this->sess_db->where("models.is_actve", 1);
$query = $this->sess_db->get();

A Database Error Occurred

Error Number: 1054

Unknown column 'NULL' in 'field list'

I believe this to be a bug.

[eluser]jmadsen[/eluser]
If you print out your query, you will see that NULL is being "pipped" as `NULL`

Add "false" as a second argument to your ->select() and you will be fine.

i.e.,

Code:

$this->sess_db->select("models.model_id, NULL AS sub_cat", false);

[eluser]Jaketoolson[/eluser]
Thanks! Setting the 2nd parameter to false caused CI to not protect my fields with `backticks` which resolved the issue.