[geekygirl]

I have two tables....

 

Categories and Images...

On category table I'd like to show the number of images per category.

Here's the SQL:

SELECT c.id, c.name, (SELECT  COUNT(*)  FROM images i WHERE i.category = c.id ) as count FROM categories c

Is this possible with grocerycrud or I have to add count field in category field and have to maintain it's value after each delete,add on images table?

 

[Paul Savostin]

"add count field in category field and have to maintain it's value after each delete,add on images table"   -  never do this! NEVER! this is absolutely wrong idea.


So what u need in GC? Where u need to show images per category? in category GC view list ?

You have so many options:

1) Add custom column, lets say "qty" and make callback_column function for this new column where u make SQL query for certain category.

2) Make custom model and extend query for display category, just add "select fields" in your case smth like (SELECT  COUNT(*)  FROM images i WHERE i.category = c.id ) as count

 

3) Or u can even just extend query in your controller, for example

 

if(in_array($this->state, array('list','success')))
{
            $this->db->select('count(i.primary_key_of_your_image_table) FROM images i where i.category_id = categories.id) As img_qty',false);                     
}

Then in callback for custom column u no need do SQL - just return $row->img_qty

OR make callback_column_category_name and there return somt like this - return $row->category_name . '(' . $row->img_qty . ')';  it would be like animals (234), sport (122)...


P.S. $this->state this is pure $crud->getState

P.S.S. - custom column I mean that u add it in GC using callback_column - not in the table!
 

[geekygirl]

I was on a vacation and I just came back... I am continuing my work on project again..

Thanks Paul for your answer.

 

Here's where I am now:

 

1. I have tried to do it this way but keeping that count up to date can be quite difficult... I have tried to do it and I have ran into some problems with deleting images and updating the count... Long story short, I'd like to avoid this solution...

 

2. I have checked custom_model docs http://www.grocerycrud.com/documentation/options_functions/set_model and I wasn't able to figure it out... Seems like a decent solution but I just can't figure it out... Some quality samples on the topic would be nice since this looks pretty useful and I'll probably need in the future...

 

3. This looks like something that will work... and that's not really hard to implement. My problem is how to pass that extended data results to the column_category..

 

Here's what I've got:

// Adds a custom column count
$crud->columns('name', 'count');

// Callback for the custom column _callback_get_counter
$crud->callback_column('count', array($this, '_callback_get_counter'));


/* Just before the view I have this */

$this->state = $crud->getState();
if(in_array($this->state, array('list','success')))
{
	$this->db->select('categories.id, (SELECT COUNT(*) FROM images i WHERE i.category = categories.id ) as img_qty', false);
	$q = $this->db->get('categories');
/* Do I need anything else in here ??? */
}


// Callback function
public function _callback_get_counter($value, $row) {
	return $row->img_qty';
}

But this simply doesn't work...

[Paul Savostin]

if(in_array($this->state, array('list','success')))
{
    $this->db->select('categories.id, (SELECT COUNT(*) FROM images i WHERE i.category = categories.id ) as img_qty', false);
    $q = $this->db->get('categories');
/* Do I need anything else in here ??? */
}

Here you just extend your GC query! You dont need to $this->db->get('categories');!

And in callback you just

public function _callback_get_counter($value, $row) {
    return $row->img_qty';
}

Doesnt this work?

 

[geekygirl]

doesn't work...

keep getting this error

Severity: Notice
Message: Undefined property: stdClass::$img_qty
Filename: controllers/categories.php
Line Number: 53

[geekygirl]

When I place var_dump($row) in callback I get this... http://prntscr.com/7zscif

 

so before the GroceryCRUD is shown I have that extra field but not within it...

[geekygirl]

it works fine when I remove this condition...

if(in_array($crud->getState(), array('list','success'))) {

[geekygirl]

I had to add this  'ajax_list' to the array and now it works just fine :)

if(in_array($crud->getState(), array('list','ajax_list','success'))) {

[Paul Savostin]

hi! yes, ajax_list had to be add cause in some cases in list view we have this state:)

Can u write your action when you get error and ajax_list comes to play?

I am glad that it works.

Edit: Yes, ajax_list state when you refresh table list with ajax list refresh button, so adding in_array 'ajax_list' is required!

[geekygirl]

I have noticed ajax_list while debugging something...

Anyway I'm glad this works...

 

Thanks a lot for your help Paul!

.

[Paul Savostin]

You're welcome!