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:

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:

{% 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 ^_^



88.1k

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.