views:

43

answers:

2

i have a tweet table that stores tweets and its replies like this

tweet(id, parent_id, tweet_message, time)

where parent_id is a self id if its a tweet and is of parent id if its a reply. how to do a find to pull out tweets where id = parent_id

example:

tweet(1, 1, 'My name is Harsha', 'time') parent_id = id since its a tweet and not a reply tweet(2, 1, 'Hello Harsha', 'time') parent_id = 1 which tells its a reply to the tweet with id = 1

+1  A: 

This should theoretically work. Look at the SQL Debug log to find out what it does if there are any errors

$conditions = array(
    'Tweet.id = Tweet.parent_id'
    );
$tweets = $Tweet->find('all', $conditions);

Or if you are looking for a specific ID you can do

$conditions = array(
    'Tweet.id' => 1,
    'Tweet.parent_id' => 1
    );
$tweets = $Tweet->find('all', $conditions);

you can use MySQL <> not operator to search for all nonroot tweets

$conditions = array(
    'Tweet.id' => 1,
    'Tweet.parent_id <>' => 1
    );
$tweets = $Tweet->find('all', $conditions);

or

'Tweet.id <> = Tweet.parent_id'
Yarek T
thanks. the first one was what i was trying. i was doing it like this $conditions = array( 'Tweet.id' = 'Tweet.parent_id' );
Harsha M V
now for the replies how can i do it the other way round where 'Tweet.id is not equal to Tweet.parent_id'
Harsha M V
thanks will try it
Harsha M V
You should however consider using tree behaviour as suggested by Nik. Cake's Model behaviours are quite useful if your model grows complex.
Yarek T
+2  A: 

Why don't you use the Tree behaviour?

Probably this suggested from Yarek T will work but only for one level.

Nik