如何将不同表中的其他列值获取到查询中? [英] How to get other column value in different table into the query?
问题描述
我有搜索应用程序，查找已按某些标准(类别、经验年限等)过滤的个人信息
I had searching application, finding personal information which had been filtered by some criteria (category, years of experience etc)
最后一个过滤器negeri 的 tempoh perkhidmatan"有问题.我需要按州(negeri)计算工作经验的数量.例如，当在 state(negeri) 'x' 中搜索 5 年的人时，sql 将汇总所选州中每个人的经验年限.
I had problem with the last filter, 'tempoh perkhidmatan by negeri'. I need to calculate the number of working experience by state(negeri). For example, when searching for people of 5 years in the state(negeri) 'x', the sql will sum years of experience of each person in the state selected.
这是按条件进行 SQL 搜索的完整代码:
This is the full code of SQL searching by criteria:
$query = DB::table('itemregistrations')
->join('sections', 'itemregistrations.SectionID', '=', 'sections.SectionID')
->join('categories', 'itemregistrations.CategoryID', '=', 'categories.CategoryID')
->join('operasi', 'itemregistrations.OperasiID', '=', 'operasi.OperasiID')
->join('negeri', 'itemregistrations.NegeriID', '=', 'negeri.NegeriID')
->join('gred', 'itemregistrations.GredID', '=', 'gred.GredID')
->where('itemregistrations.statusProID', '=', 1)
->select('itemregistrations.name','sections.sectionname', 'categories.categoryname', 'operasi.operasiname', 'itemregistrations.Nobadan', 'itemregistrations.lahir_yy', 'itemregistrations.pdrm_yy', 'gred.namagred', 'itemregistrations.itemRegistrationID', '');
if($request->input('negeri_lahir') != ''){
$query->where('itemregistrations.NegeriID', $request->input('negeri_lahir'));
}
if($request->input('kategori') != '') {
$query->where('itemregistrations.CategoryID', $request->input('kategori'));
}
if($request->input('pangkat') != '') {
$query->where('itemregistrations.OperasiID', $request->input('pangkat'));
}
if(request('umur')) {
$query->whereRaw('YEAR(CURDATE()) - lahir_yy >= ?', [request('umur')]);
}
if($request->input('gred') != '') {
$query->where('itemregistrations.GredID', $request->input('gred'));
}
if(request('tempoh')) {
$query->whereRaw('YEAR(CURDATE()) - pdrm_yy >= ?', [request('tempoh')]);
}
if($request->input('negeri_perkhidmatan') != '') {
$query->join('itemregistrationpangkat', 'itemregistrationpangkat.itemRegistrationID', '=', 'itemregistrations.itemRegistrationID')
->where('itemregistrationpangkat.NegeriID', $request->input('negeri_perkhidmatan'));
}
if(request('tempoh_negeri')) {
$query->select(DB::raw('m.itemRegistrationID, sum(m.duration)'))
->from(DB::raw('(SELECT itemRegistrationID, NegeriID, yeartamatkhidmat - yearmulakhidmat as duration FROM itemregistrationpangkat) AS m
RIGHT JOIN itemregistrations ON itemregistrations.itemRegistrationID=m.itemRegistrationID'))
->distinct()
->groupBy('m.itemRegistrationID');
}
$newitem = $query->get();
return response::json($newitem);
需要解决的代码是这个(最后一个过滤器):
The code involve to be solve is this(the last filter):
if(request('tempoh_negeri')) {
$query->select(DB::raw('m.itemRegistrationID, m.NegeriID, sum(distinct m.duration)'))
->from(DB::raw('(SELECT itemRegistrationID, NegeriID, yeartamatkhidmat - yearmulakhidmat as duration FROM itemregistrationpangkat) AS m
RIGHT JOIN itemregistrations ON itemregistrations.itemRegistrationID=m.itemRegistrationID'))
->groupBy('m.itemRegistrationID', 'm.NegeriID');
}
问题是我需要从 $query 语句的 itemregistrations 表中获取 name 列、sectionID 列、CategoryID、OperasiID、NegeriID、GredID.如何将tempoh_negeri"中的最后一个查询过滤器与上一个查询过滤器结合起来?
The problem is I need to get name column, sectionID column, CategoryID, OperasiID, NegeriID, GredID, from itemregistrations table from the $query statement. How to combine the last query filter in 'tempoh_negeri' with the previous one?
推荐答案
我不是特别了解 Laravel，所以我很难理解你的查询是如何构建的，但这种语法似乎使人们能够编写通过添加块的请求，但不一定按正确的顺序.因此，对于 SQL 演讲者，我认为您的查询应该执行以下操作:
I didn't know about Laravel in particular, so I had much trouble trying to understand how your query was built, but this syntax seems to enable people to write a request by adding chunks, but not necessarily in the right order. So here's what I believe your query is supposed to do, for SQL speakers:
SELECT itemregistrations .name,
sections .sectionname,
categories .categoryname,
operasi .operasiname,
itemregistrations .Nobadan,
itemregistrations .lahir_yy,
itemregistrations .pdrm_yy,
gred .namagred,
itemregistrations .itemRegistrationID
-- if($tempoh_negeri) (request)
,m .itemRegistrationID,
sum(m.duration)
FROM itemregistrations
-- if($tempoh_negeri) (request)
,(SELECT DISTINCT itemRegistrationID,
NegeriID,
yeartamatkhidmat - yearmulakhidmat as duration
FROM itemregistrationpangkat) AS m
RIGHT JOIN itemregistrations
ON itemregistrations.itemRegistrationID = m.itemRegistrationID
JOIN sections
ON itemregistrations.SectionID = sections.SectionID
JOIN categories
ON itemregistrations.CategoryID = categories.CategoryID
JOIN operasi
ON itemregistrations.OperasiID = operasi.OperasiID
JOIN negeri
ON itemregistrations.NegeriID = negeri.NegeriID
JOIN gred
ON itemregistrations.GredID = gred.GredID
-- if($negeri_perkhidmatan)
JOIN itemregistrationpangkat
ON itemregistrationpangkat.itemRegistrationID = itemregistrations.itemRegistrationID
WHERE itemregistrations.statusProID = 1
-- if($negeri_lahir) (WHERE)
AND itemregistrations.NegeriID = $negeri_lahir
-- if($kategori) (WHERE)
AND itemregistrations.CategoryID = $kategori
-- if($pangkat) (WHERE)
AND itemregistrations.OperasiID = $pangkat
-- if(umur) (WHERERAW) (request)
AND YEAR(CURDATE()) - lahir_yy >= umur
-- if($gred) (WHERE)
AND itemregistrations.GredID = $gred
-- if($tempoh) (WHERERAW) (request)
AND YEAR(CURDATE()) - pdrm_yy >= tempoh
-- if($negeri_perkhidmatan)
AND itemregistrationpangkat.NegeriID = $negeri_perkhidmatan
-- if($tempoh_negeri) (request)
GROUP BY m.itemRegistrationID
如果是这样，您将无法按照这种方式执行您想要的操作(包括子查询中的主列)，因为您的子查询将在主查询之前进行评估.
If it's so, you cannot do what you want following that way (including main columns into the subquery) because your subquery will be evaluated BEFORE the main one is.
相反，您需要在主查询级别编写一个适当的过滤器，即:在其他JOIN...ON"子句中已经存在.这会给:
Instead, you need to write a proper filter at main query level, that is : among the others "JOIN…ON" clauses already in place. Which would give:
LEFT JOIN itemregistrationpangkat
ON itemregistrationpangkat.itemRegistrationID = itemregistrations.itemRegistrationID
... 然后直接在 sum() 函数中指定减法
… then specify the substraction directly in your sum() function
sum(yeartamatkhidmat - yearmulakhidma)
至于 Lavarel，这可能会给出如下结果:
As regards Lavarel, this probably would give something like:
if(request('tempoh_negeri')) {
$query->leftjoin('itemregistrationpangkat','itemRegistrationID','=','itemregistrations.itemRegistrationID');
$query->select(DB:raw('sum(yeartamatkhidmat - yearmulakhidmat'));
}
这篇关于如何将不同表中的其他列值获取到查询中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
