SELECT Join as SELECT

you are trying to select Replies.id from Posts_Comments.

just fix this query first

SELECT
    Replies.id 
  FROM Posts_Comments 
  WHERE Replies.id != 0
  ORDER BY Replies.id
  LIMIT 1

if you still got problem give us the create statment of the tables, with 2 inserts, just so I can play with the query

The code dosent work.. :-/ You can try it you're self, here is the SQL with two inserted... Thanks alot!

EDIT: I can see that the reply column only can have 1 integer, that's and fault, it has to be 11, but it dosent matter when you test it :-) Just so you dont get confused

CREATE TABLE `posts_comments` (
`id` int(11) NOT NULL,
  `postid` int(11) DEFAULT NULL,
  `blogid` int(11) NOT NULL,
  `userid` int(11) DEFAULT NULL,
  `reply` int(1) NOT NULL DEFAULT '0',
  `name` varchar(60) COLLATE utf8_danish_ci DEFAULT NULL,
  `comment` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL,
  `reports` int(11) DEFAULT '0',
  `date` date DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=235 DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

--
-- Data dump for tabellen `posts_comments`
--

INSERT INTO `posts_comments` (`id`, `postid`, `blogid`, `userid`, `reply`, `name`, `comment`, `reports`, `date`) VALUES
(1, 1, 0, 1, 0, 'Name', 'dsadsdsaads', 0, NULL),
(2, 1, 0, 1, 1, 'Name 2', 'sffsdfdfsfsd', 0, NULL);

here is your query, working :

SELECT 
    posts_comments.postid, posts_comments.comment
FROM
    posts_comments
        LEFT JOIN
    (SELECT 
        *
    FROM
        posts_comments
    WHERE
        posts_comments.id != 0
    ORDER BY posts_comments.id DESC
    LIMIT 1) AS Replies ON posts_comments.postid = Replies.postid
        AND posts_comments.blogid = Replies.blogid
        AND posts_comments.id = Replies.reply

but i am not sure if this query makes sence. Tell me if you need more help ( but explain what you want to achieve )

Well, it kinda works, but not the way i want it to.

What i'm trying im trying to build is a comment system with reply comments

The code you gave me prints out like this:

Comment 1 answer 1
Comment 1 answer 2
Comment 1 answer 3
Comment 1
Comment 2

I need it like this:

Comment 1
Comment 2
Comment 3
    Comment 3 answer 1
    Comment 3 answer 2
    Comment 3 answer 3
Comment 4
Comment 5
    Comment 5 answer 1
    Comment 5 answer 2

Do you have any idea on how to make this?

Thanks for your help so far!

With SQL imopossible(or VERY sneaky), with nonsql possible. Just get all comments, loop over them and get all answers for them OR select all ANSWERS with left join comment - thet it will work and group them maybe byt comment ? or something like this

Why can't it be done? I've done this before, just not with" left join ( SELECT.. )"

I tried it without, but I can't use this becuase I need to set a limit to left join, and if i dont do it this way, the LIMIT 12 will merge the two tables together, so if there is 4 answers, and 12 comments, it will only print 8 comments enstead of 12, becuase it needs to 4 answers..

Sorry if im bad at explaning..

@GunslerRage, I probably can make the query you are looking for, but i dont think it will be worth it.

because for each comment, the query will fetch all its replies, but each reply will content the original comment itself. And at sertant point I think it is quite possible this to become performance issue because of the "fetching data" of the mysql.

When i did comments with replies, i did seperated query for each comment to check for its replies. You can make milions of solutions in order to save some queries.

for eample you can make trigger, to update the number of the replaies for a sertant comment, so when you are fetching "blog" comments you will display the number of the replays, when the user clicks in "expand replaies" then you can load all the replaies (via ajax), for this specific comment. ( google does somethign similar i think )

another way is to cache your queries.

And also, there are external solutions for comments, like facebook and some 100% fully ready solutions including the design. And it will take you few moments to intergrate and thats it. After that you wont have to worry about anything.

Anyways, let me know if you still want this query.

I want what's the best solution for my site, but I can't use any 3rd party comment system, it has to be integrated in the system.. So that every user that own the comments can edit them..

If im going to do this, what is the best way of doing it? Could you maybe provide me some code?

And thanks again for using your time to help me out! :-)

postsComments.php

namespace MyNameSpace\Models;

class postsComments extends \Phalcon\Mvc\Model {

    public $id;
    public $postid;
    public $blogid;
    public $userid;
    public $reply;
    public $name;
    public $comment;
    public $reports;
    public $date;

    public function beforeCreate(){
        $this->date = time(); //ot date('y .. bla bla i dont them by hearth)
    }

    public function initialize(){

        $this->setSource("posts_comments");
    }

    public static function displayComments($blog_id, $limit = 10){

        $comments = self::find([
            'conditions' => 'blogid = ?1',
            'bind' => [
                1 => $blog_id
            ],
            "limit" => $limit
        ]);

        if (count($comments) == 0){
            return ' no comment babe';
        }

        foreach ($comments AS $k => $comment){
            $comments[$k]['replays'] =
                self::find([
                    'conditions' => 'replay = ?1',
                    'bind' => [
                        1 => $comment->id
                    ]
                ]);
        }

        return $comments;
    }
}

and call it

var_dump(MyNameSpace\Models::displayComments($_GET['blog_id']));
exit;

code isn't tested cuz i am still at work.

But there few things, in the foreach you can use reference, i hate using references so i did not.

//just a few notes, hope i wont offend you, it is just what i think

* postid => post_id // actually what is this ?!
* blogid => blog_id
* userid => user_id
* name => ?!?
* reply => not clear, child_of or at least replay_of
* date - i usually use timestamps / but its up to you, there is no big difference

It dosent work, it just prints out the comments, and it dosent get grouped. I can't seem to get the "Replays" in the foreach I did however change the line

$comments[$k]['replays'] to $comments{$k}->replays

Else it would give me and error, that its not an object..

And I use the postid to check what post the comment belongs to

I dont know why is use blogid enstead of blog_id - Maybe becuase i can't use findFirstByBlogid? :-O

Name is the name of a person IF he/she is not logged in

reply, if 0 then it's not a reply, if not 0 then it's a reply and belongs to the giving commnent id

I am going to use timestamps, just havent changed it

Again thanks you your help! :-)

they wont get grouped, you have to

foreach ($comments AS $comment){
    echo '<div class="comment">'.$comment->comment.'</div>';

    if ($comment->replays != false or null or array i dont know ){
        foreach ($comment->replay as $replay){
            echo '<div class="replay">'.$replay->comment.'</div>';
        }
    }
}

or something like this.

in phalcon - most of the time "_" means that next capital has to be capitalized. so if you call a method add_comment your action has to be AddCommentAction

as far as i used it.

There is many - to - many relationship, that i still havent test out. Maybe you can try hasOne, hasMany just play with them in order to understand what's the idea. They work only for findFirst, but i am not sure if many - to - many works with find.

I get this error: Notice: Access to undefined property PostsComments::replays in

It seems like the replays dosent get set?

The code we used to make it work:

public static function displayComments($blog_id, $limit = 10){

    $comments = self::find([
        'conditions' => 'blogid = ?1 AND reply = ?2',
        'bind' => [
            1 => $blog_id,
            2 => 0
        ],
        "limit" => $limit
    ])->toArray();

    if (count($comments) == 0){
        return ' no comments..';
    }

    foreach ($comments AS $k => $comment){

        $data = self::find([
                'conditions' => 'reply = ?1',
                'bind' => [
                    1 => $comment['id']
                ]
            ])->toArray();

        if (isset($comments[$k]['replays'])){
            $comments[$k]['replays'] = $data;
        }

        //echo "<pre>";
        //print_r($koko->toArray())
    }

    return $comments;
}

Again, thanks for the amazing support Izo, it really means alot!