如何获取Laravel 5中的连接查询中的最大ID（完整性约束违反:) [英] How to get the max ID in a Join Query in Laravel 5 (Integrity constraint violation:)

问题描述

我的查询是这样的：

$deals=DB::table('leadsheet')
            ->join('Deal', 'leadsheet.leadcode', '=', 'Deal.leadcode')
             ->join('benefits', 'leadsheet.leadcode', '=', 'benefits.leadcode')
             ->join('delegatedealinfo', 'leadsheet.leadcode', '=', 'delegatedealinfo.leadcode')
              ->join('vipbooking', 'leadsheet.leadcode', '=', 'vipbooking.leadcode')
             ->where('id', DB::raw("(select max(`id`) from vipbooking)"))
           ->where('leadsheet.leadcat', '=','Delegates')

            ->get();

所以我有以下表格：

1.leadsheet 
   -- leadcode
   -- leadcat
2.Deal
   -- leadcode
3.benefits
   -- leadcode
4.delegatedealinfo
   -- leadcode
5.vipbooking
   -- leadcode

我想要做的是获取所有最大ID vipbooking code> leadcode 与所有 leadcode相同 FROM leaderheet WHERE leadheet 。 leadcat =代表

What I'm trying to do is to get all the maximum id of vipbooking form where leadcode is same as all the leadcode FROM leadsheet WHERE leadsheet.leadcat=Delegates

我唯一的问题是 MAX ID for vipbooking 表单不工作

My only problem is MAX ID for vipbooking form is not working

可以有任何一个在应用@anant提供的解决方案之后，请帮助我出来

Can any one one help me out

UPDATE1

错误

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select * from `leadsheet` inner join `Deal` on `leadsheet`.`leadcode` = `Deal`.`leadcode` inner join `benefits` on `leadsheet`.`leadcode` = `benefits`.`leadcode` inner join `delegatedealinfo` on `leadsheet`.`leadcode` = `delegatedealinfo`.`leadcode` inner join `vipbooking` on `leadsheet`.`leadcode` = `vipbooking`.`leadcode` where `id` = (select MAX(id) AS vipid from vipbooking) and `leadsheet`.`leadcat` = Delegates)

转发问题

我想获得代理用户交易的vipbooking表单的详细信息，我们可以找到交易是否供应商或代表来自Leadsheet表

I want to get the details of the vipbooking form for a Deal of a Delegate user , we can find if a Deal is Vendor or Delegate from Leadsheet table

Leadsheet

Leadsheet

id | leadcode | leadcat

1  | DL2016012| Delegates
2  | DL2016013| Delegates
3  | VL2016001| Vendors
4  | VL2016002| Vendors

交易

id | leadcode | DealAmount

1  | DL2016012| 123
2  | VL2016002| 1000
2  | DL2016013| 1200

vipbooking

vipbooking

    id | leadcode | date      | bookingtxt

    1  | DL2016012| 20-04-2016| xxx
    2  | DL2016012| 20-04-2016| dddd
    3  | VL2016012| 21-04-2016| ppp
    4  | DL2016013| 20-04-2016| xxx
    5  | DL2016013| 22-04-2016| dddd

所以我的输出应该有

 2 123 | Delegates| DL2016012| 20-04-2016| dddd
 5 1200| Delegates| DL2016013| 22-04-2016| dddd

谢谢

推荐答案

尝试这个

$deals=DB::table('leadsheet')
            ->join('Deal', 'leadsheet.leadcode', '=', 'Deal.leadcode')
             ->join('benefits', 'leadsheet.leadcode', '=', 'benefits.leadcode')
             ->join('delegatedealinfo', 'leadsheet.leadcode', '=', 'delegatedealinfo.leadcode')
              ->join('vipbooking', 'leadsheet.leadcode', '=', 'vipbooking.leadcode')
             ->where('leadsheet.id', DB::raw("(select max(`id`) from vipbooking)"))
           ->where('leadsheet.leadcat', '=','Delegates')

            ->get();

添加 leadsheet.id

这篇关于如何获取Laravel 5中的连接查询中的最大ID（完整性约束违反:)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！