Question about ActiveRecord query syntax [repost - simplified]

[eluser]stormbytes[/eluser]
QUESTION: What is it about my 'where' statement that's borking everything?

I've got two class methods:

Code:

// Takes given $asset_id & checks if a row exists in the database with that asset_id value

public function get_asset_by_id($asset_id = NULL)
{
    if( ! $asset_id)
    {
        return FALSE;
    }

    $args['where']    = array('a.id' => $asset_id);
    $args['limit']    = 1;

    $rows = $this->_read($args);
    
    if($rows->num_rows() == 1)
    {
        foreach($rows->result() as $row)
        {
            return $row;
        }
    }
}


// Reads database & returns rows

private function _read($args = array())
{
    $query =    "a.id,
                a.user_id,
                a.title,
                a.is_public,
                a.is_enabled,
                c.category,
                u.user_handle";

    $this->db->select($query);

    if(array_key_exists('where', $args))
    {
        $this->db->where($args['where']);
    }

    if(array_key_exists('order_by', $args))
    {
        $this->db->order_by($args['order_by']);
    }
    else
    {
        $this->db->order_by('date_created DESC');    
    }
    
    if(array_key_exists('limit', $args))
    {
        $limit = $args['limit'];
    }
    else
    {
        $limit = NULL;
    }
    
    if(array_key_exists('offset', $args))
    {
        $offset = $args['offset'];
    }
    else
    {
        $offset = NULL;
    }
    
    $this->db->join('meta_categories c', 'a.category_id = c.id', 'inner');
    $this->db->join('users u', 'a.user_id = u.id', 'inner');
        
    if(($rows = $this->db->get('user_assets a', $limit, $offset))) // CIUG has order of 2nd, 3rd args backwards!
    {
        return $rows;
    }
}

For some reason, get_asset_by_id returns num_rows = 0.

In fact, there *is* a row with the requested asset_id, and it returns this when written this way:

Code:

public function get_asset_by_id($asset_id = NULL)
{
    if( ! $asset_id)
    {
        return FALSE;
    }

    $args['where']    = array('a.id' => $asset_id);
    $args['limit']    = 1;

        // Direct DB call
      $rows = $this->db->get_where('user_assets', array('id' => $asset_id));
    
    if($rows->num_rows() == 1)
    {
        foreach($rows->result() as $row)
        {
            return $row;
        }
    }
}

[eluser]WanWizard[/eluser]
Have you checked the resulting SQL of your method ( echo $this->db->last_query() ). If you paste it into PHPMyAdmin, or the mysql commandline tools, what does it produce there?

Apart from the fact there are quite a few remarks to give about your code (which I don't think are relevant to the question), I don't see anything obviously wrong. Are you sure the joins are ok? And that the data exists in those tables to (if no match is found the result will not be included)?

[eluser]stormbytes[/eluser]
WanWizard -

1. I'm really happy to hear whatever remarks you have about how I could improve my code, so please share.

2. I just ran the query through phpMyAdmin:

Code:

SELECT
    `a`.`id`,
    `a`.`user_id`,
    `a`.`title`,
    `a`.`date_created`,
    `a`.`date_revised`,
    `a`.`category_id`,
    `a`.`is_public`,
    `a`.`is_enabled`,
    `c`.`category`,
    `u`.`user_handle`

FROM (`user_assets` a)

INNER JOIN `meta_categories` c ON `a`.`category_id` = `c`.`id`
INNER JOIN `users` u ON `a`.`user_id` = `u`.`id`
WHERE `a`.`id` = '1008166'
ORDER BY `date_created` DESC
LIMIT 1

It returns an empty result set. I don't understand why that's happening since there *is* a record in the 'user_assets' table with the specified value for 'id'.

I didn't think to check it this way since I'm using that function all over the place, even WITH the 'where' clause (albeit on another field and without the table alias) without a problem.

[eluser]InsiteFX[/eluser]
If you read the CodeIgniter User Guide on Active Record you would see
that it expects an Associative array method.

Code:

$array = array('name' => $name, 'title' => $title, 'status' => $status);

$this->db->where($array);

// Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active'

InsiteFX