[smart-aleck]

Hey Guys!

 

I have to say that GroceryCRUD is an amazing and extremely powerful tool, I'm so glad it's available for us!

 

I discovered CodeIgniter when designing a client's order tracking system last year and it has been continuing to evolve as I discover great software like this.  My application has a login system powered by CIzACL and I've given each 'role' it's own controller which have similar functions.  There are two functions, 'deals' and 'orders', which present data specifc to the logged in user, and this is where I realized had to ask question.

 

Here are two of my tables:

 

deals

-deal_id

-name

-start

-end

-url

 

orders

-order_id

-deal_id --> deals table (deal_id)

-mfg_id --> user table (user_id)

-courier_id --> user table (user_id)

-acpt_date

-ship_date

 

Say I log in as a specific user with a 'courier' role type, I am brought to the 'courier/deals' function where groceryCRUD presents a flexigrid containing a list of deals and an action to examine the orders for each deal.  The 'examine orders' action points to the 'courier/orders/'.$id function which will show all orders for that 'deal_id' AND where 'courier_id' = 'user_id' (taken from session data.)  This seems to work well but I noticed a behavior which I did not initially plan on and am unsure how to rectify (if even possible.)

 

What I am noticing is that some deals may not have any orders for a particular courier, yet the deal will still be included in the listing when they login.  This is obviously normal behavior but is redundant for the courier to see this row if they have no orders for that deal.  I can write a function to check how many orders they are assigned to for each deal but have no idea how I could use it to tell groceryCRUD not to display these rows and there isn't a GC 'query' function for me to write a query using an IF statement or something.

 

Can anyone give me some suggestions on how to accomplish this?

 

Thanks much!

[mavershim]

hi,

 

can you show your controller.. have you use the where (condition)

[smart-aleck]

Here is the 'courier' controller 'deals' function:

 

function deals() {
    $crud = new grocery_CRUD();
    
    //Set Column Names
    $crud = $this->cb_model->_set_col_names($crud);
    
    //Set Add, Edit, Delete Actions
    $crud = $this->cb_model->_unset_aed($crud, AED);
    
    $crud->set_theme('flexigrid');
    $crud->set_subject('Deal');
    $crud->set_table('deals');
    $crud->columns('deal_id', 'name', 'start', 'end', 'url');
    $crud->callback_column('url', array($this,'_callback_webpage_url'));
    $crud->add_action('View Orders', '', 'courier/orders', 'truck-icon');
    $output = $crud->render();
    $this->_out($output);
}
 

 

 

 

Here is the 'courier' controller 'orders' function:

 

function orders($id) {
    $crud = new grocery_CRUD();
    
    //Set Column Names
    $crud = $this->cb_model->_set_col_names($crud);        
    
    //Set Add, Edit, Delete Actions
    $crud = $this->cb_model->_unset_aed($crud, AED);
    
    $crud->set_theme('flexigrid');
    $crud->set_subject('Orders');        
    $crud->set_table('orders');
    $crud->where('deal_id', $id);
    $crud->where('courier_id', $this->session->userdata('user_id'));
    $crud->columns('order_id', 'mfg_id', 'acpt_date', 'ship_date');
    $crud->set_relation('mfg_id', 'user_profiles', 'user_profile_name');        
    $output = $crud->render();
    $this->_out($output);    
}

[smart-aleck]

Is this a case where I may need to change the structure of my database or setup some sort of relationship?

[smart-aleck]

OK, I did some looking into what I want to do .. how am I able to generate a crud of the following query?

 

SELECT *
FROM deals a, orders b
WHERE a.deal_id=b.deal_id
AND b.mfg_id= $this->session->userdata('user_id')
 

[davidoster]

OK, I did some looking into what I want to do .. how am I able to generate a crud of the following query?

 

SELECT *
FROM deals a, orders b
WHERE a.deal_id=b.deal_id
AND b.mfg_id= $this->session->userdata('user_id')
 

http://www.grocerycrud.com/documentation/options_functions/set_model

[smart-aleck]

Great, thanks!

 

I was looking around concerning the 'set_model' function brought me to this thread: /topic/264-join-tables/

 

I followed the instructions and extended grocery_CRUD_Model with my own Jobs_model and started testing with the 'get_list()' function to see what code I needed to add.

 

Here is what the function looks like:

 

    function get_list()
    {
        if($this->table_name === null) return false;
        
        $select = "{$this->table_name}.*";
        $select .= ", orders.*";
        
        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);
            $this->db->join('orders', 'orders.deal_id=deals.deal_id');
            $this->db->where('orders.mfg_id', $this->session->userdata('user_id'));
            $this->db->group_by('deals.deal_id');
            
            $results = $this->db->get($this->table_name)->result();
      
            return $results;
    }
 

 

I added the orders table to the select statement and then used the join, where and group_by database functions to join the orderrs table where the deal_id was equal, the orders.mfg_id equals the current user id and the results are grouped by deal_id.  I realized that I needed to use the group_by function after my first result set contained 200 or so rows when I was only expecting 10 out of a maximum 13.  Once the group_by function was applied I started to obtain the results I was looking for, there were the 10 expected deals showing but the pagination results still said "Displaying 1 to 13 of 13 items."

 

I ended up echoing the query string to get a look at it and noticed that my other relation was using a 'LEFT JOIN' and read--

 

Each item in the left table will show up in a MySQL result, even if there isn't a match with the other table that it is being joined to.

 

I went ahead and copied the join_relation() function into my Jobs_model and updated the code to remove the 'left' part of the JOIN statement.  This unfortunately did not seem to make a difference and it still said that there were 13 records though only displaying the 10 I expected.  When I attempt to sort the fields the grid displays for a second and then gives me an error, I have to go delete all my cookies in order for it to render properly again. Finally, search does not seem to be working when using my custom model and I suspect if I was using the add/edit/delete actions there may be issues with those as well.

 

Am I missing something?

[davidoster]

I wouldn't know where the problem is. So instead of all these changes maybe via your own model you do something like this,

 

http://ellislab.com/codeigniter/user-guide/database/queries.html

[smart-aleck]

Here is the get_list() function after re-writing using only a query string:

 

    function get_list() {
        
        $q    = "SELECT * "
            . "FROM deals d"
            . "JOIN user_profiles as up "
            . "ON up.user_profile_user_id=deals.dealsite_id "
            . "JOIN orders "
            . "ON orders.deal_id=deals.deal_id "
            . "WHERE courier=1 "
            . "AND orders.mfg_id=".$this->session->userdata('user_id')." "
            . "GROUP BY deals.deal_id";
        
        $results = $this->db->query($q)->result();
                
        return $results;
    }
 

 

With this code I am now getting the error:  Fatal error: Call to a member function result() on a non-object in /home/quiltsdi/public_html/trace/application/models/jobs_model.php on line 16

 

Line 16:   $results = $this->db->query($q)->result();

 

I've tried changing this up and removing ->result() and then I get the following two errors:

 

A PHP Error was encountered

Severity: Warning

Message: Invalid argument supplied for foreach()

Filename: libraries/grocery_crud.php

Line Number: 1634

 

 

Fatal error: Call to a member function num_rows() on a non-object in /home/quiltsdi/public_html/trace/application/models/grocery_crud_model.php on line 186

 

 

It appears that the more I deviate from using the grocery_crud code the less functionality is available.  I highly doubt the add/edit/sort/search functions are going to work even after figuring out how to get this results query to return correctly.  Are there any examples on this forum of a model change which works?

[smart-aleck]

Investigating a bit more, I went back to my first get_list() function and realized that I didn't need to modify the select statement so I took that line out.  I gave it a test and the sort/search features seem to be working fine now.  I haven't checked the add/edit yet but I suspect it will be OK if I need to turn them on.  What still is bugging me is the fact that it says there are 13 records when only 10 are displayed.  I suspect that number is updated via it's own query and I need to find and extend that function so that it contains my additional join.

 

Researching . . .

[smart-aleck]

PERFECT

 

I found the query in the get_total_results() function so I extended it and added my additional join, where, group_by statements and it's finally spitting out the correct number!

 

Thanks for the help.