Last active
January 6, 2017 22:04
-
-
Save luchaos/26779524c42099d35d86344b919b50d3 to your computer and use it in GitHub Desktop.
Laravel Spatie Activity Log for PostGRES
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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