CakePHP: creating an array field that holds the count of specific values from another array

Question

I'm working with someone else's CakePHP code, with minimal knowledge of PHP, so this may be a very basic question.

Within a controller, the following code begins to create an array that's then passed to a .ctp file:

$prefs_by_user = $this->PanelPref->find('all', array(
    'fields' => array(
        'UserTimeSlot.day_time_slot_id', 'PanelPref.panel_id', 'Panel.name',
        'COUNT(DISTINCT PanelPref.user_id) AS panels_int' ,

Within PanelPref are panel ratings that may equal 1, 2, or 3. I would like to add fields that count how many people have given this particular panel each rating. I attempted this:

        'COUNT(PanelPref.panel_rating_id = 3) AS rated_three',
        'COUNT(PanelPref.panel_rating_id = 2) AS rated_two',
        'COUNT(PanelPref.panel_rating_id = 1) AS rated_one',

but all that does is count PanelPref.panel_rating_id and since there are as many ratings as there are users, all the variables end up with the same value.

I've tried using == and => instead of =, but they return errors.

I've tried COUNT(PanelPref.panel_rating_id WHERE PanelPref.panel_rating_id = 3) AS rated_three and gotten an error.

I've tried using array_count_values but it doesn't seem to work within the array fields (and I'm probably not using it properly anyway).

Any thoughts on how to make this work? It's not vital but I would really like to have it.

XtinaS · Accepted Answer · 2013-06-04 23:07:23Z

3

Replace those with these three:

'SUM(CASE PanelPref.panel_rating_id WHEN 3 THEN 1 ELSE 0 END) AS rated_three',
'SUM(CASE PanelPref.panel_rating_id WHEN 2 THEN 1 ELSE 0 END) AS rated_two',
'SUM(CASE PanelPref.panel_rating_id WHEN 1 THEN 1 ELSE 0 END) AS rated_one'

Those items are part of the "fields" array, which correspond to the SELECT area of a generic T-SQL code block. What it looks like you want is a count of ratings for the item, and so this sum of case is the sort of hacky workaround for this.

answered Jun 4, 2013 at 23:07

XtinaS

1943 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chaos · Accepted Answer · 2013-06-07 22:49:00Z

1

This should work too:

'SUM(PanelPref.panel_rating_id = 3) AS rated_three',
'SUM(PanelPref.panel_rating_id = 2) AS rated_two',
'SUM(PanelPref.panel_rating_id = 1) AS rated_one',

answered Jun 7, 2013 at 22:49

chaos

125k34 gold badges306 silver badges310 bronze badges

Collectives™ on Stack Overflow

CakePHP: creating an array field that holds the count of specific values from another array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related