Raw SQL quering

Hi all! I'm a newbie in Phalcon, so maybe this question is obvious, but not for me...

I have 3 tables: * sections * posts * comments

How can I fetch for example: 10 latest posts from specific section and first and n-last comments for each post in set? When I was on Kohana framework I can perform it by subquery in JOIN clause, however PHQL doesn't support subqueries yet. I'm trying to do it by executing raw SQL query. But I still can't realize one thing. The answer it is a set of fields of three joined tables and how it can fit in created model. Like in example: ```php public static function findByCreateInterval() { // A raw SQL statement $sql = "SELECT * FROM robots WHERE id > 0";

    // Base model
    $robot = new Robots();

    // Execute the query
    return new Resultset(null, $robot, $robot->getReadConnection()->query($sql));

} ``` For example I change php $robot = new Robots(); to php $robot = new Posts(); but Posts doesn't have fileds that Comments model have to store this all in one object and have access from the view like: ```volt {% for post in posts %} {{ post.id }} {% for comment in post.comments %} {{ comment.message }} {% endfor %} {% endfor %} ``` Which is the best practice to do this? I need your help! Please! Working code will make me happy ^_^



82.7k

Hi, could you post the 3 tables definitions? is Posts n-n or 1-n to Comments?

Don't worry I already solve it by binding models to each other through hasMany, belongsTo. I don't profiling my SQL queries yet, but it seems ok and it works perfectly.