[Imc-uk-process] PROPOSAL - media topics

[Zak]

yossarian wrote:
> zcat at ametrika.com wrote:
>> one question would it automaticly grab all the audio posts from before
>> it is implimented? or would we have to trawl them all and tick them?

> Technically speaking, I have no idea how to make this work at the
> moment, to be honest, but I guess we would try to make it grab all the
> old stuff automatically if possible.

Yes, this should be easy to do at the database level. The gory details
follow (I'm copying this to imc-uk-tech).


Zak.


-- Assuming the topics are called 'audio', 'images' and 'video'


-- Populate 'audio' topic

INSERT INTO content_x_topic (content_id, topic_id)
  SELECT DISTINCT content.id,
                  ( SELECT id FROM topic WHERE filename='audio' )
  FROM content
    INNER JOIN content_x_media ON content.id=content_x_media.content_id
    INNER JOIN audio ON content_x_media.media_id=audio.id;


-- Populate 'images' topic

INSERT INTO content_x_topic (content_id, topic_id)
  SELECT DISTINCT content.id,
                  ( SELECT id FROM topic WHERE filename='images' )
  FROM content
    INNER JOIN content_x_media ON content.id=content_x_media.content_id
    INNER JOIN images ON content_x_media.media_id=images.id;


-- Populate 'video' topic

INSERT INTO content_x_topic (content_id, topic_id)
  SELECT DISTINCT content.id,
                  ( SELECT id FROM topic WHERE filename='video' )
  FROM content
    INNER JOIN content_x_media ON content.id=content_x_media.content_id
    INNER JOIN video ON content_x_media.media_id=video.id;


-- And, if the articles need regenerating after adding to the new topics
-- (although I can't see why they would):

UPDATE content
  SET webdb_lastchange=NOW(), is_produced=FALSE
  WHERE id IN ( SELECT content_id
                  FROM content_x_topic
                    INNER JOIN topic ON topic_id=topic.id
                  WHERE topic.filename IN ('audio','images','video') );

-- All done.