MySQL: Joining multiple tables to one table using multiple attributes of that single table

0
votes

I have 3 tables:

  • Activities
  • Posts
  • Comments

Activities has two attributes: item_id and item_type. Attribute item_type specifies if it is a post or a comment and item_id contains the id of the respective type. Comments are also linked to posts. I need to sort my activities according to post created time. Now since activities can be linked to posts or comments, how can I define the association of such kind and write a query in cake php?

EDIT:
The attributes relevant to this question are as follows
Activities: item_id, item_type
Posts: id, created
Comments: id, post_id

2
mysqlcakephp
Could you please provide full table descriptions? – vhu
@vhu: Do you need complete description of that much is good enough? Other details might not be contextually relevant – ChiragAgarwal
when an Activity is related to a Comment (and not to a Post) how you want to take into account of it? Do you want to exclude from the list? Do you put it at the bottom of the list or do you want to look at the created date of the post related to the comment? – arilia
@arilia If Activity is related to comment, it is indirectly related to post. Using this indirect relation, i need to sort them according to the post's created time – ChiragAgarwal
ok, try my answer and see if it works – arilia

2 Answers

0
votes

In your case post has a comment without post a comment cannot be there I'm not a cake php person but i can explain from a sample query.

SELECT * FROM Activities a   
       INNER JOIN Posts p ON a.item_id = p.item_id   
       INNER JOIN Comments c ON c.post_id = p.id 
ORDER BY created

I think this will help you.

0
votes

in your Activity Model

public $belongsTo = array
(
    'Post' => array
    (
        'foreignKey' => 'item_id',
        'conditions' => array('Activity.item_type' => 'Post')
    ),
    'Comment' => array
    (
        'foreignKey' => 'item_id',
        'conditions' => array('Activity.item_type' => 'Comment')
    )
);

so in your ActivitiesController you can do

$this->Activity->virtualFields['post_created'] => "IF(Activity.item_type = 'Post'), Post.created, CommentPost.created)"; 

$this->Activity->find
(
    'all',
    array
    (
        'joins' => array
        (
            array
            (
                'table' => 'posts',
                'alias' => 'CommentPost',
                'conditions' => array(
                    'Comment.post_id = Post.id'
            )
        ),
        'order' => array('Activity.post_created'), 
    )
);