Skip to content

关联查询指定表的字段

Flight表关联了多张表,如果不指定字段,默认会返回所有,显得找起来很麻烦,尤其是表中很多字段的情况!

php
$result = Flight::query()
->where(['fltNr' => $id])->with([
    'flightPlayers',
    'flightPlayers.relation',
    'flightPlayers.greenfee',
    'flightPlayers.sales',
    'flightPlayers.sales.item',
    'flightPlayers.sales.salesTransaction',
])->first();
$result = Flight::query()
->where(['fltNr' => $id])->with([
    'flightPlayers',
    'flightPlayers.relation',
    'flightPlayers.greenfee',
    'flightPlayers.sales',
    'flightPlayers.sales.item',
    'flightPlayers.sales.salesTransaction',
])->first();

输出结果

json
{
	"fltNr": 9451976,
	"fltComNr": 9,
	"fltRefType": null,
	"fltRefNr": null,
	"fltDate": 738084,
	"fltTime1Booked": 580,
	"fltTimeFixedYN": null,
	"fltTime1": 580,
	"fltBreakTime": null,
	"fltTime2": 720,
	"fltCrlNr1": 44,
	"fltCrlNr2": 0,
	"fltAloneYN": null,
	"fltHole": null,
	"fltOrigin": null,
	"fltSize": 3,
	"fltCheckedIn": 0,
	"fltTimestamp": "2020-08-20 15:51:37",
	"fltCarNr": null,
	"fltNotes": null,
	"fltDownPaymentPerc": null,
	"fltPaid": null,
	"fltAgtNr": null,
	"fltHotNr": null,
	"fltOptionDate": null,
	"fltCcard1": null,
	"fltCcard2": null,
	"fltCcardExpMonth": null,
	"fltCcardExpYear": null,
	"fltCcardCode": null,
	"fltCcard3": null,
	"fltCcard4": null,
	"fltResNr": null,
	"fltCost": null,
	"fltStatus": null,
	"fltHosNrLocked": 94,
	"fltCheckResult": null,
	"fltHosNr": 94,
	"fltEmpNr": 24,
	"flight_players": [{
		"flpNr": 29803563,
		"flpFltNr": 9451976,
		"flpMatch": null,
		"flpSide": 1,
		"flpRelNr": 309,
		"flpRelNrWildcard": null,
		"flpName": "Slof",
		"flpGrpNr1": null,
		"flpGrpNr2": null,
		"flpGrpNr3": null,
		"flpExtra": null,
		"flpPhone": "0172617000",
		"flpEmail": "595@mms.nl",
		"flpHandicap": 25.1,
		"flpGrfNr": 7,
		"flpGfcNr": null,
		"flpItmNr": 212,
		"flpPrice": 25,
		"flpBilNr": null,
		"flpGrfNrDiscount": null,
		"flpGfcNrDiscount": null,
		"flpItmNrDiscount": null,
		"flpDiscount": null,
		"flpSalNrDiscount": null,
		"flpVoucher": null,
		"flpScorecard": null,
		"flpQualifying": null,
		"flpCarNr": null,
		"flpIntro": null,
		"flpTeebox": null,
		"flpCheckResult": null,
		"flpRelNrMain": null,
		"flpEmailMarkNY": 1,
		"relation": {
			"relNr": 309,
			"relRelNrMain": null,
			"relComNr": 9,
			"relType": null,
			"relName": "Slof",
			"relGender": 1,
			"relFirstName": "D",
			"relPrefix": "",
			"relRtlNr": 1,
			"relAddress1": "Stik van Linschoten 22",
			"relAddress2": "Stik van Linschoten 22",
			"relCity": "Bergdorp",
			"relPostalCode": "2411 PZ",
			"relState": null,
			"relCouNr": 1,
			"relPhone": "0172617000",
			"relPhoneMobile": "0172617000",
			"relPhoneWork": "0172617000",
			"relFax": null,
			"relEmail": "595@mms.nl",
			"relEmailVerifyCode": null,
			"relEmailWork": null,
			"relDateBirth": 717929,
			"relCreatedDate": null,
			"relHandicap": 25.1,
			"relGolferId": null,
			"relMemberCode": "142",
			"relMemberType": "a",
			"relExternalId": null,
			"relDebtorId": null,
			"relMaxCredit": null,
			"relValue1": null,
			"relValue2": null,
			"relValue3": null,
			"relGrfNr": null,
			"relGrfNrDiscount": null,
			"relRelNrCompany": null,
			"relDftContactYN": null,
			"relRelNrDebtor": null,
			"relRelNrMail": null,
			"relLngNr": null,
			"relEmpNr": null,
			"relRbrNr": null,
			"relEndBlockDate": null,
			"relGrpNr1": 136,
			"relGrpNr2": 2580,
			"relGrpNr3": 2581,
			"relDontCheckYN": null,
			"relPliNr": null,
			"relSupplierYN": null,
			"relImage": "https:\/\/s3-eu-west-1.amazonaws.com\/intogolf.nl\/new-avatar\/IoXwfFyPPg545W5LGDmhfy67ksQNCncor9e7e10C.jpeg",
			"relNotes": null,
			"relActualYN": 1,
			"relHandicapYN": null,
			"relCoursePermissionYN": null,
			"relBankAccount": null,
			"relChargeBankYN": null,
			"relRttNr": null,
			"relCallName": "Dirk",
			"relVatId": null,
			"relLastChanged": null,
			"relIBAN": null,
			"relBIC": null,
			"relGolfPermitDate": null,
			"relCoursePermDate": null,
			"relRulesExam": null,
			"relHomeclub": null,
			"full_name": "D Slof",
			"age": 55
		},
		"greenfee": {
			"grfNr": 1,
			"grfComNr": 9,
			"grfOrdering": 10,
			"grfName": "Standaard Greenfee",
			"grfDateFrom": 737790,
			"grfDateTo": 738155,
			"grfIntroYN": 0,
			"grfDiscountYN": 0,
			"grfCardYN": 0,
			"grfDiscountCardYN": 0,
			"grfRounds": 0,
			"grfRoundType": 1,
			"grfCardPrice": 0,
			"grfCardDuration": 12,
			"grfCardDurationType": 0,
			"grfActualYN": 0,
			"grfWildcardYN": 0,
			"grfFreePriceYN": 1,
			"grfCardDiscount": 0,
			"grfMlaNrCard": null,
			"grfMaxPerPeriod": 0,
			"grfMaxPeriod": 0
		},
		"sales": [],
		"rateList": {
			"1": {
				"grfNr": 7,
				"grfName": "Greenfeee",
				"grfCard": 0,
				"grfDiscountCard": 0,
				"grfDiscount": 0,
				"price": 0.02,
				"itmNr": 212,
				"gfcNr": null
			}
		},
		"discountList": [{
			"grfNr": 0,
			"grfName": "...",
			"grfCard": 0,
			"grfDiscountCard": 0,
			"grfDiscount": 1,
			"price": 0,
			"itmNr": 0
		}],
		"isSelected": false,
		"flpFullName": "D Slof (Dirk)"
	}]
}
{
	"fltNr": 9451976,
	"fltComNr": 9,
	"fltRefType": null,
	"fltRefNr": null,
	"fltDate": 738084,
	"fltTime1Booked": 580,
	"fltTimeFixedYN": null,
	"fltTime1": 580,
	"fltBreakTime": null,
	"fltTime2": 720,
	"fltCrlNr1": 44,
	"fltCrlNr2": 0,
	"fltAloneYN": null,
	"fltHole": null,
	"fltOrigin": null,
	"fltSize": 3,
	"fltCheckedIn": 0,
	"fltTimestamp": "2020-08-20 15:51:37",
	"fltCarNr": null,
	"fltNotes": null,
	"fltDownPaymentPerc": null,
	"fltPaid": null,
	"fltAgtNr": null,
	"fltHotNr": null,
	"fltOptionDate": null,
	"fltCcard1": null,
	"fltCcard2": null,
	"fltCcardExpMonth": null,
	"fltCcardExpYear": null,
	"fltCcardCode": null,
	"fltCcard3": null,
	"fltCcard4": null,
	"fltResNr": null,
	"fltCost": null,
	"fltStatus": null,
	"fltHosNrLocked": 94,
	"fltCheckResult": null,
	"fltHosNr": 94,
	"fltEmpNr": 24,
	"flight_players": [{
		"flpNr": 29803563,
		"flpFltNr": 9451976,
		"flpMatch": null,
		"flpSide": 1,
		"flpRelNr": 309,
		"flpRelNrWildcard": null,
		"flpName": "Slof",
		"flpGrpNr1": null,
		"flpGrpNr2": null,
		"flpGrpNr3": null,
		"flpExtra": null,
		"flpPhone": "0172617000",
		"flpEmail": "595@mms.nl",
		"flpHandicap": 25.1,
		"flpGrfNr": 7,
		"flpGfcNr": null,
		"flpItmNr": 212,
		"flpPrice": 25,
		"flpBilNr": null,
		"flpGrfNrDiscount": null,
		"flpGfcNrDiscount": null,
		"flpItmNrDiscount": null,
		"flpDiscount": null,
		"flpSalNrDiscount": null,
		"flpVoucher": null,
		"flpScorecard": null,
		"flpQualifying": null,
		"flpCarNr": null,
		"flpIntro": null,
		"flpTeebox": null,
		"flpCheckResult": null,
		"flpRelNrMain": null,
		"flpEmailMarkNY": 1,
		"relation": {
			"relNr": 309,
			"relRelNrMain": null,
			"relComNr": 9,
			"relType": null,
			"relName": "Slof",
			"relGender": 1,
			"relFirstName": "D",
			"relPrefix": "",
			"relRtlNr": 1,
			"relAddress1": "Stik van Linschoten 22",
			"relAddress2": "Stik van Linschoten 22",
			"relCity": "Bergdorp",
			"relPostalCode": "2411 PZ",
			"relState": null,
			"relCouNr": 1,
			"relPhone": "0172617000",
			"relPhoneMobile": "0172617000",
			"relPhoneWork": "0172617000",
			"relFax": null,
			"relEmail": "595@mms.nl",
			"relEmailVerifyCode": null,
			"relEmailWork": null,
			"relDateBirth": 717929,
			"relCreatedDate": null,
			"relHandicap": 25.1,
			"relGolferId": null,
			"relMemberCode": "142",
			"relMemberType": "a",
			"relExternalId": null,
			"relDebtorId": null,
			"relMaxCredit": null,
			"relValue1": null,
			"relValue2": null,
			"relValue3": null,
			"relGrfNr": null,
			"relGrfNrDiscount": null,
			"relRelNrCompany": null,
			"relDftContactYN": null,
			"relRelNrDebtor": null,
			"relRelNrMail": null,
			"relLngNr": null,
			"relEmpNr": null,
			"relRbrNr": null,
			"relEndBlockDate": null,
			"relGrpNr1": 136,
			"relGrpNr2": 2580,
			"relGrpNr3": 2581,
			"relDontCheckYN": null,
			"relPliNr": null,
			"relSupplierYN": null,
			"relImage": "https:\/\/s3-eu-west-1.amazonaws.com\/intogolf.nl\/new-avatar\/IoXwfFyPPg545W5LGDmhfy67ksQNCncor9e7e10C.jpeg",
			"relNotes": null,
			"relActualYN": 1,
			"relHandicapYN": null,
			"relCoursePermissionYN": null,
			"relBankAccount": null,
			"relChargeBankYN": null,
			"relRttNr": null,
			"relCallName": "Dirk",
			"relVatId": null,
			"relLastChanged": null,
			"relIBAN": null,
			"relBIC": null,
			"relGolfPermitDate": null,
			"relCoursePermDate": null,
			"relRulesExam": null,
			"relHomeclub": null,
			"full_name": "D Slof",
			"age": 55
		},
		"greenfee": {
			"grfNr": 1,
			"grfComNr": 9,
			"grfOrdering": 10,
			"grfName": "Standaard Greenfee",
			"grfDateFrom": 737790,
			"grfDateTo": 738155,
			"grfIntroYN": 0,
			"grfDiscountYN": 0,
			"grfCardYN": 0,
			"grfDiscountCardYN": 0,
			"grfRounds": 0,
			"grfRoundType": 1,
			"grfCardPrice": 0,
			"grfCardDuration": 12,
			"grfCardDurationType": 0,
			"grfActualYN": 0,
			"grfWildcardYN": 0,
			"grfFreePriceYN": 1,
			"grfCardDiscount": 0,
			"grfMlaNrCard": null,
			"grfMaxPerPeriod": 0,
			"grfMaxPeriod": 0
		},
		"sales": [],
		"rateList": {
			"1": {
				"grfNr": 7,
				"grfName": "Greenfeee",
				"grfCard": 0,
				"grfDiscountCard": 0,
				"grfDiscount": 0,
				"price": 0.02,
				"itmNr": 212,
				"gfcNr": null
			}
		},
		"discountList": [{
			"grfNr": 0,
			"grfName": "...",
			"grfCard": 0,
			"grfDiscountCard": 0,
			"grfDiscount": 1,
			"price": 0,
			"itmNr": 0
		}],
		"isSelected": false,
		"flpFullName": "D Slof (Dirk)"
	}]
}

下面我们优化,只输出前台用到的字段

php
$result = Flight::query()
    ->select('fltNr', 'fltDate', 'fltTime1', 'fltTime2', 'fltCrlNr1', 'fltCrlNr2')
    ->with(['flightPlayers:flpNr,flpFltNr,flpBilNr,flpRelNr,flpGrfNr,flpScorecard',
        'flightPlayers.greenfee:grfNr,grfName,grfCardPrice,grfRoundType,grfOrdering,grfDateFrom,grfDateTo',
        'flightPlayers.relation:relNr,relName,relImage,relRtlNr,relMemberCode,relCity,relPhone,relGolferId,relAddress1,relDateBirth,relEmail,relHandicap,relFirstName,relCallName,relGender,relGrpNr1,relLastChanged',
        'flightPlayers.sales:salNr,salTrnNr,salBilNr,salItmNr,salVat,salBilNr,salAmount,salTrnNr',
        'flightPlayers.sales.item:itmNr,itmName,itmPrice,itmIsgNr,itmSellYN',
        'flightPlayers.sales.salesTransaction:trnNr,trnTimestamp'
    ])
    ->where(['fltNr' => $id])
    ->first()
$result = Flight::query()
    ->select('fltNr', 'fltDate', 'fltTime1', 'fltTime2', 'fltCrlNr1', 'fltCrlNr2')
    ->with(['flightPlayers:flpNr,flpFltNr,flpBilNr,flpRelNr,flpGrfNr,flpScorecard',
        'flightPlayers.greenfee:grfNr,grfName,grfCardPrice,grfRoundType,grfOrdering,grfDateFrom,grfDateTo',
        'flightPlayers.relation:relNr,relName,relImage,relRtlNr,relMemberCode,relCity,relPhone,relGolferId,relAddress1,relDateBirth,relEmail,relHandicap,relFirstName,relCallName,relGender,relGrpNr1,relLastChanged',
        'flightPlayers.sales:salNr,salTrnNr,salBilNr,salItmNr,salVat,salBilNr,salAmount,salTrnNr',
        'flightPlayers.sales.item:itmNr,itmName,itmPrice,itmIsgNr,itmSellYN',
        'flightPlayers.sales.salesTransaction:trnNr,trnTimestamp'
    ])
    ->where(['fltNr' => $id])
    ->first()

需要注意,我们需要指定主键名称。 如 Flight 表和 flightPlayers 表是一对多关联,flpNr字段是flightPlayers表的主键,大概格式就是

->with[
'关联表1:关联表主键1,字段1,字段2'
'多级关联表1.多级关联表2:关联表主键2,字段1,字段2'
]
->with[
'关联表1:关联表主键1,字段1,字段2'
'多级关联表1.多级关联表2:关联表主键2,字段1,字段2'
]