2 Table Join, Ambiguous Columns, Aliasing, Active Record

[eluser]syncronos[/eluser]
THE PROBLEM:

The 2 tables I want to join have ambiguous columns (id and name are columns in both the events table and venues table *see below). This is a problem because i need to output both the event name and venue name, but when I JOIN the table, one is over-riding the other. Additionally, when looking in the manual I didn't see an alias function in the database class.

OPTIONS:

Option 1.) Don't use Active Record and alias the columns in the SQL query. I don't prefer this option though; I want AR's escaping, cleanliness, et al--I didn't see how to create aliases with the database class, as I mentioned. I was looking for something like $this->db->alias('table.column' => 'alias'), but to no avail. Does this function exist?

Option 2.) Prefix my database columns in the mysql database: event_name, venue_name, etc so that there are not any duplicates--seems like a hack though.

Ideas? Suggestions? A Solution?

My code is pasted below--thanks in advance,
~Syn

TABLE EXAMPLES:

Code:

events (id, name, venue_id, day, description, price)
venues (id, name, day, street, city, state, zip)

*Notice that both the events table and venues table have duplicate columns: id & name.

MODEL CODE:

Code:

function display_event()
{
    $this->db->select('*');
    $this->db->from('events');
    $this->db->join('venues', 'venues.id = events.venue_id');
            
    $query = $this->db->get();
    return $query;
}

PRINT RECURSIVE

Code:

print_r($query->result());

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [venue_id] => 1
            [name] => Venue Name
            [day] => 2009-01-30 22:00:00
            [description] => Lorem ipsum dolor sit amet
            [price] => 11.11
            [street] => 123 Magnolia Ave
            [city] => Orlando
            [state] => Florida
            [zip] => 32801
        )

)

*Notice that there is only 1 id and name column for venue, whereas I also need an id and name column for event.

[eluser]Armchair Samurai[/eluser]
[quote author="syncronos" date="1231413857"]THE PROBLEM:
Don't use Active Record and alias the columns in the SQL query.[/quote]
Just so you know, AR deals with aliases just fine, so could do something like:

Code:

$this->db->select('e.*, v.id AS id_venue, v.name as name_venue, v.day AS day_venue, v.street, v.city, v.state, v.zip');
$this->db->join('venues v', 'v.id = e.venue_id');
$query = $this->db->get('events e');

[eluser]syncronos[/eluser]
Great, thanks. Will give it a try in the morning.

Thanks again,
~Syn

[eluser]syncronos[/eluser]
Just to update you on this...

Aliasing the column fields worked just fine (which is what I needed). The attempted aliasing of the tables themselves caused an error.

Here is a truncated version of my code that worked:

Code:

function index_events()
    {
        $this->db->select('events.id AS events_id, events.name AS events_name,
                          venues.id AS venues_id, venues.name AS venues_name');
        $this->db->from('events');
        $this->db->join('venues', 'venues.id = events.venue_id');
        
        $query = $this->db->get();
        return $query;
    }

On a side note, instead of [i]SELECT * events[/i] and simply aliasing only the venues table, I opted to alias all of the fields. I did this to keep a specific naming convention throughout.

...Than again, If I'm aliasing everything, why not simply rename my columns in my database schema.

Thanks,
~Syn

[eluser]darkhouse[/eluser]
Don't rename your columns, this is the best way (imo) to do it. I'm sure you'll have other instances where you aren't joining tables, so why bother renaming the columns to only satisfy joins. Just alias the columns in the SELECT and that's it. I generally alias my tables in joins as well. Here's how I would've written it.

Code:

function index_events(){
   $this->db->select('e.id event_id, e.name event_name, v.id venue_id, v.name venue_name');
   $this->db->join('venues v', 'v.id = e.venue_id');
   $query = $this->db->get('events e');
   return $query;
}

Don't know if that helps your or not, but good luck either way.

[eluser]Phil Sturgeon[/eluser]
If I remember rightly, get() does not support aliasing, but from() and join() do.

[eluser]Armchair Samurai[/eluser]
[quote author="pyromaniac" date="1231519025"]If I remember rightly, get() does not support aliasing, but from() and join() do.[/quote]
get() does support aliasing, as do join() and from().

There was a bug at one point where including AS in the statement would cause Active Record to break when you used query caching, but I believe that has been fixed in 1.7.

[eluser]helmutbjorg[/eluser]
I've got into the habit of the following naming convention for my tables.

Instead of:

Code:

events (id, name, venue_id, day, description, price)
venues (id, name, day, street, city, state, zip)

I do:

Code:

tbl_events (event_id, event_name, venue_id, event_day, event_description, event_price)
tbl_venues (venue_id, venue_name, venue_day, venue_street, venue_city, venue_state, venue_zip)

It is bloated but you never have any conflicts or confusion about where your data is coming from.