laravel 模型事务操作,关联模型新增数据,有A,B两张表,A.id与B.organization_id关联,只有当A表插入成功,才会向B表继续插入,否则回滚数据库操作!

3 篇文章 0 订阅
laravel 模型事务操作,关联模型新增数据

有A,B两张表,A.id与B.organization_id关联,只有当A表插入成功,才会向B表继续插入,否则回滚数据库操作!

$data = $request->all();
$organization = new Organization();
$organization->name_cn          = $data['name_cn'];
$organization->sort             = $data['sort'];
$organization->intrusion_set    = $data['intrusion_set'];
$organization->campaigns        = $data['campaigns'];
$organization->attack_patterns  = $data['attack_patterns'];
$organization->industries       = $data['industries'];
$organization->seo_keyword      = $data['seo_keyword'];
$organization->seo_desciption   = $data['seo_desciption'];
$organization->detail           = $data['detail'];

//实务操作
DB::beginTransaction();
$organization->save();
$id = $organization->id; //获取插入之后返回的id
if($id){
    $event = new OrganizationEvent();
    $event->organization_id     = intval($id);
    if ($event->save()){
        DB::commit();
    } else{
        DB::rollBack();
    }
    return true;
}
return false;
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
优化sql:SELECT we.organization_id ,we.wip_entity_id ,case when wl.line_id is null then we.wip_entity_name else '' end wip_entity_name ,we.primary_item_id ,mtt.transaction_type_name ,mmt.transaction_date ,bd.department_code ,mmt.inventory_item_id ,mmt.subinventory_code ,mta.reference_account ,br.resource_code ,lu2.meaning as line_type_name ,mta.base_transaction_value ,mta.cost_element_id ,flv.meaning as cost_element ,wdj.class_code job_type_code ,ml.meaning job_type_name FROM (select * from gerp.mtl_material_transactions where substr(transaction_date,1,7) >= '2023-06' and transaction_source_type_id = 5) mmt inner join gerp.wip_entities we on mmt.organization_id = we.organization_id inner join gerp.mtl_transaction_accounts mta on mta.transaction_source_id = we.wip_entity_id and mta.transaction_id = mmt.transaction_id and mta.transaction_source_type_id = 5 inner join gerp.mtl_transaction_types mtt on mtt.transaction_type_id = mmt.transaction_type_id inner join mfg_lookups lu2 on lu2.lookup_code = mta.accounting_line_type and lu2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' inner join gerp.mtl_system_items_b msi on msi.inventory_item_id = mmt.inventory_item_id and msi.organization_id = mta.organization_id left join gerp.bom_departments bd on bd.department_id = mmt.department_id left join gerp.bom_resources br on br.resource_id = mta.resource_id left join gerp.wip_lines wl on wl.line_id = mmt.repetitive_line_id left join gerp.wip_discrete_jobs wdj on wdj.wip_entity_id = mta.transaction_source_id left join gerp.fnd_lookup_values_vl flv on cast(mta.cost_element_id as string) = flv.lookup_code and flv.lookup_type = 'CST_COST_CODE_TYPE' left join mfg_lookups ml on ml.lookup_code = wdj.job_type and ml.lookup_type = 'WIP_DISCRETE_JOB' 。其中mmt,we,mta,msi,wdj数据量很大
06-10
针对这个SQL语句,可以优化的地方有: 1. 使用JOIN语句时,可以根据数据量大小,将数据量大的放在后面,这样可以避免在连接时出现性能问题。 2. 使用子查询时,可以在子查询中添加条件筛选,减少返回的数据量。 3. 可以对查询中的日期条件进行优化,例如使用日期范围查询时,可以使用BETWEEN关键字代替大于等于和小于等于的运算符。 4. 可以对大进行分区或分片,以加快查询速度。 5. 可以对JOIN语句中的字段添加索引,以提高查询效率。 下面是对原SQL语句的优化: ``` SELECT we.organization_id, we.wip_entity_id, CASE WHEN wl.line_id is null THEN we.wip_entity_name ELSE '' END wip_entity_name, we.primary_item_id, mtt.transaction_type_name, mmt.transaction_date, bd.department_code, mmt.inventory_item_id, mmt.subinventory_code, mta.reference_account, br.resource_code, lu2.meaning as line_type_name, mta.base_transaction_value, mta.cost_element_id, flv.meaning as cost_element, wdj.class_code job_type_code, ml.meaning job_type_name FROM gerp.wip_entities we INNER JOIN ( SELECT * FROM gerp.mtl_material_transactions WHERE transaction_date BETWEEN '2023-06-01' AND '2023-06-30' AND transaction_source_type_id = 5 ) mmt ON mmt.organization_id = we.organization_id INNER JOIN gerp.mtl_transaction_accounts mta ON mta.transaction_source_id = we.wip_entity_id AND mta.transaction_id = mmt.transaction_id AND mta.transaction_source_type_id = 5 INNER JOIN gerp.mtl_transaction_types mtt ON mtt.transaction_type_id = mmt.transaction_type_id INNER JOIN mfg_lookups lu2 ON lu2.lookup_code = mta.accounting_line_type AND lu2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' INNER JOIN gerp.mtl_system_items_b msi ON msi.inventory_item_id = mmt.inventory_item_id AND msi.organization_id = mta.organization_id LEFT JOIN gerp.bom_departments bd ON bd.department_id = mmt.department_id LEFT JOIN gerp.bom_resources br ON br.resource_id = mta.resource_id LEFT JOIN gerp.wip_lines wl ON wl.line_id = mmt.repetitive_line_id LEFT JOIN gerp.wip_discrete_jobs wdj ON wdj.wip_entity_id = mta.transaction_source_id LEFT JOIN gerp.fnd_lookup_values_vl flv ON cast(mta.cost_element_id as string) = flv.lookup_code AND flv.lookup_type = 'CST_COST_CODE_TYPE' LEFT JOIN mfg_lookups ml ON ml.lookup_code = wdj.job_type AND ml.lookup_type = 'WIP_DISCRETE_JOB'; ``` 在优化后的SQL语句中,将子查询中的日期范围查询放在了WHERE语句中,将数据量较大的放在了后面,左连接的也放在了后面。同时,可以根据具体情况对需要添加索引的字段进行索引优化。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值