[SOLVED] How to use MySQL alias join with CI's Active Record

[eluser]tkaw220[/eluser]
Hi,

I have recently working with CI's Active Record class. Consider I have below code, where the code retrieve data from two table by using join. How can I improve below code by using MySQL alias? i.e.,

Code:

select inventory_logs as l

?

Code:

$this->db->select('inventory_logs.log_no, inventory_logs.transaction, inventory_logs.type, inventory_logs.purchase_order, inventory_logs.job_sheet, inventory_logs.invoice, inventory_logs.delivery_order, inventory_logs.note, inventory_logs.user, inventory_logs.log_date, booksellers.name')
->join('booksellers', 'inventory_logs.bookseller_id = booksellers.id', 'left')
->where('inventory_logs.log_no', $log_no);

$data['log'] = $this->db->get('inventory_logs', 1)->row();

Many thanks.

7/14/2011: Found this solution from Grey Aker's blog. Thanks Grey.

Code:

$qry = $this-db->select('m.member_id, m.group_id, m.username, m.screen_name, m.email, m.url, m.location, g.group_title')
->from('members m')
->join('member_groups g', 'g.group_id = m.group_id')
->where('m.member_id', $id)
->get();

Hope someone find this helpful.

[eluser]danmontgomery[/eluser]

Code:

$this->db->select('inventory_logs.log_no as l, ...

[eluser]tkaw220[/eluser]
Hi noctrum,

Is my code below correct?

Code:

$this->db->select('inventory_logs.log_no as l, l.transaction, l.bookseller_id, l.type, l.purchase_order, l.job_sheet, l.invoice, l.delivery_order, l.note, l.user, l.log_date, booksellers.name as b, b.id')
->join('booksellers', 'l.bookseller_id = b.id', 'left')
->where('inventory_logs.log_no', $log_no);

Thank you.

[eluser]jmadsen[/eluser]
no, you need to alias your table, not your field, for what you are doing.

Take it out of Active Record for a moment and look at what you are writing

[eluser]danmontgomery[/eluser]
To alias the table instead of the column:

Code:

$this->db->get('inventory_logs as l', 1);