Skip to content

Instantly share code, notes, and snippets.

@luchaos
Last active Jan 6, 2017
Embed
What would you like to do?
Laravel Spatie Activity Log for PostGRES
-- use case: find activities of a resource and/or within its related resources
-- causes sequential scans and is a tad slower because of the explicit json casting:
SELECT *
FROM activity_log
WHERE (log_name = 'resources' AND subject_id = 351030)
OR (log_name = 'related_resources' AND (properties :: JSON -> 'attributes' ->> 'resource_id') :: INT = 351030)
ORDER BY created_at DESC;
-- switch to JSONB
-- no existing data gets lost
-- could be done initially by rewriting the CreateActivityLogTable migration as well: $table->jsonb('properties')->nullable();
ALTER TABLE public.activity_log
ALTER COLUMN properties TYPE JSONB USING properties :: JSONB;
-- add index on logname and json attribute
-- creating a jsonb index with laravel's schema builder feels a bit messy but should be possible as well. not database driver independent!
CREATE INDEX "activity_log_log_name_properties-attributes-resource_id"
ON activity_log (log_name, ((properties -> 'attributes' ->> 'resource_id') :: INT));
-- add index on logname and subject id
-- easily doable within the initial laravel migration
CREATE INDEX "activity_log_log_name_subject_id"
ON activity_log (log_name, subject_id);
-- bliss. no more sequential scans, index scans only, average speed noticeably better
-- no more explicit json casting for properties column needed
SELECT *
FROM activity_log
WHERE (log_name = 'related_resources' AND (properties -> 'attributes' ->> 'resource_id') :: INT = 351030)
OR (log_name = 'resources' AND subject_id = 351030)
ORDER BY created_at DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment