https://supabase.com/ logo
Join Discord
Powered by
Order of rows
# javascript
m
Order of rows
Hi! 🙂 Can you help me with a query? I have a table 📄 with names of customers 👨‍🎨 🙎 🧑‍🌾 sorted by their delivery order (1, 2, 3...) 🚚 I want a customer to change place in this delivery order 🤔
j
do you need a supabase-js query or SQL query? and what do you mean by change place?
m
Hi! I would like a supabase-js query. I would like a customer to change order with the customer before.
j
do you mean you want to change a customer's delivery order number with the customer before? if so, you'll probably need an `.update()`: https://supabase.io/docs/reference/javascript/update perhaps something like:
Copy code
const { data, error } = await supabase
  .from(your_table)
  .update({ order: 2 })
  .match({ id: 9 })
which will change customer 9's order number to 2
m
I need update both customers else they will have the same number in the order. Is there a smart query for that or do I need to write two queries?
How do I even make two queries in one request?
This one works but there might be a better way?
Copy code
const { data } = await supabase
        .from('customers')
        .select('id, delivery_order')
        .match({ delivery_order: order-1 })
        .single();
    const { body, error } = await supabase
        .from('customers')
        .upsert([
            {'id': request.params.id, 'delivery_order': order-1},
            {'id': data.id, 'delivery_order': order}]);
m
I think what you're wanting here is a transaction (https://www.postgresql.org/docs/13/tutorial-transactions.html) and if there are constraints on 
delivery_order
then you likely want to defer those too (https://www.postgresql.org/docs/13/sql-set-constraints.html)
j
then you'll need to write an SQL function in your database that does the update for both users in a transaction as mentioned above, and call the function from supabase using `.rpc()`: https://supabase.io/docs/reference/javascript/rpc
m
Thanks! I need to move some logic to the database and there is no way I can update a whole column or table with one supabase-js query.
This stored procedure/function made the job:
Copy code
update customers
set delivery_order = 0
where id = ( select id from customers where delivery_order = row_id-1 );
update customers 
set delivery_order = row_id-1
where id = ( select id from customers where delivery_order = row_id );
update customers
set delivery_order = row_id
where id = ( select id from customers where delivery_order = 0 );
I needed to use the zero number since I could not use two arguments for the function.
PreviousNext
Powered by