code igniter datamapper join get()

I am using this join in my code igniter model

 $this->db->select('e.name, p.projects');
      $this->db->from('example as e');
      $this->db->join('procure as p', e.id = p.id');
      $this->db->where('e.cityid', '1');
      $this->db->where('e.status', '0');

I do not have separate table for join. Here is my data mapper, this is not giving any output.

I have two tables and I want to write a join query on them.I have this in my controller.

   $example = new Example();
      $example ->where_join_field('procure', FALSE);

Update

can you show me the snippet for joining three tables using data mapper.

Generally you don't do joins by hand with DMZ models (the sql generated will use joins nonetheless). The thing you are looking for is Relations.

You have to set up your model's relations, using the $has_one and $has_many attributes, keeping the naming conventions, creating the necessary tables for many-to-many and so on. You can read about these in the docs here and here.

Once you got your models set up, you can use the where_related and include_related methods where you have used joins before:

1. where_related is for when you want to filter the models you are querying on some related model's field values. So if you have a related project set up on your Example class, you can write ->where_related('project', 'procure', false); and it will filter the returned Example instances based on their related project's procedure field. So basically it's the same conditionals that you would put into the where SQL clause.

2. include_related is for when you want to include fields from related models or even whole instance. So if you write ->include_related('project', 'projects') when you query Example instances you will end up with a project_projects attribute on the returned instances. There are many options to control how these attributes should be created. Basically these are the fields you would have put into the select SQL clause.

There are magic methods created for every named relation and many other options, i refer you to the Get (Advanced) page of the documentation for start and free to explore the other pages describing relations.