[Dbix-class] How to count?...

[Alex Povolotsky]

Bruce J Keeler wrote:
> Alex Povolotsky wrote:
>> Hello!
>>
>> I have a simple tree-like structure of galleries, albums and photos; 
>> to count photos in all albums, I've tried to
>>
>> sub count_photos {
>>    my $self = shift;
>>    return $self->photos->search->count;
>> }
>>
>> which apparently returns wrong result. Can I somehow
> Is gid the gallery id?  If so, that query would count the number of 
> photos in all albums in that gallery.  Have you mis-identified gid as 
> the primary key of Album?
Yes, it is.
>>
>> select count(*) from  photo photos LEFT JOIN album me ON ( photos.aid 
>> = me.aid ) WHERE ( me.gid = ? ) ?
>>
>> Or am I missing something quite SQL-trivial?
> That should be equivalent to the first query.
Oh really?

Well, let's see:

gallery_tarkhil=> \d gallery                                  Table 
"public.gallery"
  Column  |          Type          |                       
Modifiers                      
----------+------------------------+-------------------------------------------------------
 gid      | integer                | not null default 
nextval('gallery_gid_seq'::regclass)
 login    | character varying(64)  | not null
 password | character(32)          | not null
 ftpdir   | character varying(200) |
Indexes:
    "gallery_pkey" PRIMARY KEY, btree (gid)
    "gallery_login_idx" UNIQUE, btree (login)

gallery_tarkhil=> \d album
                                 Table "public.album"
 Column |          Type          |                      
Modifiers                     
--------+------------------------+-----------------------------------------------------
 aid    | integer                | not null default 
nextval('album_aid_seq'::regclass)
 gid    | integer                | not null
 name   | character varying(128) | not null
 descr  | text                   |
 pid    | integer                |
Indexes:
    "album_pkey" PRIMARY KEY, btree (aid)
    "album_full" UNIQUE, btree (gid, name)
Foreign-key constraints:
    "album_gid_fkey" FOREIGN KEY (gid) REFERENCES gallery(gid)
    "defaultpic" FOREIGN KEY (pid) REFERENCES photo(pid)
gallery_tarkhil=> \d photo
                                 Table "public.photo"
 Column |          Type          |                      
Modifiers                     
--------+------------------------+-----------------------------------------------------
 pid    | integer                | not null default 
nextval('photo_pid_seq'::regclass)
 aid    | integer                | not null
 name   | character varying(128) |
 descr  | text                   |
Indexes:
    "photo_pkey" PRIMARY KEY, btree (pid)
Foreign-key constraints:
    "photo_aid_fkey" FOREIGN KEY (aid) REFERENCES album(aid)

gallery_tarkhil=> SELECT COUNT( * ) FROM album me LEFT JOIN photo photos 
ON ( photos.aid = me.aid ) WHERE ( me.gid =1);
 count
-------
     1
(1 row)

gallery_tarkhil=> select count(*) from  photo photos LEFT JOIN album me 
ON ( photos.aid = me.aid ) WHERE ( me.gid = 1);
 count
-------
     0
(1 row)

To clarify:

gallery_tarkhil=> select count(*) from gallery;
 count
-------
     1
(1 row)

gallery_tarkhil=> select count(*) from album;
 count
-------
     1
(1 row)

gallery_tarkhil=> select count(*) from photo;
 count
-------
     0
(1 row)

Alex