Gadget Developers•2mo ago

Returning related data in api query

Hi, I'm trying to get all my subscriptions and return all the orders linked to that subscription record. I believe I have the schema setup correctly and have the syntax for the query correclty, but all it does is return the ID and not the entire record. If I write out the "select" in it's entirety for each column, I get a lint error. Attached some images of the schemas and the query that's not returning what' I'd like. Also attached is the result of the console log for the subscriptionsWithAllOrders query. I'm hoping maybe I can just do a join of some kind in my findMany method, but no luck and not seeing any documentation on how to do this. Thanks

11 Replies

Chocci_Milk•2mo ago

Hello, rechargeSubscriptionId is only the id of the record. You need to use something like this:

rechargeSubscription: {
  id: true,
  ...
}

rechargeSubscription: {
  id: true,
  ...
}

webzer33OP•2mo ago

@Chocci_Milk Thanks Antoine. Do you have a link to the documentation on this? That seems to be the correct way, but I need these to join on a specific column. Say this is the flow. - I get the rechargeOrder like it shows in my image attached - The rechargeOrder has a column rechargeSubscription. - rechargeSubscription has a column named rechargeSubscriptionId (different from what the autogenerated PK gives) - I think I need to modify my approach because currently if I do it how I have it, the rechargeSubscription comes back as null

    order = await api.rechargeOrder.maybeFindFirst({
      filter: {
        AND: [
          { shopId: { equals: shopId } },
          { rechargeOrderId: { equals: Number(eoid) } },
        ],
      },
      select: {
        id: true,
        rechargeOrderId: true,
        shopId: true,
        rechargeSubscription: {
          id: true,
          status: true,
          nextChargeScheduledAt: true,
          price: true,
          quantity: true,
          productTitle: true,
          shopId: true,
        },
        rechargeCustomer: {
          id: true,
          email: true,
          firstName: true,
          lastName: true,
          shopId: true,
        },
      },
    });
  }
  console.log('### order', order);

    order = await api.rechargeOrder.maybeFindFirst({
      filter: {
        AND: [
          { shopId: { equals: shopId } },
          { rechargeOrderId: { equals: Number(eoid) } },
        ],
      },
      select: {
        id: true,
        rechargeOrderId: true,
        shopId: true,
        rechargeSubscription: {
          id: true,
          status: true,
          nextChargeScheduledAt: true,
          price: true,
          quantity: true,
          productTitle: true,
          shopId: true,
        },
        rechargeCustomer: {
          id: true,
          email: true,
          firstName: true,
          lastName: true,
          shopId: true,
        },
      },
    });
  }
  console.log('### order', order);

the console log returns this:

  ### order {
    id: '1',
    rechargeOrderId: 1046798140,
    shopId: '76377096443',
    rechargeSubscription: null,
    rechargeCustomer: null,
    __typename: 'RechargeOrder'
  }

  ### order {
    id: '1',
    rechargeOrderId: 1046798140,
    shopId: '76377096443',
    rechargeSubscription: null,
    rechargeCustomer: null,
    __typename: 'RechargeOrder'
  }

I can assure you that the values relate between the two tables. I have the type setup where the rechargeOrder belongs to one rechargeSubscription and that rechargeSubscription has man rechargeOrders

Chocci_Milk•2mo ago

For more information on relationships and your specific API, you can take a look at the API references and the specific schema for the model you're trying to fetch

webzer33OP•2mo ago

I've looked over the documentation and I was hoping that maybe there was a link to something that had more complicated examples. Is there no documentation for that?

Chocci_Milk•2mo ago

We don't have many examples of related model filtering. If you explain exactly what you're trying to do and what models you need, I can probably give you a short snippet

webzer33OP•2mo ago

The internal docs do have some more examples. That's good to know. However, it still doesn't completely help. The query above should work. Here's the schemas and data to prove so. Again, the console log is returning this

  ### order {
    id: '1',
    rechargeOrderId: 1046798140,
    shopId: '76377096443',
    firstName: 'Todd',
    shopifyOrder: null,
    rechargeSubscription: null,
    rechargeCustomer: null,
    __typename: 'RechargeOrder'
  }

  ### order {
    id: '1',
    rechargeOrderId: 1046798140,
    shopId: '76377096443',
    firstName: 'Todd',
    shopifyOrder: null,
    rechargeSubscription: null,
    rechargeCustomer: null,
    __typename: 'RechargeOrder'
  }

did I set up the schema incorrectly?

webzer33OP•2mo ago

when I create the record, I do link it

Smelvin•2mo ago

If im understanding the relationship intended for the Schema, the Order is the Parent and Subscription is the users within the plan? I can take a better look if you can share the app URL Im looking at the query, would you be able to share what its for? I dont immidiately see any issues that would cause the results not to show but II do see that rechargeOrderId is a string not number

webzer33OP•2mo ago

the Subscription is the parent and has many Order https://clingy.gadget.app/ The rechargeOrderId is a number on the recahrgeOrder model. Would that be it? Do relations have to be strings?

Smelvin•2mo ago

I checked the docs in your app and this is how you could write a rechargeOrder query

const order = await api.rechargeOrder.maybeFindFirst({
  filter: {
    AND: [
      { shopId: { equals: shopId } },
      { rechargeOrderId: { equals: Number(eoid) } },
    ],
  },

  select: {
    id: true,
    rechargeOrderId: true,
    shopId: true,
    shopifyOrder: {
      id: true,

    },
    rechargeSubscription: {
      id: true,
      rechargeSubscriptionId: true,
      status: true,
      nextChargeScheduledAt: true,
      productTitle: true,
 
    },
    rechargeCustomer: {
      id: true,
      rechargeCustomerId: true,
      firstName: true,
      lastName: true,

    },
  },
});

console.log('Order with relationships:', order);

const order = await api.rechargeOrder.maybeFindFirst({
  filter: {
    AND: [
      { shopId: { equals: shopId } },
      { rechargeOrderId: { equals: Number(eoid) } },
    ],
  },

  select: {
    id: true,
    rechargeOrderId: true,
    shopId: true,
    shopifyOrder: {
      id: true,

    },
    rechargeSubscription: {
      id: true,
      rechargeSubscriptionId: true,
      status: true,
      nextChargeScheduledAt: true,
      productTitle: true,
 
    },
    rechargeCustomer: {
      id: true,
      rechargeCustomerId: true,
      firstName: true,
      lastName: true,

    },
  },
});

console.log('Order with relationships:', order);

If I understand the relationships correct this should be correct

webzer33OP•2mo ago

Thank you, I'll give a try I went to try this and this is what I currently have implemented and it's not working You might be able to confirm this. But would it be because of a tenancy issue? These values get injected via a CRON job in my global actions.

Gaming

Programming

Returning related data in api query

Did you find this page helpful?