Can I do this join with MY_Model?

[quadrant6]

I'm bit of a newcomer to Codeingiter. I have a simple database of venues and events and I use a join table (event_venues) to link them.

To see all venues that an Event will be held at, I can use the following plain SQL

SELECT venues.name, venues.id FROM venues, event_venues WHERE venues.id = event_venues.venue_id AND event_venues.event_id = 11

I've been looking at the docs and examples but can't see a way to do this with MY_Model. Just wanted to check before I write is pure SQL using $this->db->sql()

[andrewryno]

No need to use $this->db->sql(). You can do this with ActiveRecord still.

$this->db->select('venues.name, venues.id');
$this->db->from('venues');
$this->db->join('event_venues', 'event_venues.venue_id = venues.id');
$this->db->where('event_venues.event_id', 11);

[facultymatt]

Take a look at Relationships in the readme. You could set up your model so you could make the following call:

$event = $this->venue_model
    ->select('venues.name, venues.id')
    ->with('events')
    ->get_by('event_id', 11);

or:

$event = $this->event_model
    ->select('venues.name, venues.id')
    ->with('venues')
    ->get('11);

depends how your models are set up. As andrew noted you can also use activerecord.

[quadrant6]

Thanks andrewryno, well at least I can do that. Easy too!

facultymatt, I'm a bit slow on this stuff. I did look at the readme but still don't know what the 'right' way it to set it up in my case, as I'm using a join table 'event_venues' (multiple venues, with different order per event) and the example looks to be just doing a join straight from 'author' or 'comments' to 'post'.

To clarify, the query I should have written in full

$this->db->select('venues.name, venues.id, event_venues.order');
$this->db->from('venues');
$this->db->join('event_venues', 'event_venues.venue_id = venues.id');
$this->db->where('event_venues.event_id', $event_id);                   
$this->db->order_by("order", "asc");

[jamierumbelow]

The relationship system is great for prototyping, but if you don't mind having a bit messier code (with a performance boost!) you can use your query absolutely fine using the query builder.

To make it a bit tidier, I'd use some scopes:

class Venue_model extends MY_Model
{
    public function ordered_by_event_venue($event_id)
    {
        $this->db->select('venues.*, event_venues.order');
        $this->db->join('event_venues', 'event_venues.venue_id = venues.id');
        $this->db->where('event_venues.event_id', $event_id);
        $this->db->order_by("order", "asc");

        return $this;
    }
}

$venues = $this->venue->ordered_by_event_venue($event_id)
                      ->get_all();

[julienmru]

With commit 78c871d, you can rewrite the code to something more generic, such as:

$venues = $this->venue->join('event_venues', 'event_venues.venue_id = venues.id')
                      ->order_by('order', 'asc');
                      ->get_many_by('event_venues.event_id', $event_id);

[absmugz]

Hi, I have a simple database which uses joins as follows :

TableA aID nameA

TableB bID nameB

tableC aID bID

Please point me in the right direction of how to get data from TableA and TableB with multiple id's in tableC MY_Model. Do I have to write custom functions like :

class A_model extends MY_Model { public function Example() { Custom ActiveRecord query goes here } }

Can you please give me a rough example using the table structure above ? Thank you

[harshdusane]

hi, i'm using this model in my project working fine but stuck to join three tables.

Please help me to solve this issue.

[besabellacyrus]

here is sample with two joins normal ci active record

$res = $this->db->select('*') ->from('transaction_categories as tcat') ->join('breaks as b', 'tcat.id = b.trans_cat_id') ->join('funs_categories as fcat', 'tcat.funs_cat_id = fcat.id') ->where('b.trans_cat_id', $trans_cat_id) ->limit(1) ->get();

[bkader]

Sorry for this late suggestion 😄 .. I use an observer to join from other tables and I add it to the before_get property so whenever I call a getter I get the result with joint tables. In your case, in your model you may have:

public function __construct()
{
    array_unshift($this->before_get, 'join_venues');
    parent::__construct();
}

protected function join_venues()
{
    $this->db->join('event_venues', 'event_venues.venue_id = venues.id');
    return $this; // <- I guess this one is optional.
}