Order data based on count of related table data

Question

I have Two diff tables as given below:

users and posts

Need data from user's table order by count of posts table

Relationship is defined as:

User Model:

public $hasMany = array('Post');

Post Model

Public $belongsTo = array('User');

Answer 1

counterCache - Cache your count()

This function helps you cache the count of related data. Instead of counting the records manually via find('count'), the model itself tracks any addition/deleting towards the associated $hasMany model and increases/decreases a dedicated integer field within the parent model table.

The name of the field consists of the singular model name followed by a underscore and the word “count”:

my_model_count

Let’s say you have a model called ImageComment and a model called Image, you would add a new INT-field to the image table and name it image_comment_count.

Once you have added the counter field you are good to go. Activate counter-cache in your association by adding a counterCache key and set the value to true:

<?php
class Image extends AppModel {
    public $belongsTo = array(
        'ImageAlbum' => array('counterCache' => true)
    );
}

From now on, every time you add or remove a Image associated to ImageAlbum, the number within image_count is adjusted automatically.

You can also specify counterScope. It allows you to specify a simple condition which tells the model when to update (or when not to, depending on how you look at it) the counter value.

Using our Image model example, we can specify it like so:

<?php
class Image extends AppModel {
    public $belongsTo = array(
        'ImageAlbum' => array(
            'counterCache' => true,
            'counterScope' => array('Image.active' => 1) // only count if "Image" is active = 1
    ));
}