Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Jul 2007
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Unhappy Aggregate data from multiple mysql tables

    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
    1. Reference table (example: comments)
    2. 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...
    -

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

    Code:
    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 ;

  • #3
    New Coder
    Join Date
    Jul 2007
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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!
    -

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    With an activities table you can use one query.

  • #5
    New Coder
    Join Date
    Jul 2007
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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?
    Last edited by Leppy; 03-09-2009 at 11:21 PM.
    -

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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:

    Code:
    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

  • Users who have thanked Fumigator for this post:

    Leppy (03-10-2009)

  • #7
    New Coder
    Join Date
    Jul 2007
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts
    This helps alot, I will look into the CASE operator.

    Thanks!
    -


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •