如何将不同表中的其他列值获取到查询中?

本文介绍了如何将不同表中的其他列值获取到查询中?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有搜索应用程序，查找已按某些标准(类别、经验年限等)过滤的个人信息

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')); }