XF 2.0 Group by and count with finder

[AndrewSimm]

What I am doing is query a list of positions and the number of records that meet all the parameters.

In SQL it would ideally look something like..

SELECT man_position_id, manage_position count(distinct(man_position_id))
FROM manage_position
WHERE blah blah
GROUP BY man_position_id, manage_position

However, since I can't do this in XF2 I did

        //Positions
        $positions = $finder->with('Position',true)->with('CollegeTeam',false)
                                    ->where('class', $class_op, $get_class)
                                    ->where('Position.man_position_id', $position_op, $get_position)
                                    ->where('state', $state_op, $get_state)
                                    ->where('rating', $rating_op, $get_rating)
                                    ->where('status', $status_op, $get_status)
                                    ->where('college', $college_op, $get_college)
                                    ->where('roster', $roster_op, $get_roster)
                           ->fetch();   
        


        //Get rating from players query
        foreach ($positions as $key => $position) {
            $x[] = $position['Position']['manage_position'];
        }

        //Group values by key and count
        $x = array_count_values($x);

        arsort($x);

        $viewParams = [
            'title' => $title,   
            'x' => $x
        ];

This works, but I can only use either man_position_id or manage_position. The problem is I need to display the position name and use the id in the link. If I could do count and group by then I would be good.

Thoughts?

 

[Mike]

Finders don't currently expose things like this as they are primarily designed around getting entities. You can still write raw SQL queries to fit your needs though. (You would likely have to be very careful with escaping though, particularly if the operator part of your condition is variable.)