Blog App Tutorial Part 4

type CategoryRow record {
    int id;
    string name;
    string slug;
    string? description;
};

CategoryRow tech = check (check db.'from(Category).create({
    name: "Technology",
    slug: "technology",
    description: "Tech-related posts"
})).cloneWithType();

CategoryRow tutorial = check (check db.'from(Category).create({
    name: "Tutorial",
    slug: "tutorial",
    description: "Step-by-step guides"
})).cloneWithType();

record {}? postRow = check db.'from(Post)
    .'where({status: {'equals: PUBLISHED}})
    .findFirst();

if postRow is record {} {
    PostRow post = check postRow.cloneWithType();

    _ = check db.rawExecute(
        "INSERT INTO post_categories (post_id, category_id) VALUES ($1, $2), ($3, $4)",
        [post.id, tech.id, post.id, tutorial.id]
    );
}

PostRow[] posts = check (check db.'from(Post)
    .'where({
        AND: [
            {
                OR: [
                    {status: {'equals: PUBLISHED}},
                    {status: {'equals: DRAFT}}
                ]
            },
            {title: {contains: "Ballerina"}}
        ]
    })
    .orderBy({createdAt: orm:DESC})
    .take(10)
    .findMany()).cloneWithType();

record {}[] userEmails = check (check db.'from(User)
    .'select({id: true, email: true, name: true})
    .'where({status: {'equals: ACTIVE}})
    .findMany()).cloneWithType();

record {}? thambaruRow = check db.'from(User)
    .'where({email: {'equals: "hi@thambaru.com"}})
    .findUnique();

if thambaruRow is record {} {
    UserRow thambaru = check thambaruRow.cloneWithType();

    int postCount = check db.'from(Post)
        .'where({authorId: {'equals: thambaru.id}})
        .count();

    // use postCount
    _ = postCount;
}