XF 2.2 order by using db adapter query

AndrewSimm

Well-known member
I have done some var_dumps and the right string is being assigned to $sort. What am I doing wrong that is preventing this from working? The query itself works fine, it just doesn't return using the right order by.

PHP:
$sort = $this->filter('sort', 'str');

if(empty($sort))
{
    $sort = 'year';
}

$db = $this->app->db();
$recruitingYears = $db->fetchAll('
select
year.name as "year"
,sum(case when player.rating_star = 5 then 1 else 0 end) as five_star
,sum(case when player.rating_star = 4 then 1 else 0 end) as four_star
,sum(case when player.rating_star = 3 then 1 else 0 end) as three_star
,count(1) as total
,(sum(case when player.rating_star = 5 then 1 else 0 end) + sum(case when player.rating_star = 4 then 1 else 0 end)) / count(1) * 100 as blue_chip
,(sum(player.rating_star) / count(1)) as average_stars
,(sum(case when player.state_id = team.state_id then 1 else 0 end) / count(1) )*100 as in_state
from xf_andrew_prms_player player
inner join xf_andrew_prms_year year on player.year_id = year.year_id
inner join xf_andrew_team team on player.team_id = team.team_id
where 1=1
and player.sport_id = ?
and player.team_id = ?
group by year.name
order by ? desc', [$sportId, $teamId, $sort]);
 
Last edited:

XFA

Well-known member
Year being a specific keyword maybe you need to do:
PHP:
$sort = '"year"';

So that year is as you did in the year.name row.
 
Top