podoc:count_photos_by_user_sql_function
Database Function Description of "count_photos_by_user"
Name
count_photos_by_user
Description
Count the photos of a particular user.
There are two versions, one that counts all photos and one that counts only those photos up to a given access rights setting (either in the folders or in the albums).
Purpose
To retrieve the number of photos of a particular user.
Schema
CREATE FUNCTION count_photos_by_user(INTEGER) RETURNS BIGINT AS 'select count(identifier) from photo where users = $1' LANGUAGE 'sql';
CREATE FUNCTION count_photos_by_user(INTEGER, INTEGER) RETURNS BIGINT AS 'select count(photo.identifier) from photo, folder where photo.folder=folder.identifier and photo.users = $1 and photo.access_rights <= $2 and (folder.access_rights <= $2 or (select album.access_rights from album, album_content where album_content.album=album.identifier and album_content.photo=photo.identifier and album.access_rights <= $2 limit 1) <= $2) ' LANGUAGE 'sql';
Related tables
History
Used first
Version 2.5
Used last
Still in use
Change history
= Version 2.5 =
CREATE FUNCTION count_photos_by_user(INTEGER) RETURNS BIGINT AS 'select count(identifier) from photo where users = $1' LANGUAGE 'sql';
CREATE FUNCTION count_photos_by_user(INTEGER, INTEGER) RETURNS BIGINT AS 'select count(photo.identifier) from photo, folder where photo.folder=folder.identifier and photo.users = $1 and photo.access_rights <= $2 and folder.access_rights <= $2' LANGUAGE 'sql';
= Version 2.8 =
-- Count photos per user for a specified access level. -- A photo is accessible (visible), if its parent folder and the -- photo itself has lower or equal access_rights to the specified one. -- If the photo's visibility is restricted by its parent folder -- we also take in consideration any album which might include the photo. -- This is a temporary solution anyway. If we want to be accurate -- we should make a recursive plpgsql script, counting all photos -- of public directories, keeping the subselect for the albums. DROP FUNCTION count_photos_by_user(INTEGER, INTEGER); CREATE FUNCTION count_photos_by_user(INTEGER, INTEGER) RETURNS BIGINT AS 'select count(photo.identifier) from photo, folder where photo.folder=folder.identifier and photo.users = $1 and photo.access_rights <= $2 and (folder.access_rights <= $2 or (select album.access_rights from album, album_content where album_content.album=album.identifier and album_content.photo=photo.identifier and album.access_rights <= $2 limit 1) <= $2) ' LANGUAGE 'sql';
podoc/count_photos_by_user_sql_function.txt · Last modified: 2007/04/23 14:27 by Luud