Again: O2M M2O M2M relatioships

[idc77]

I'm asking again.
There's a blog with posts and comment
I'd like to know the comment count when querying posts that belong to a blog.
How do I define the schema?
How do I query for the posts of a blog and have the comment count returned in the posts query?

[idc77]

SELECT *, count(SELECT * FROM comment WHERE post = post.id) as comment_count FROM post;

always returns the number of all comments.
In regular SQL you'd do this:
https://stackoverflow.com/questions/33948817/sql-count-amount-of-comments-for-each-post-written-by-specific-author

But there is not postID field.
SurrealDB's documentation is severely lacking.
You invent some new query language but don't explain how to use it and don't respond to questions.

[suleimanas]

like this:

SELECT *,
    (SELECT count() FROM comment WHERE post_id = $parent.id group all) AS comment_count
FROM post
WHERE blog_id = blog:1;

more:
https://surrealdb.com/docs/surrealdb/surrealql/functions/database/count
https://surrealdb.com/docs/surrealdb/surrealql/parameters#parent-this

[timpratim]

Hi @idc77 ,
Is this what you're trying to achieve?

[timpratim]

Here is the link if you want to try it out in Surrealist.

[suleimanas]

Hi. Yes you're on the right path:

keep in mind surrealql is flexible, so you can do:

SELECT *,
    (SELECT count() FROM comment WHERE post.id = $parent.id group all)[0].count AS comment_count
FROM post
WHERE blog.id = $blog1[0].id;

• or -

SELECT *, count(SELECT 1 FROM comment WHERE post.id = $parent.id) FROM post WHERE blog.id = $blog1[0].id;