<query-mapping package='AWA.Blogs.Models'>
    <description>
      List of blogs
    </description>

    <class name="AWA.Blogs.Models.Blog_Info" bean="yes">
        <comment>The list of blogs.</comment>
        <property type='Identifier' name="id">
            <comment>the blog identifier.</comment>
        </property>
        <property type='String' name="title">
            <comment>the blog title.</comment>
        </property>
        <property type='String' name="uid">
            <comment>the blog uuid.</comment>
        </property>
        <property type='Date' name="create_date">
            <comment>the blog creation date.</comment>
        </property>
    </class>

    <query name='blog-list'>
       <comment>Get the list of blogs that the current user can update</comment>
       <sql>
    SELECT
      blog.id,
      blog.name,
      blog.uid,
      blog.create_date
    FROM blog AS blog
    INNER JOIN acl ON acl.entity_id = blog.id AND acl.entity_type = :table AND acl.user_id = :user_id
    ORDER BY blog.create_date DESC
       </sql>
    </query>

</query-mapping>

<query-mapping package='AWA.Blogs.Models'>
    <description>
      List of blogs
    </description>

    <class name="AWA.Blogs.Models.Blog_Info" bean="yes">
        <comment>The list of blogs.</comment>
        <property type='Identifier' name="id">
            <comment>the blog identifier.</comment>
        </property>
        <property type='String' name="title">
            <comment>the blog title.</comment>
        </property>
        <property type='String' name="uid">
            <comment>the blog uuid.</comment>
        </property>
        <property type='Date' name="create_date">
            <comment>the blog creation date.</comment>
        </property>
        <property type='Integer' name="post_count">
            <comment>the number of posts published.</comment>
        </property>
    </class>

    <query name='blog-list'>
       <comment>Get the list of blogs that the current user can update</comment>
       <sql>
    SELECT
      blog.id,
      blog.name,
      blog.uid,
      blog.create_date,
      (SELECT count(*) FROM blog_post WHERE blog_post.blog_id = blog.id AND blog_post.status = 1)
    FROM blog AS blog
    INNER JOIN acl ON acl.entity_id = blog.id AND acl.entity_type = :table AND acl.user_id = :user_id
    ORDER BY blog.create_date DESC
       </sql>
    </query>

</query-mapping>

Add the number of posts in the blog
Add the number of posts in the blog
