How can I achieve this request with a transaction ?

[Nico7522]

Hello !

I have a request that create a simple order ( commands table ), then attach I the products throught a many to many (commands_products table ).
Here my code

Command model

export default class Command extends BaseModel {
  @column({ isPrimary: true })
  public id: number

  @column.dateTime({ autoCreate: true })
  public created_at: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updated_at: DateTime

  @column()
  public user_id: number 

  @belongsTo(() => User, { foreignKey: "user_id"})
  public user: BelongsTo<typeof User>

  @manyToMany(() => Product, {
    pivotTable: 'command_products',
    pivotColumns: ['quantity'],
    pivotTimestamps: true
    
  })
  public products: ManyToMany<typeof Product>
}

Product model has these line for the relationship

@manyToMany(() => Command, {
    pivotTable: "command_products",
    pivotTimestamps: true,
  })
  public commands: ManyToMany<typeof Command>;

Controller

public async store({ request, response }: HttpContextContract) {
    const trx = await Database.transaction();
    try {
      const userId = request.cookie("id");
      const allProducts = request.body().products;
  
      if (!userId || !allProducts || allProducts.length < 1) {
        await trx.rollback(); 
        return response.notFound();
      }
  
      
      for (const product of allProducts) {
        if (!product.product_id || !product.quantity) {
          await trx.rollback();
          return response.badRequest('Données de produit invalides');
        }
      }
  
      const command = new Command();
      command.user_id = userId;
  
      const created = await command.save();
      const productData = {};
  
      for (const product of allProducts) {
        productData[product.product_id] = {
          quantity: product.quantity,
        };
      }
  
      await created.related("products").attach(productData);
      await trx.commit();
      
      const commandPosted = await Command.find(created.id);
  
      if (commandPosted) {
        await commandPosted.load("products");
      }
  
      return commandPosted;
    } catch (error) {
      console.log('coucou');
      await trx.rollback();
      throw error; 
    }
  }

I would like that my order ( Command.save() ) are not created when my products are not valid ( no product or invalid id ). But when this is the case, my command is created anyway. But on the product_command table not (and this is ok). How can I achieve this with a transaction ? I have look at the doc but nothing is working. I need a thing that not "save" the Command.

Thanks a lot !

[tomgobich]

Doesn't look like you're actually using the transaction. I think you're just one line away from having this working.

const command = new Command();
command.useTransaction(trx); // <-- bind the transaction to your Command model instance
command.user_id = userId;

https://docs.adonisjs.com/guides/database/transactions#using-transactions-with-lucid-models

You shouldn't need to reapply the transaction here because Lucid will cascade the transaction to the related model from created

 await created.related("products").attach(productData);

https://docs.adonisjs.com/guides/database/transactions#persisting-relationships-inside-a-transaction

[Nico7522]

Hey, thanks for you're reply.

This is what I did yesterday and it work perfectly. Just forget to mention it.
Thanks !