Select filters for n tables on relational queries

Let's just use the schema from the docs: Users has many Posts This would give me User 1 with all of their posts:

const res = await db.query.users.findMany({
    where: eq(users.id, 1),
    with: {
        posts: true
    }
});

What if I want to retrieve the User with only the posts that have a certain keyword on them I thought this would work:

const res = await db.query.users.findMany({
    where: and(
        eq(users.id, 1),
        like(posts.content, "%bananas%"),
    ),
    with: {
        posts: true
    }
});

But apparently that searches for "bananas" on a Users column. If not possible with relational queries I'd have to use regular selects with Joins and then use Aggregations to present the data like relational queries do. Any help would be appreciated

1 Reply

Andrii Sherman•15mo ago

You need to add this where statements inside posts object

with: {
    posts: {
        where: like(posts.content, "%bananas%")
    }
}

with: {
    posts: {
        where: like(posts.content, "%bananas%")
    }
}

Gaming

Programming

Select filters for n tables on relational queries