...

View Full Version : Aggregate data from multiple mysql tables



Leppy
03-09-2009, 05:20 PM
Hello,

I working on a pages where I list all of the latest activities a specific users. Among those activities are comments, pages, status updates, etc.

I am wondering how I should do this... I have 2 ideas.

1. Create a new table called "users_activities" where I would have 2 columns

Reference table (example: comments)
Reference id (example: 1)


Problems: Too many queries - For each record in users_activities, I would have to have to query the referenced table to get the details.

2. Query my tables separately so SELECT from comments, pages, status updates then sorting it all out with PHP

Problems: Many queries here again and I might query tables I don't even need to.

Does anyone has dealt with this kind of problem before?

Any help is appreciated...

Fumigator
03-09-2009, 05:42 PM
I've done this same thing-- recent activity. My solution was an "activities" table that gets a new row every time something happens. It's redundant and not all that efficient, but I make sure it doesn't get too big with a periodic purge of older stuff and it works fine for me.

More details: I store only the primary key to the table the activity relates to and then get the details as needed.



CREATE TABLE `activity_tbl` (
`activity_id` int(11) NOT NULL auto_increment,
`person_id` int(11) NOT NULL default '0',
`action_type_cd` char(8) NOT NULL default '',
`reference_id` int(11) NOT NULL default '0',
`updated_ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`created_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`activity_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=800000 ;

Leppy
03-09-2009, 07:49 PM
It's interesting...

I am considering this solution, but I am just worry about the amount of queries I will have to do to.

Having 1 activity table and 3 other tables, I think I could do this in 4 queries... I'm kinda OCD about queries, I hate doing them!

Fumigator
03-09-2009, 09:32 PM
With an activities table you can use one query.

Leppy
03-09-2009, 11:13 PM
Even to retrieve information for other tables? How would you do that? Conditional joins?

My activities table contains information about multiple other table (comments, status, pages, etc.), how in 1 query could you get all the information of the activity depending if it is a comment, a status or something else?

Fumigator
03-09-2009, 11:56 PM
The way I did it (and there are surely other ways, some probably better) is I used a CASE statement in the query to determine which table to do a subquery on depending on the type of activity was on each row.

For example:



SELECT action_type_cd
,reference_id
,created_ts
,case
when action_type_cd = 'ADDALBUM'
then (select album_nm from album_tbl where album_id = reference_id)
when action_type_cd = 'ADDTHRED' or action_type_cd = 'POSTRPLY'
then (select title_ds from mb_thread_tbl where thread_id = reference_id)
when action_type_cd = 'ADDEVENT' or action_type_cd = 'EDTEVENT'
then (select event_ds from event_tbl where event_id = reference_id)
when action_type_cd = 'ADDLTR'
then (select title_ds from letter_tbl where letter_id = reference_id)
when action_type_cd = 'PUBLSHNL'
then (select title_ds from newsletter_tbl where newsletter_id = reference_id)
else ''
end as ref_nm
FROM activity_tbl as a
ORDER BY created_ts desc
LIMIT 50

Leppy
03-10-2009, 05:58 AM
This helps alot, I will look into the CASE operator.

Thanks!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum