I'm building an application where users can sign petitions - each petition would have a subject attribute and a location attribute. I would have a landing page for each subject, a landing page for each location and a landing page for each petition (subject+location combo). I have a Signature eloquent model with a signatures pivot table with columns user_id, subject_id, location_id.
On the landing page for each subject, I would like to display a headline like: "XX total signatures for [subject] across YY locations!"
On the landing page for each location, I would like to display a headline like: "XX total signatures for [location] across YY subjects!"
On the petition landing page (subject/location combination), I would like to display a headline like: "XX total signatures for [subject] in [location]!"
On the home page, I would like to display a headline like: "XX total signatures across YY subjects in ZZ locations!"
I was thinking to create a SignatureObserver which would increment or decrement the relevant values any time a signature is added or removed like this:
class SignatureObserver
{
public function created(Signature $signature)
{
Cache::increment("total_signatures");
Cache::increment("location_signatures:{$signature->location_id}");
Cache::increment("subject_signatures:{$signature->subject_id}");
Cache::increment("subject_location_signatures:{$signature->subject_id}:{$signature->location_id}");
}
public function deleted(Signature $signature)
{
Cache::decrement("total_signatures");
Cache::decrement("location_signatures:{$signature->location_id}");
Cache::decrement("subject_signatures:{$signature->subject_id}");
Cache::decrement("subject_location_signatures:{$signature->subject_id}:{$signature->location_id}");
}
}
I have not tested this just yet, but I'm wondering would this be the most efficient way of storing and accessing these stats? Would events/listeners or queues jobs be a better approach? Would it make sense to have a dedicated table for these stats? If I already have a bunch of signatures in the database OR if the cache is cleared - what would be the best way to call a re-count should the cache values be null at any point?
Why make it harder than it needs to be? Just count the results in the database.
$subjectCount = Subject::count()->get();
$signatureCount = Signature::count()->get();
$locationCount = Location::count()->get();
And just add the where clauses you need.
@Tray2 additional database calls on every page load. Probably not an issue unless the platform gets big. Let's assume there's 100,000 signatures for a petition wouldn't
Signature::where('location_id',1)->where('subject_id',1)->count()->get();
Take longer than just pulling the already calculated total out of cache?
@YevC Yes, it would take longer, but with the proper indexes, and making is a single query as @petrit shows , it should be fast enough. Caching is fine for things that rarely changes, and this sounds like things that change fairly often. The database also caches the query so it goes faster on the second run.
@Tray2 Interesting, got it. I will be displaying various totals in different places so in the interest of DRY practices where would it make the most sense to store the logic for these stats? Static methods on the Signatures model? Dedicated Stats service class? None of the above?
@YevC That depends a little, but I would likely use a view composer
Thank you, I looked into view composers. I ended up creating a StatsService class with a method for each calculation I need. Since there was no overlap between views, in terms of what stats are needed I figured a view composer wouldn't make sense in my implementation. I'm simply calling the relevant StatsService methods in each controller and passing them to the views that way.
class StatsService
{
public static function totalSignatures()
{
return Signature::toBase()->count();
}
public static function totalLocationsSignatured()
{
return Signature::toBase()->distinct('location_id')->count('location_id');
}
public static function totalSubjectsSignatured()
{
return Signature::toBase()->distinct('subject_id')->count('subject_id');
}
public static function totalSignaturesForSubject($subjectId)
{
return Signature::toBase()->where('subject_id', $subjectId)->count();
}
public static function totalLocationsForSubject($subjectId)
{
return Signature::toBase()->where('subject_id', $subjectId)->distinct('location_id')->count('location_id');
}
public static function totalSignaturesForLocation($locationId)
{
return Signature::toBase()->where('location_id', $locationId)->count();
}
public static function totalSubjectsForLocation($locationId)
{
return Signature::toBase()->where('location_id', $locationId)->distinct('subject_id')->count('subject_id');
}
public static function totalSignaturesForSubjectAndLocation($subjectId, $locationId)
{
return Signature::toBase()->where('subject_id', $subjectId)->where('location_id', $locationId)->count();
}
public static function totalLocationsForSubjectAndLocation($subjectId)
{
return Signature::toBase()->where('subject_id', $subjectId)->distinct('location_id')->count('location_id');
}
}
class SubjectController extends Controller
{
public function show(Subject $subject)
{
$stats = ['totalVotesForSubject' => StatsService::totalVotesForSubject($subject->id),
'totalLocationsForSubject' => StatsService::totalLocationsForSubject($subject->id)];
return view('subjects.show', compact('subject', 'stats'));
}
}
Using mysql CASE function is very efficient way getting stats:
Signature::toBase()
->selectRaw('count(case when status is null then 1 end) as signatures_1')
->selectRaw('count(case when status is null and location_id=2 then 1 end) as signatures_2')
->selectRaw('count(*) as total')
->first();
Please or to participate in this conversation.