Stefan Stefan - 5 months ago 42
MySQL Question

Don't show dublicates posts, when tags of this post are equal to one topic

i ve got topics with corresponding tags, when user add corresponding tag to post, this post will display on this topic:
enter image description here

as you can see, if my corresponding tags, like "js" and "css" on image, equal to one post, my post dublicates, how i can remove this dublicate and show only one post, that have "css" and "js" tags.

"get corresponding tags by topic" model:

function getTopicTagsByTopicId($i)
{
$this->db->where('topic_tags.id_topic', $i);
$this->db->join('user_topics', 'topic_tags.id_topic = user_topics.topic_id', 'left');
$query = $this->db->get('topic_tags');
return $query->result_array();
}


"get posts by corresponding tag of the topic" model:

function getTopicPostsByCorrespondingTagSlug($i, $u)
{
$this->db->join('posts_tags', "posts_tags.postid = posts.post_id AND posts_tags.creator_id =$u", 'left');
$this->db->join('tags', "tags.id_tag = posts_tags.tagid", 'left');
$this->db->where('tags.tag_slug', $i);
$query = $this->db->get("posts");
return $query->result_array();
}


view:

<?php foreach($topics as $topic): ?>

<div class="spacer"></div>
<div class="topic small" data-topic-id="<?php echo $topic['topic_id']; ?>">

<a class="showProgress"><?php echo $topic['topic_name']; ?></a>

<?php if ( $topic['user_id'] == $this->session->userdata('userid') ) { ?>
<a class="edit" title="Edit Topic">
<div class="icon">
<i class="fa fa-pencil"></i>
</div>
</a>
<a class="remove-topic" rel="nofollow" title="Remove <?php echo $topic['topic_name']; ?> topic">
<i class="fa fa-times"></i>
</a>
<?php } ?>

</div>

<?php $topic_tags = $this->stories_model->getTopicTagsByTopicId($topic['topic_id']); ?>
<?php if (count($topic_tags)>0): ?>
<div class="spacer-small"></div>
<?php foreach($topic_tags as $topic_tag): ?>
<p><?php echo $topic_tag['slug_tag']; ?></p>
<?php $corresponding_tags = $this->stories_model->getTopicPostsByCorrespondingTagSlug($topic_tag['slug_tag'], $u['user_id']); ?>
<?php if ( count( $corresponding_tags ) > 0 ) : ?>
<div class="feed-items" data-links-tag="<?php echo $topic_tag['slug_tag']; ?>">
<?php foreach( $corresponding_tags as $corresponding_tag ) : ?>

<div class="link" data-link-id="<?php echo $corresponding_tag['post_id']; ?>" id="link-<?php echo $corresponding_tag['post_id']; ?>">
<a href="<?php echo $corresponding_tag['post_url']; ?>"><?php echo $corresponding_tag['post_subject']; ?></a>
</div>

<?php endforeach; ?>
</div>
<?php endif; ?>

<?php endforeach; ?>
<?php endif; ?>

<?php endforeach; ?>


my DB:

user_topics:

enter image description here

topic_tags:

enter image description here

Answer

You can combine the 2 methods in a single method with a single database query, since the tables in the currently used 2 methods are related. I know sql better, so I'm providing you an sql based solution, you can adapt it to codeigniter.

select topic_tags.slug_tag, t1.post_id, t1.post_url, t1.post_subject
from topic_tags 
inner join user_topics on topic_tags.id_topic = user_topics.topic_id
left join
    (select max(topic_tags.slug_tag) as unique_slug_tag, posts.post_id, posts.post_url, posts.post_subject
    from topic_tags 
    inner join user_topics on topic_tags.id_topic = user_topics.topic_id
    inner join tags on tags.tag_slug = topic_tags.slug_tag
    inner join posts_tags on tags.id_tag = posts_tags.tagid
    inner join posts on posts_tags.postid = posts.post_id AND posts_tags.creator_id =$u
    where topic_tags.id_topic = $i
    group by posts.post_id, posts.post_url, posts.post_subject) t1 on topic_tags.slug_tag = t1.unique_slug_tag
where topic_tags.id_topic = $i

The subquery gets you the list of posts by associated tags, a post appears under a single tag only thanks to the group by. The tag will be the highest tag name per alphabetical order (max()). The outer query gets all the tags the topic is associated with and gets the associated posts (if any) for each of them.

$i and $u are parameters that need to be supplied by your php program similarly to how they are used in the current code.

Comments