Where on multiple join

web-johnny, on 31 Mar 2012 - 04:11, said:

Hello R2D2 and welcome to the forum,

Actually perhaps it seems obvious to grocery CRUD to have joins and customs queries to the table, but it still NOT an available feature at this moment. That's why you didn't find it at google.

The only thing you can do for now and not change the core of grocery CRUD is to use the set_model function ( http://www.grocerycr...tions/set_model ) . So in your case you just have to create a model that it will look something like this (the name Users_join is just an example):

<?php
class Users_join extends grocery_CRUD_Model
{
//The function get_list is just a copy-paste from grocery_CRUD_Model
    function get_list()
    {
     if($this->table_name === null)
     return false;

     $select = "{$this->table_name}.*";

// ADD YOUR SELECT FROM JOIN HERE, for example: <------------------------------------------------------
// $select .= ", user_log.created_date, user_log.update_date";

     if(!empty($this->relation))
     foreach($this->relation as $relation)
     {
     list($field_name , $related_table , $related_field_title) = $relation;
     $unique_join_name = $this->_unique_join_name($field_name);
     $unique_field_name = $this->_unique_field_name($field_name);

    if(strstr($related_field_title,'{'))
        $select .= ", CONCAT('".str_replace(array('{','}'),array("',COALESCE({$unique_join_name}.",", ''),'"),str_replace("'","\'",$related_field_title))."') as $unique_field_name";
     else
        $select .= ", $unique_join_name.$related_field_title as $unique_field_name";

     if($this->field_exists($related_field_title))
        $select .= ", {$this->table_name}.$related_field_title as '{$this->table_name}.$related_field_title'";
     }

     $this->db->select($select, false);

// ADD YOUR JOIN HERE for example: <------------------------------------------------------
// $this->db->join('user_log','user_log.user_id = users.id');

     $results = $this->db->get($this->table_name)->result();

     return $results;
    }
}You can download the file from users_join.php

So in any case you have to create a separate file for each let's say different case.

Important note: Before using the set_model method make sure that you totally understand how the set_relation and set_relation_n_n works. I am telling this for the reason that the set_model is suggested to use it from more let's say familiar users with grocery CRUD

Hi, this post has been working just fine for me. Thank you very much.

Now I have another problem:

I've got a variable from my controller and I want to add it to a where clause in my view.

In more exact words:

I've got a function "employees($id_enterprise)" and I want to set the crud based on that id.

I've put a where clause in the model you posted like this

$this -> db -> where('enterprise.id_enterprise = 1');

And it worked fine, but I need that value as a variable.

Thank you for your support.

chest94