DT
Drizzle Team•2y ago
finn

re-formatting duplicated data

so i have a db with the schema shown below a userPost object consists of the following: 
  {
    id,
    description,
    createdAt,
    expiresAt,
    user: {
      id,
      premium,
      lastRefreshedAt,
    },
    user_items: [{
      assetId,
      itemId,
      imageUrl,
    },],
    user_items_items: [{
      id,
      name,
      special,
      rarity,
      imageUrl
    },],
    items: [{
      id,
      name,
      special,
      rarity,
      imageUrl
    },
    {
      id,
      name,
      special,
      rarity,
      imageUrl
    },],
  }
  {
    id,
    description,
    createdAt,
    expiresAt,
    user: {
      id,
      premium,
      lastRefreshedAt,
    },
    user_items: [{
      assetId,
      itemId,
      imageUrl,
    },],
    user_items_items: [{
      id,
      name,
      special,
      rarity,
      imageUrl
    },],
    items: [{
      id,
      name,
      special,
      rarity,
      imageUrl
    },
    {
      id,
      name,
      special,
      rarity,
      imageUrl
    },],
  }
So: - The user has created a posts which has an id, description, createdAt, expiresAt - Within this, they have chosen to include some of their own user_items, which are linked to this post via post_user_items - Each user_items has a corresponding items based on the item_id, and this contains the specific information about the item like its name - They also choose generic items from the database, which are linked to this post via post_items I need to be able to retrieve a list of userPosts where one of the items linked via post_items has an item_id = {something}
3 Replies
finn
finnOP•2y ago
So far I have tried 
async function getPostsByItemId(itemId: number): Promise<void> {
  const itemsHas = alias(items, "items_has");
  const itemsWants = alias(items, "items_wants");
  const piw = alias(postItems, "piw");

  const postsWithItemId = await db
  .select()
  .from(posts)
  .innerJoin(users, eq(posts.userId, users.id))
  .innerJoin(postUserItems, eq(posts.id, postUserItems.postId))
  .innerJoin(userItems, eq(postUserItems.assetId, userItems.assetId))
  .innerJoin(itemsHas, eq(userItems.itemId, itemsHas.id))
  .innerJoin(postItems, eq(posts.id, postItems.postId))
  .innerJoin(itemsWants, eq(postItems.itemId, itemsWants.id))
  .where(exists(
    db.selectDistinct()
    .from(piw)
    .where(
      and(
        eq(piw.postId, posts.id),
        eq(piw.itemId, itemId)
      )
    )
  ));
async function getPostsByItemId(itemId: number): Promise<void> {
  const itemsHas = alias(items, "items_has");
  const itemsWants = alias(items, "items_wants");
  const piw = alias(postItems, "piw");

  const postsWithItemId = await db
  .select()
  .from(posts)
  .innerJoin(users, eq(posts.userId, users.id))
  .innerJoin(postUserItems, eq(posts.id, postUserItems.postId))
  .innerJoin(userItems, eq(postUserItems.assetId, userItems.assetId))
  .innerJoin(itemsHas, eq(userItems.itemId, itemsHas.id))
  .innerJoin(postItems, eq(posts.id, postItems.postId))
  .innerJoin(itemsWants, eq(postItems.itemId, itemsWants.id))
  .where(exists(
    db.selectDistinct()
    .from(piw)
    .where(
      and(
        eq(piw.postId, posts.id),
        eq(piw.itemId, itemId)
      )
    )
  ));
but this returns one result for each postItems in the post, where everything is duplicated except the postItems and itemsWants are different i have been banging my head against the wall for forever now would really appreciate some help ❤️
finn
finnOP•2y ago
finn
finnOP•2y ago
veryy confused tho 
const userPosts = await db.query.posts.findMany({
  // where ...
  columns: {
    id: true,
    description: true,
    createdAt: true,
    expiresAt: true,
  },
  with: {
    user: {
      columns: {
        id: true,
        premium: true,
        lastRefreshedAt: true
      }
    },
    userItems: {
      columns: {},
      with: {
        userItem: {
          columns: {
            assetId: true,
            itemId: true,
            imageUrl: true,
          },
          with: {
            item: {
              columns: {
                id: true,
                name: true,
                special: true,
                rarity: true,
              }
            }
          }
        }
      }
    },
    items: {
      columns: {},
      with: {
        item: {
          columns: {
            id: true,
            name: true,
            special: true,
            rarity: true,
            imageUrl: true,
          }
        }
      }
    }
  }
});
const userPosts = await db.query.posts.findMany({
  // where ...
  columns: {
    id: true,
    description: true,
    createdAt: true,
    expiresAt: true,
  },
  with: {
    user: {
      columns: {
        id: true,
        premium: true,
        lastRefreshedAt: true
      }
    },
    userItems: {
      columns: {},
      with: {
        userItem: {
          columns: {
            assetId: true,
            itemId: true,
            imageUrl: true,
          },
          with: {
            item: {
              columns: {
                id: true,
                name: true,
                special: true,
                rarity: true,
              }
            }
          }
        }
      }
    },
    items: {
      columns: {},
      with: {
        item: {
          columns: {
            id: true,
            name: true,
            special: true,
            rarity: true,
            imageUrl: true,
          }
        }
      }
    }
  }
});
okay so this feels very close however, how can i add a where that only returns userPosts where one of the items.item.id = 1 almost? 
const itemId = 1;
const userPosts = await db.query.posts.findMany({
  where: (post, { eq }) => exists(
    db.select().from(posts.items).where(
      eq(posts.items.itemId, itemId)
    )
  ),
  ...
const itemId = 1;
const userPosts = await db.query.posts.findMany({
  where: (post, { eq }) => exists(
    db.select().from(posts.items).where(
      eq(posts.items.itemId, itemId)
    )
  ),
  ...
just confusing 
const itemId = 1;
const userPosts = await db.query.posts.findMany({
  where: (post) => exists(
    db.select({}).from(postItems).limit(1).where(
      and(
        eq(postItems.postId, post.id),
        eq(postItems.itemId, itemId)
      )
    )
  ),
  ...
const itemId = 1;
const userPosts = await db.query.posts.findMany({
  where: (post) => exists(
    db.select({}).from(postItems).limit(1).where(
      and(
        eq(postItems.postId, post.id),
        eq(postItems.itemId, itemId)
      )
    )
  ),
  ...
seems inefficient? plz help i wanna do something like 
where: (post, { sql }) => (sql`(exists(select 1 from ${post.items} where item_id=${itemId})
where: (post, { sql }) => (sql`(exists(select 1 from ${post.items} where item_id=${itemId})
what am i doing wrong? @Andrew Sherman any chance you could help when you have a sec please? i don’t really understand what ${post.items} actually is here in terms of type, attributes etc
Want results from more Discord servers?
Add your server