Active Record - many-to-many relation with fields in the join table? (no ORM, just CI)

Active Record - many-to-many relation with fields in the join table? (no ORM, just CI) - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Active Record - many-to-many relation with fields in the join table? (no ORM, just CI) (/showthread.php?tid=48196)

Active Record - many-to-many relation with fields in the join table? (no ORM, just CI) - El Forum - 01-07-2012

[eluser]Unknown[/eluser]
I have 2 tables and 1 join table.
And the problem is that I have some extra fields in the join table, and i can't figure out how to get them xD

Here are the tables:

Code:
gj_sponsors:

  id_sponsor

  name

  description

  img_link

  url

gj_events:

  id_event

  name

  place

  description

gj_join_events_sponsors:

  id_sponsors

  id_event

  value

  maxvalue

  qr_string

I need to get a list of sponsors in a single event, and get the value, maxvalue and qr_string from the join table too. I don't need any info from the event table (not yet anyways xD)

Here is "where I am" now xD

Code:
$this->db->select('id_sponsor, name, description, img_link, url, id_event, value, maxvalue, qr_string');

$this->db->from('gj_join_events_sponsors');

$this->db->join('gj_sponsors', 'gj_sponsors.id_sponsor = gj_join_events_sponsors.id_sponsor');

$this->db->where(array('gj_join_events_sponsors.id_event' => $event_id));

$query = $this->db->get();

$row = $query->result();

vd::dumpd($row);

I get this error:

Quote:A Database Error Occurred

Error Number: 1052

Column 'id_sponsor' in field list is ambiguous

SELECT `id_sponsor`, `name`, `description`, `img_link`, `url`, `id_event`, `value`, `maxvalue`, `qr_string` FROM (`gj_sponsors`) JOIN `gj_join_events_sponsors` ON `gj_join_events_sponsors`.`id_sponsor` = `gj_sponsors`.`id_sponsor` WHERE `gj_join_events_sponsors`.`id_event` = '8'

Filename: C:\........\system\database\DB_driver.php

Line Number: 330

Really hope someone can help xD
And please no "just use an ORM" or similar Wink

This project I'm making without, I'm looking into it for my next project ^^