Joining tables with different field names using Grocery CRUD

Question

I have the below tables

CREATE TABLE IF NOT EXISTS `maintrequests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `propID` int(11) DEFAULT NULL,
  `landlordID` int(11) NOT NULL,
  `subject` varchar(45) DEFAULT NULL,
  `message` varchar(200) DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(45) DEFAULT NULL,
  `firstname` varchar(45) DEFAULT NULL,
  `lastname` varchar(45) DEFAULT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
)

I am very new to Grocery CRUD and having issues joining these two tables. The join would be maintrequests.landlordID = users.id. All of the examples on the Grocery CRUD site join tables where the relevant fields have identical names

I did find this answer but he even says "this is rediculous"

My controller code

public function test(){
    $crud = new grocery_CRUD();
    $crud->set_subject('Maintenance Requests')->set_table('maintrequests')->columns('subject','created','status');
    $this->data['maintlist'] = $crud->render();


    $this->data['title'] = 'Maintenance Test';
    $this->load->view('global/_layout_main_test',$this->data);
}

My function of my model that I'm trying to convert to Grocery

public function get_all_for_landlord_table($landlordid){
    return $this->db->select('maintrequests.subject,maintrequests.created,maintrequests.status,maintrequests.message,properties.address,units.unitnum')->order_by('maintrequests.created','asc')->from('maintrequests')->join('properties','maintrequests.propid = properties.id')->join('units','maintrequests.unitid = units.id')->where(array('maintrequests.landlordID'=>$landlordid))->get()->result();
}

I haven't gotten to the point of testing yet because I'm not sure the syntax to use. Their site says to use this syntax `$crud->set_relation('user_id','users','username');`. However this means that `user_id` is the field name in both tables. In my case, that isn't true — mhopkins321, Jan 26 '14 at 07:16

score 1 · Accepted Answer · answered Jan 26 '14 at 07:30

1

Try this code. I didn't test.

public function get_all_for_landlord_table($landlordid){

    $crud = new grocery_CRUD();
    $crud->set_table('maintrequests');
    $crud->set_relation('maintrequests.propid','properties','properties.id');
    $crud->set_relation('maintrequests.unitid','units','units.id');
    $crud->where('maintrequests.landlordID',$landlordid);
    $output = $crud->render(); 

}

answered Jan 26 '14 at 07:30

Kumar V

8,810
9
39
58

So, you can't join with different field names without creating a model ? – marifrahman Jan 06 '16 at 03:49

score 0 · Answer 2 · edited May 07 '16 at 16:17

0

I had the same problem. Just use this:

$crud->set_table('maintrequests');
$crud->set_relation('**landlordID**','**users**','**firstname**');

If the **users** table doesn't have the **landlordID** key, Grocery gets the PRIMARY KEY of the **users** table.

edited May 07 '16 at 16:17

mastov

2,942
1
16
33

answered May 07 '16 at 16:07

user3385267

1

Joining tables with different field names using Grocery CRUD

2 Answers2