Best approach to store counters

It's better to add a column to the mysql table.

@dany68 Well it depends on what metrics you actually want to track. Do you just want to literally count these things? Or do you need other information such as who downloaded what, when, etc?

Hello,

Redis or File Cache seems to be good. You create a key for the metric like product_1_download_count and increment this on every download for example. Once per minute (10 minutes or hours) with a Scheduled Task (https://laravel.com/docs/10.x/scheduling) that run a Command (https://laravel.com/docs/10.x/artisan#generating-commands) :

• you take the count stored in product_1_download_count and add this in your database.
• Reset the counter to 0.

You can build you key for every product like 'product_' . $product->id . 'count_download' and in your Scheduled Task get all the products and loop on it to update count in database and and restore count to 0.

You can even make it cleaner by defining interface and service like:

Interface MetricInterface {
    public static function metricKeyForModel(): string;
}

Class Product extends Model implements MetricInterface {
    public static function metricKeyForModel(): string
    {
     return __CLASS__; // Or you can use something else
    }
}

Class MetricService {
    public const DOWNLOAD_METRIC_KEY = '_count_download';
    public const PURCHASE_METRIC_KEY = '_count_purchase';

    public function getDownloadMetricFor(MetricInterface $model): string
    {
        return $model::metricKeyForModel() . $model->id . self::DOWNLOAD_METRIC_KEY;
    }

    public function getPurchaseMetricFor(MetricInterface $model): string
    {
        return $model::metricKeyForModel() . $model->id . self::PURCHASE_METRIC_KEY;
    }

    public function saveAndResetCounter(string $metric)
	{
	    // Save in database
		// Reset counter
     }
}

like this you can for every model you can get his metric key easily

$product = Product::findOrFail(1);
MetricService::getDownloadMetricFor($product);
MetricService::getPurchaseMetricFor($product);

In your Task you can do something like:

class ProductCounter extends Command
{
    protected $signature = 'product:counter';
 
    protected $description = 'Save all counters and reset them in database for products';

    public function handle(Product $product): void
    {
        foreach ($product->all() as $product) { // Chunk would be better instead of all
            MetricService::saveAndResetCounter(MetricService::getDownloadMetricFor($product))
            MetricService::saveAndResetCounter(MetricService::getPurchaseMetricFor($product))
        }
    }
}

PS: With you use exactly this code you will have one issue: In the MetricService::saveAndResetCounter you would not know for which product save this metric in database. To prevent this you could define function getDownloadMetricAttribute and getPurchaseMetricAttribute directly in the model class (instead of MetricService). So you would be able to do: $product->purchaseMetric

saveAndResetCounter should become:

// I know very long name (better architecture will prevent this but I don't have an IDE and time right now :/
public function saveAndResetCounterForModelWithAttributeAndKeyMetric(MetricInterface $model, string $attibuteToUpdateInDatabase, string $metricKey): void
{
	    $model->${attibuteToUpdateInDatabase} = $model->${attibuteToUpdateInDatabase} + Cache::get($metricKey);
        Cache::set($metricKey, 0):
 }

and ProductCounter should become:

class ProductCounter extends Command
{
    protected $signature = 'product:counter';
 
    protected $description = 'Save all counters and reset them in database for products';

    public function handle(Product $product): void
    {
        foreach ($product->all() as $product) { // Chunk would be better instead of all
            MetricService::saveAndResetCounterForModelWithAttributeAndKeyMetric($product, 'download_count', $product->downloadMetric)
            MetricService::saveAndResetCounterForModelWithAttributeAndKeyMetric($product, 'purchase_count', $product->purchaseMetric)
        }
    }
}

Hope that will help you :)