Hey team! I'm wondering how to make queries with one to many relationships. My specific example is an invoicing app where each

Invoice

has many

LineItems

. My db model is setup where the

LineItem

has a reference as

invoiceId

to the primary key of the corresponding invoice. I'm wondering how can query and create invoices that includes that line items themselves? The only thing I found in the docs is to include the foreign key relationship which I already have setup. So, I know I can query an invoice from a line item, but how do I query line items from an invoice?

Hello @James Q Quick! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User so that they can see your message and help you as soon as possible! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ``...`` menu) and select "Leave Thread" to unsubscribe from future updates. Want to change the title? Use the ``/title`` command! We have solved your problem? Click the button below to archive it.

I think what you are looking for is this:

.from('messages')
        .select('*, test!inner(*)')
        .eq('test.message_id',7)

Where 7 is the message id and test is a table with an fk to messages table. It returns: image

That does seem like what I'm looking for. Can you help me understand the syntax there? Is

test!inner(*)

saying to grab all items and call them an array called

test

then the selector is in the

eq

line specifying which items we want to get it?

Sorry, test is a table that has fk's pointing to messages that I quickly made to make sure I got the right syntax.

Do you know how

eq

works specifically with a dynamic parameter. Like if I'm querying for an array of Invoices and their associated LineItems? I tried doing

.eq(`LineItem.invoiceId`, `Invoice.id`);

but I'm getting a new error

invalid input syntax for type bigint: "Invoice.id"'

Why are you passing a string in?

I'm not sure how to reference the id of the dynamically retrieved invoices. In your example, you have it hard coded to 7, but I'd like to tie it back to the dynamic invoices that I'm querying

You would pass in the invoice id with a variable

It's not a variable yet unfortunately. Like I'm not just querying for line items with a specific invoice. I'm querying all invoices AND their associated line items

Ah, totally different

So here's an example with the hard coded Invoice id I have so far.

const { data: loadedInvoices, error: invoicesError } = await supabase
      .from('Invoice')
      .select(
        `
          id, status, subject, dueDate, issueDate, 
          discount, notes, amount,
          client:clientId (id, name, email, address, city, state, zip, phone),
          user:userId (id, name, email, address, city, state, zip, phone),
          LineItem!inner(*)
        `
      ).eq(`LineItem.invoiceId`, 51);

I thought you were wanting AN invoice with all the line times for it in your call.

ah yeah. This would be all invoices and their associated lineItems. I'm thinking it would be something close to what we have right now.

Not so sure from the client as you don't have the invoice ids and don't want to get and loop thru them client side.

Yeah right now, I have two separate queries and then merging the data together, but i feel like there's got to be a better way

For sure using an rpc and postgres function. Maybe a view also. I'm skeptical the PostgREST API the client uses though can generate the proper request, but I'm not a guru on it.

I think I got it.

const { data: loadedInvoices, error: invoicesError } = await supabase
      .from('Invoice')
      .select(
        `
          id, status, subject, dueDate, issueDate, 
          discount, notes, amount,
          client:clientId (id, name, email, address, city, state, zip, phone),
          user:userId (id, name, email, address, city, state, zip, phone),
          LineItem: lineItems
        `
      );

LOL

It automatically is associating the line items appropriately because of the relationship!!

Sorry, for anyone scanning this later, we both posted the without .eq at sametime....

Somehow I just broke it lol

NOOOOO!

Wait yeah I lied. It hadn't worked before lol

https://postgrest.org/en/stable/api.html#resource-embedding is what the client eventually uses.

That got me back to where I want it to be lol. Only thing is the naming. THe property in the returned value is

LineItem

instead of

lineItems