使用MySQL创建新闻流，从基于提供表名和ID的3个表中进行选择

Question

I'm trying to create a news flux with MySQL... for that, I have one table 'action' which records anytime an insert or an update is made in one of 3 other tables ('article','commentaire','auteur'). Now, I have in this first table 'action' 4 columns :

• id_action (which allows to know in order when action have been taken)
• action (add/update)
• table (name of one of the 3 other table)
• id_table (corresponding id in the upper table)

The 3 other tables have different columns names and contents, thus, I have :

• table 'article' with columns 'id', 'titre', 'contenu', 'categorie'
• table 'commentaire' with columns 'id', 'contenu'
• table 'auteur' with columns 'id', 'prenom', 'presentation', 'localisation'

I've tried with joins like:

SELECT ar.*,co.*,au.*
FROM 'action' AS ac
LEFT JOIN ac.table AS tbl ON ac.id_table = tbl.id

... but it didn't give anything as table 'ac.table' doesn't exist... (actually I hoped maybe it's going to take the name of the table and would replace it in the JOIN... :p

Thus, after 2-3 trial without success, I finally found something working but the latter is quite consuming and slow and I'm looking for a better solution... the solution I found is made of 4 functions:

function showAllAction(){
   $preprequete = "SELECT id_table, table
   FROM 'action'";

   $requete = $this->pdo->prepare($preprequete);
   if ($requete->execute()) {
       $result = $requete->fetchAll(PDO::FETCH_ASSOC);
       $requete->closeCursor();
       return $result;
   }else{ die(print_r($requete->errorInfo()));} 
   return false;
}

followed by a foreach

$actions = $this->showAllAction();
foreach ($actions as $value){
   if($value['table']=='article'){
      $article = $this->showArticle($value['id_table']);
      echo $article['titre'];
   }elseif($value['table']=='commentaire'){
      $commentaire = $this->showCommentaire($value['id_table']);
      echo $commentaire['contenu'];
   }else{
      $auteur = $this->showAuteur($value['id_table']);
      echo $auteur['prenom'];
   }
}

where the différentes functions are simple SELECT like (following codes are just for illustrating, I have removed all none needed stuff to go to the point):

function showArticle($id_table){
   "SELECT titre, contenu, categorie
   FROM 'article'
   WHERE id = :id_table"
}
function showCommentaire($id_table){
   "SELECT contenu
   FROM 'commentaire'
   WHERE id = :id_table"
}
function showAuteur($id_table){
   "SELECT prenom, presentation, localisation
   FROM 'auteur'
   WHERE id = :id_table"
}

This, if anyone have a more efficient way to do what's needed, all answers are welcome! JOIN, conditionals, I have no idea what could do the trick...

Answer 1

Stick a couple of datetime columns on each table, one for creation_time and one for update_time. See [this link][http://twopieceset.blogspot.com.au/2007/12/best-practices-5-ws-of-database-design.html] for a more detailed explanation.

You can create a view to simplify your chronological lookup, such as:

create or replace view recent_updates as 
select 'article' as `table`, article_id from article 
where update_time > now() - interval 1 day 
union all
select 'comment' as `table`, comment_id from comment 
where update_time > now() - interval 1 day 
union all
...
order by update_time desc
limit 1000
;

This will stay fast even as your database grows and doesn't require a separate tracking table. Don't forget to index the time columns!

(Note: Added a new answer since this wouldn't fit in a comment)