...

View Full Version : Tricky sql-grouping for a photo-database needed



Timeout
04-23-2009, 04:35 PM
Hi,

i have the following table saving photos:

+---------+--------+------------+
| photoID | userID | datUpload |
+---------+--------+------------+
| 1700001 | 1 | 1235400142 |
+---------+--------+------------+
| 1700002 | 1 | 1235400154 |
+---------+--------+------------+
| 1700003 | 2 | 1235400158 |
+---------+--------+------------+
| 1700004 | 3 | 1235400161 |
+---------+--------+------------+
| 1700006 | 1 | 1235400167 |
+---------+--------+------------+
| 1700007 | 1 | 1235400169 |
+---------+--------+------------+
| 1700008 | 1 | 1235400175 |
+---------+--------+------------+
| 1700009 | 4 | 1235400182 |
+---------+--------+------------+

which is filled with photos (auto increment), the user and the date uploaded (and some other things).

What i need as output is the following:

+---------+--------+------------+----------+
| photoID | userID | datUpload | aggCount |
+---------+--------+------------+----------+
| 1700009 | 4 | 1235400182 | 1 |
+---------+--------+------------+----------+
| 1700008 | 1 | 1235400175 | 3 |
+---------+--------+------------+----------+
| 1700004 | 3 | 1235400161 | 1 |
+---------+--------+------------+----------+
| 1700003 | 2 | 1235400158 | 1 |
+---------+--------+------------+----------+
| 1700002 | 1 | 1235400154 | 2 |
+---------+--------+------------+----------+


which means that i want only the latest photo from a user as long as there are no uploads from other users in between his uploads... and i need the number of photos which were uploaded in a row. (it is also enough if i knew that he uploaded more than one photo)

BUT if a user upload i.e. 4 photos then another user upload 1 and then the first user upload another 2 the first user should come 2 times like FIRST user, SECOND user, FIRST user

i have no idea, how this can be reached.

thanks for your help.

Fumigator
04-23-2009, 07:22 PM
I thought about this and couldn't think of any way to do it just with SQL. It would be pretty easy with a server language (PHP or whatever) though.

Old Pedant
04-23-2009, 10:22 PM
Seems to me like you could do it in a stored procedure, by creating a temp table and then putting records into the temp table as you looped through individual records in the main table.

But that would be as much code, and as slow, as just doing it in the server-side language, as Fumigator said.

If this is something you do all the time, though, then maybe the right answer is to create the second table one time and then use a TRIGGER to keep it updated as new records are added to the main table? (Or, likely better, don't allow direct inserts into the main table; require that they be performed by way of a stored procedure.)

Because no matter where it's done, creating this weird set of data is going to be an expensive operation. So better to do it once, upon insert of the data, than have to be continuously making a lengthy query.

Timeout
04-23-2009, 11:12 PM
and what about an SQL-possibility to display only one or two photos of a user per day, but still to know if there are more than this one or two...

Old Pedant
04-23-2009, 11:59 PM
Sorry, do not understand that.

Try describing again?

Timeout
04-24-2009, 10:54 AM
I mean i have for example

Monday
User 1 - 6 Photos
User 2 - 7 Photos
User 3 - 2 Photos
User 4 - 1 Photo

Tuesday
User 1 - 2 Photos
User 2 - 3 Photos
User 3 - 1 Photos
User 4 - 0 Photo

Wednesday
User 1 - 1 Photos
User 2 - 0 Photos
User 3 - 5 Photos
User 4 - 3 Photo


The result set should be:

userID --- photo --- day --- more than 2
-------------------------------------------------
1 --- latest photo --- Wed --- no
3 --- latest photo --- Wed --- yes
3 --- penultimate photo --- Wed --- yes
4 --- latest photo --- Wed --- yes
4 --- penultimate photo --- Wed --- yes
1 --- latest photo --- Tue --- no
1 --- penultimate photo --- Tue --- no
2 --- latest photo --- Tue --- yes
2 --- penultimate photo --- Tue --- yes
3 --- latest photo --- Tue --- no
1 --- latest photo --- Mon --- yes
1 --- penultimate photo --- Mon --- yes
2 --- latest photo --- Mon --- yes
2 --- penultimate photo --- Mon --- yes
3 --- latest photo --- Mon --- no
3 --- penultimate photo --- Mon --- no
4 --- latest photo --- Mon --- no


which means: from every user the latest 2 Photos per day with a flag that tells me, if there are more than two or not. Ordered by date desc.

Fumigator
04-24-2009, 05:30 PM
May I ask why this all must be done with one query?

Old Pedant
04-24-2009, 11:59 PM
Looks like a fun one!

I agree w/ Fumigator: Probably easier to do this with a combo of SQL and JSP/PHP/ASP/whatever coding. But...

Let's give it a shot:

First, the table:


Table: Photos
photoid : autonumber [optional if photodate has date and time]
userid : int, foreign key to users table
photodate : datetime [assumed to contain both date and time when uploaded]
photoname : file name on disk? [or could have blob to hold photo]

...


Then let's see what we come up with:


SELECT X.photonum, X.userid, X.theDay, X.pdate, X.howmany, pa.photoname
FROM (
SELECT 1 AS photonum, userid, DATE(photodate) AS theDay, MAX(photodate) AS pdate, COUNT(*) as howmany
FROM photos
GROUP BY userid, DATE(photodate)
UNION
SELECT 2, p.userid, DATE(p.photodate), MAX(p.photodate), -1
FROM photos AS p WHERE photodate NOT IN (
SELECT MAX(p2.photodate) FROM photos AS p2
WHERE p2.userid = p.userid
)
GROUP BY p.userid, DATE(p.photodate)
) AS X,
photos AS pa
WHERE X.userid = pa.userid AND X.pdate = pa.photodate
ORDER BY X.userid, X.theDay, X.photonum

.


If the photodate field has only the date, then we would NEED the photoid AUTOINCREMENT field, in order to distinguish the most recent and next most recent photo per day.

Do you see why it works?

First, we find the latest photo, per user, per day, along with the count of photos for that day. We add on the "1 AS photonum" to make sure that, when we finally order the records, this latest photo will come out in the list before...

Second, we find the penultimate photo, per user, per day. To do this, we find the latest photo, per user, per day, *EXCEPTING* for the photo we found in the First step. [And if the inner SELECT doesn't work, as given, in MySQL, there are other ways to do this. Creating a VIEW would be a big help.]

So we UNION those two SELECTs and we will have data something like this:


userid :: theDay :: photonum :: howMany :: pDate
33 :: Apr 4 :: 1 :: 4 :: Apr 4 7:32 PM
33 :: Apr 4 :: 2 :: -1 :: Apr 4 6:58 PM
33 :: Apr 5 :: 1 :: 1 :: Apr 5 12:30 AM
...


Finally, we JOIN that UNION result back to the Photos table, again, to pick up the photoname (or blob).

Does that make sense?

If you do have an AUTONUMBER photoid field in there, the query can be just a tiny bit simpler/faster, I think.

Oh...if not obvious: If photonum is 1, then howMany will tell you how many total photos by that user on that day. (The number is meaningless when photonum is 2.)

bazz
04-25-2009, 01:28 AM
I wonder if a new col could be added to the table of photos. Call it 'upload_number'. So when a person uploads some pics, the insert statement checks for the max(upload_number) and (in time for the actual insert) it increments it. resultant table would be as shown below and the query would need only then select the max(upload_number) in order to do what the OP asks for.




+---------+--------+------------+-------------------+
| photoID | userID | datUpload | upload_number |
+---------+--------+------------+-------------------+
| 1700001 | 1 | 1235400142 | 5 |
+---------+--------+------------+-------------------+
| 1700002 | 1 | 1235400154 | 5 |
+---------+--------+------------+-------------------+
| 1700003 | 2 | 1235400158 | 4 |
+---------+--------+------------+-------------------+
| 1700004 | 3 | 1235400161 | 3 |
+---------+--------+------------+-------------------+
| 1700006 | 1 | 1235400167 | 2 |
+---------+--------+------------+-------------------+
| 1700007 | 1 | 1235400169 | 2 |
+---------+--------+------------+-------------------+
| 1700008 | 1 | 1235400175 | 2 |
+---------+--------+------------+-------------------+
| 1700009 | 4 | 1235400182 | 1 |
+---------+--------+------------+-------------------+




Of course, I shall defer to the greater learning of the language by Fumigator and The Old Pedant.

bazz



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum