podoc:count_photos_by_user_sql_function
This is an old revision of the document!
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.1176903477.txt.gz · Last modified: 2007/04/18 13:37 by Luud