User Tools

Site Tools


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';

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