CodeIgniter Forums

Full Version: How to get table.column in active record queries?
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]Jakobud[/eluser]
Lets say I have two tables: users and roles

Each table has a column called 'id'.

If I use active record to query the values of the joined tables:

Code:
$this->db->join('role', 'role.id = user.role_id');
$query = $this->db->get('user');

foreach ( $query->result() as $row )
    echo $row->id; // <--- this is the problem right here

The 'id' that the active record query prints out is the 'id' column from the role table. But what if I want to print out the id from the user table? Is there anyway to specify it implicitly like this?

Code:
echo $row->user.id

The only alternative I've found so far is to specify what I'm selecting first:

Code:
$this->db->select('user.id AS user_id, role.id AS role_id');

But I thought perhaps there was a different way to do it with active record... Any thoughts?

El Forum

[eluser]ToddyBoy[/eluser]
[quote author="Jakobud" date="1282713783"]Lets say I have two tables: users and roles

Each table has a column called 'id'.

If I use active record to query the values of the joined tables:

Code:
$this->db->join('role', 'role.id = user.role_id');
$query = $this->db->get('user');

foreach ( $query->result() as $row )
    echo $row->id; // <--- this is the problem right here

The 'id' that the active record query prints out is the 'id' column from the role table. But what if I want to print out the id from the user table? Is there anyway to specify it implicitly like this?

Code:
echo $row->user.id

The only alternative I've found so far is to specify what I'm selecting first:

Code:
$this->db->select('user.id AS user_id, role.id AS role_id');

But I thought perhaps there was a different way to do it with active record... Any thoughts?[/quote]

I think your problem is in the way you reference the result array.

Code:
echo $row->id;

Try something like this

Code:
echo $row['id'];

El Forum

[eluser]Vheissu[/eluser]
I think you are misunderstanding how Active Record queries work. Active Record is merely standard database queries but simplified without having to write tedious SQL clauses and statements. What you are asking to do with Active Record is probably not easily possible unless you're using aliases in your select statement through the use of AS. Even then that doesn't do what you want it to do.

What you are looking for is an ORM which will turn your database tables and fields into objects you can access inside of your controllers. Check out DataMapper Overzealous Edition: http://www.overzealous.com/dmz/index.html - probably the best ORM for Codeigniter.

El Forum

[eluser]kaejiavo[/eluser]
Quote:The only alternative I’ve found so far is to specify what I’m selecting first:

$this->db->select('user.id AS user_id, role.id AS role_id');
But I thought perhaps there was a different way to do it with active record… Any thoughts?


Jakobud,

what you descripe happens to all columns with the same name in your joined tables. The most right column will be the only in the result.
I ran into the same problem, and found no way to use aliases with AR but manually write the select statement as you have done as alternative.
Maybe an AR expert can give us some help here, or takes this issue (using column aliases for joined tables) to the AR feature request list.

Marco

@ToddyBoy
there is definitely no difference for this issue using the object or array result set.

@Veishu
this is a standard SQL requirement. Only because you want to join 2 tables, there is no obvious need for an ORM i would say. For using DMZ your tables and relations have to follow special conventions, which is o.k. but doesn't suit everyone.

El Forum

[eluser]Vheissu[/eluser]
[quote author="mawi27" date="1282736959"]
Quote:The only alternative I’ve found so far is to specify what I’m selecting first:

$this->db->select('user.id AS user_id, role.id AS role_id');
But I thought perhaps there was a different way to do it with active record… Any thoughts?


Jakobud,

what you descripe happens to all columns with the same name in your joined tables. The most right column will be the only in the result.
I ran into the same problem, and found no way to use aliases with AR but manually write the select statement as you have done as alternative.
Maybe an AR expert can give us some help here, or takes this issue (using column aliases for joined tables) to the AR feature request list.

Marco

@ToddyBoy
there is definitely no difference for this issue using the object or array result set.

@Veishu
this is a standard SQL requirement. Only because you want to join 2 tables, there is no obvious need for an ORM i would say. For using DMZ your tables and relations have to follow special conventions, which is o.k. but doesn't suit everyone.[/quote]

He was asking if you can access field names in tables specifically using Active Record much like an ORM would let you do, like this; $this->users->id which would be saying I want to access the ID field of the users table (like the example the question asker provided) and the answer is Active Record can't do that.

Sure an ORM might be something new to learn and cause you to rewrite your queries, but if you want to access tables and field names like objects, an ORM is the way to go and not Active Record.

The purpose of Active Record is to simplify queries you otherwise would be writing manually like tedious joins.

El Forum

[eluser]kaejiavo[/eluser]
Vheissu,

i don't disagree with you regarding ORM. But it is not the question here.

Please reread the original post:
Quote:If I use active record to query the values of the joined tables:

$this->db->join('role', 'role.id = user.role_id');
$query = $this->db->get('user');

foreach ( $query->result() as $row )
echo $row->id; // <--- this is the problem right here
The ‘id’ that the active record query prints out is the ‘id’ column from the role table. But what if I want to print out the id from the user table? Is there anyway to specify it implicitly like this?

echo $row->user.id
The only alternative I’ve found so far is to specify what I’m selecting first:

$this->db->select('user.id AS user_id, role.id AS role_id');
But I thought perhaps there was a different way to do it with active record… Any thoughts?

There is a field 'id' in the user table and another one in the role table. As the role table is the most right in his join, only the role.id is part of his $query->result().
He has found an alternative, namely to alias the role.id so that it does not conflict with the user.id
The context is the foreach( $query->result() as $row ) where he uses the AR Methods to build his array or whatever. I cannot see a question about $this->users->id there.

Anyway, of course you can have a variable $this->users in your controller and with AR set the value as
$this->users = $this->db->row()
and then use $this->users->columnname in your code. I am absolutely sure this is working as i am using it in many places without ORM.

@Jakobud
sorry, i didn't want to hijack your thread, i stop posting here, now.