[Richard van den Winkel]

Hi!

I have 3 tables:
[b]user[/b]
- id
- name
[b]product[/b]
- id
- name
[b]userLikesProduct[/b]
- user_id
- product_id

Now i want to show the number of likes in the datagrid.
Via normal SQL i would do something like this:
[font=courier new,courier,monospace]SELECT u.id, u.name, count(*) as numLikes FROM user AS u, userLikesProduct AS l WHERE u.id = l.user_id GROUP BY u.id[/font]

How would i achieve this with Grocery Grud?

I tried like this, but then sorting the column in the flexigrid doesn't work:
[font='courier new', courier, monospace][size=4]$this->grocery_crud->set_relation_n_n('userLikesProduct', 'productlikes', 'product', 'user_id', 'product_id', 'id');[/size][/font]

[font=courier new,courier,monospace]$this->grocery_crud->callback_column('userLikesProduct', function($value, $row) {[/font]
[font=courier new,courier,monospace] return [/font][font='courier new', courier, monospace][size=4]count([/size][/font][font='courier new', courier, monospace][size=4]explode(',', $value));[/size][/font]
[font='courier new', courier, monospace][size=4]});[/size][/font]


Hope someone can help me out.

Best,
Richard

[victor]

As a simply solution you can create an extra field (or extra table) "count".

tbl "user"
id| user_name| count_products

or
tbl "count"
user_id | count

[Richard van den Winkel]

@Victor: Thanks for your reply!

Didn't want the extra column, but seems indeed it is the most easy way for now.

 

Best

[davidoster]

Richard even your supplied SQL has an extra fake column (count(*) as numLikes).

What [member=victor] suggested is the equivalent solution to grocery crud library.

 

Hi!

I have 3 tables:
user
- id
- name
product
- id
- name
userLikesProduct
- user_id
- product_id

Now i want to show the number of likes in the datagrid.
Via normal SQL i would do something like this:
SELECT u.id, u.name, count(*) as numLikes FROM user AS u, userLikesProduct AS l WHERE u.id = l.user_id GROUP BY u.id

How would i achieve this with Grocery Grud?

I tried like this, but then sorting the column in the flexigrid doesn't work:
$this->grocery_crud->set_relation_n_n('userLikesProduct', 'productlikes', 'product', 'user_id', 'product_id', 'id');

$this->grocery_crud->callback_column('userLikesProduct', function($value, $row) {
return count(explode(',', $value));
});


Hope someone can help me out.

Best,
Richard

[Richard van den Winkel]

Richard even your supplied SQL has an extra fake column (count(*) as numLikes).

What [member=victor] suggested is the equivalent solution to grocery crud library.

 

True.

Only now i need an extra column / table in my database which i need to maintain.

However, when i comes to speed, that probably even is a better solution.

 

Cheers!