many-to-many selection as array

const data = await db
.select({
id: posts.id,
content: posts.content,
imageUrl: posts.imageUrl,
placeholder: posts.placeholder,
allowComments: posts.allowComments,
createdAt: posts.createdAt,
author: {
id: users.id,
username: users.username,
firstName: users.firstName,
lastName: users.lastName,
imageUrl: users.imageUrl,
},
likeCount: count(likes.postId),
likedByMe: countDistinct(likes.userId),
})
.from(posts)
.leftJoin(users, eq(posts.authorId, users.id))
.leftJoin(hashtagsToPosts, eq(hashtagsToPosts.postId, posts.id))
.leftJoin(hashtags, eq(hashtags.id, hashtagsToPosts.hashtagId))
.leftJoin(likes, eq(posts.id, likes.postId))

.where(cursor ? lte(posts.createdAt, new Date(cursor)) : undefined)
.limit(limit + 1)
.orderBy(desc(posts.createdAt))
.groupBy(posts.id)
const data = await db
.select({
id: posts.id,
content: posts.content,
imageUrl: posts.imageUrl,
placeholder: posts.placeholder,
allowComments: posts.allowComments,
createdAt: posts.createdAt,
author: {
id: users.id,
username: users.username,
firstName: users.firstName,
lastName: users.lastName,
imageUrl: users.imageUrl,
},
likeCount: count(likes.postId),
likedByMe: countDistinct(likes.userId),
})
.from(posts)
.leftJoin(users, eq(posts.authorId, users.id))
.leftJoin(hashtagsToPosts, eq(hashtagsToPosts.postId, posts.id))
.leftJoin(hashtags, eq(hashtags.id, hashtagsToPosts.hashtagId))
.leftJoin(likes, eq(posts.id, likes.postId))

.where(cursor ? lte(posts.createdAt, new Date(cursor)) : undefined)
.limit(limit + 1)
.orderBy(desc(posts.createdAt))
.groupBy(posts.id)
How to join hashtagsToPosts with hashtags to data selection
2 Replies
Angelelz
Angelelz12mo ago
You'll need some type of aggregation logic here Which is probably exacly what you want. Depending on your dialect there are different array aggregation functions
Kai Revona
Kai RevonaOP12mo ago
Can you give me some array aggregation functions for my code I use MySQL Planetscale. Please update my code with array aggregation, hashtagsToPosts is an array with joins
Want results from more Discord servers?
Add your server