>From what I understand of your issue, you just need 4 different counts for each user, who has sent invitations. So using a little of my experience with SQL, I would think something like this could be a starting point for you.
[code] SELECT usr.id, usr.login_name, COUNT(rem.id) AS total_invitations, COUNT(rem.active = 1) AS has_activated, COUNT(rem.registered = 1 AND rem.active = 0) AS has_registered, COUNT(rem.registered = 0) AS not_responded FROM users AS usr LEFT JOIN recomm_emails AS rem ON rem.user_id = usr.id GROUP BY usr.id, usr.login_name ORDER BY usr.login_name [/code] You probably have to look into using som IF statements in the SELECT statement, but as a starting point, it should get you going. Others are free to amend my suggestion :) Enjoy, John On Aug 9, 5:04 pm, Anna P <apad...@o2.pl> wrote: > Hello. > > It's maybe not such a CakePHP question, but rather regarding a rather > complex (for me) SQL query. > > I have a table storing e-mail addresses to which has been sent an > invite to a website: > > CREATE TABLE IF NOT EXISTS `recomm_emails` ( > `id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT, > `user_id` smallint(5) unsigned NOT NULL, > `email` varchar(100) NOT NULL, > `registered` tinyint(1) NOT NULL DEFAULT '0', > `active` tinyint(1) NOT NULL DEFAULT '0', > `created` date NOT NULL, > PRIMARY KEY (`id`) > ) > > 'user_id' is the ID of registered user, who has sent an invitation to > given e-mail address. > The 'registered' field means that user who got the invitation has > registered in website. The 'acitve' field holds the information if > invited user has activated his account after registration. > > The problem is, I have to create a statistics of invitations. List > should be divided into pages and should contain: > - User.id and User.login_name (both from users table), > - total number of all invited users, > - number of users who registered and activated account (registered=1 > and active=1), > - number of users who registered but didn't activate the account > (registered=1 and active=0) > - number of users who didn't register at all (registered=0). > I need this kind of list, because it will have to be also ordered by > selected parameters (chosen from 4 numbers above). So everything must > be in one query. > > How to write such a query? I can't come up with solution for a long > time! Please help!:) > > I first came up to an idea to do it by PHP, but it is not a very nice > solution, because on one page I shall present about 30 records, and > there is no point to get data from whole the users table (which can > have let's say couple tousend of records). -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
