Photo Organizer

count_photos_by_user

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).

To retrieve the number of photos of a particular user.

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

• album
• album_content
• folder
• photo

Version 2.5

Still in use

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