How to make these code shorter?

Question

    $categories = PostCategoryQuery::create()->find();

    $categories_array = [];
    foreach ($categories as $k => $category):
        $posts_count = PostQuery::create()->filterByPostCategory($category)->count();
        $categories_array[$k]['category'] = $category->getName();
        $categories_array[$k]['count'] = $posts_count;
    endforeach;

    uasort($categories_array, "sortByCount");

    function sortByCount($a, $b) {
        return $a['count'] > $b['count'] ? -1 : 1;
    }

I want to get 'categories' array order by numbers of its related posts, I think there must be a way to make my code shorter, hoping get some suggestions, thanks~

Answer 1

If in your ORM you could use a simple query with group by you simply could perform a select like

    select category, count(*)
    from  post_category 
    inner join post  on post_category.category_id = post.category_id
    group by category
    order by count(*)

and then your code will be reduced to the query..

Answer 2

I don't know your model exactly, but I would recommend something like that:

$categories_array = PostQuery::create('pq')
    ->joinPostCategory('pc')
    ->withColumn('COUNT(pq.id)', 'count')
    ->groupBy('pc.id')
    ->select(array('pc.name', 'count'))
    ->orderBy('count')
    ->find();

// Should return PropelArrayCollection(
//   array('pc.name' => 'category1', 'count' => 123),
//   array('pc.name' => 'category2', 'count' => 234)
// )

This way you avoid object hydrating as you do only select the columns that you need.