英文:
How to write a nested join query using distinct method and concat two columns into one in laravel query builder
问题
以下是您要翻译的部分:
原始查询如下:
select distinct positions.pid, positions.eid, positions.name as position_name, elections.name as election_name, candidates.uname, firstname, surname
from positions
inner join elections on positions.eid = elections.eid
inner join candidates on positions.pid = candidates.pid
inner join erole2 on candidates.uname = erole2.clogin
where elections.complaints_deadline <= CURRENT_TIMESTAMP;
我已经在Laravel控制器中编写了上述查询,如下所示:
$positions = DB::table('positions')
->join('elections', 'positions.eid', '=', 'elections.eid')
->join('candidates', 'positions.pid', '=', 'candidates.pid')
->join('erole2', 'candidates.uname', '=', 'erole2.clogin')
->select('positions.pid', 'positions.eid', 'positions.name as position_name', 'elections.name as election_name', 'candidates.uname', 'firstname', 'surname')
->where('elections.complaints_deadline', '<=', DB::raw('CURRENT_TIMESTAMP'))
->distinct()->get();
但它没有返回相同的结果。原始查询返回12行,而我在控制器中编写的查询返回一个空集。我做错了什么?如何在Laravel中编写该查询?我使用的是Laravel 9.0。
其次,
我想将上述查询中的两列(firstname + surname作为fullname)连接成一个单一的列名。如何做到这一点?在上面的代码中,我尝试编写select子句如下所示:
->select('positions.pid', 'positions.eid', 'positions.name as position_name', 'elections.name as election_name', 'candidates.uname', DB::raw('CONCAT(firstname, surname) AS fullname'))
但我收到错误消息,说Concat不是有效的列名。
您能指导我纠正我的错误吗?
英文:
My original query is
select distinct positions.pid, positions.eid, positions.name as position_name, elections.name as election_name, candidates.uname, firstname, surname
from positions
inner join elections on positions.eid = elections.eid
inner join candidates on positions.pid = candidates.pid
inner join erole2 on candidates.uname = erole2.clogin
where elections.complaints_deadline <= CURRENT_TIMESTAMP;
I have written the above query in laravel controller like the following
$positions = DB::table('positions')
->join('elections', 'positions.eid', '=', 'elections.eid')
->join('candidates', 'positions.pid', '=', 'candidates.pid')
->join('erole2', 'candidates.uname', '=', 'erole2.clogin')
->select('positions.pid', 'positions.eid', 'positions.name as position_name', 'elections.name as election_name', 'candidates.uname', 'firstname', 'surname')
->where('elections.complaints_deadline', '<=', 'CURRENT_TIMESTAMP')
->distinct()->get();
But its not giving the same result. The original query returning 12 rows whereas the query I have written in my controller is returning an empty set. What i am doing wrong. How to write that query in laravel. I am using larvel 9.0
Secondly
I want to concatenate two columns from the above query ( firstname + surname as fullname) into one single column name. how to do that? In the above code I have tried writing the select clause like this
->select('positions.pid', 'positions.eid', 'positions.name as position_name', 'elections.name as election_name', 'candidates.uname', '"CONCAT('firstname','surname') AS fullname"')
But I am getting error saying Concat is not a valid column name.
Can you please guide me correct my wrong?
答案1
得分: 1
您正在将CURRENT_TIMESTAMP
用作字符串而不是MySQL函数。为此,您需要使用DB::raw()
函数。您还可以将DB::raw()
用于名称。
尝试这样做:
$positions = DB::table('positions')
->join('elections', 'positions.eid', '=', 'elections.eid')
->join('candidates', 'positions.pid', '=', 'candidates.pid')
->join('erole2', 'candidates.uname', '=', 'erole2.clogin')
->select('positions.pid', 'positions.eid', 'positions.name as position_name', 'elections.name as election_name', 'candidates.uname', DB::raw('CONCAT(firstname, " ", surname) AS fullname'))
->where('elections.complaints_deadline', '<=', DB::raw('CURRENT_TIMESTAMP'))
->distinct()
->get();
英文:
You're using CURRENT_TIMESTAMP
as a string not as a MySQL function. For that, you need to use DB::raw()
function. You can use DB::raw()
for the name as well.
Try this
$positions = DB::table('positions')
->join('elections', 'positions.eid', '=', 'elections.eid')
->join('candidates', 'positions.pid', '=', 'candidates.pid')
->join('erole2', 'candidates.uname', '=', 'erole2.clogin')
->select('positions.pid', 'positions.eid', 'positions.name as position_name', 'elections.name as election_name', 'candidates.uname', DB::raw('CONCAT(firstname, " ", surname) AS fullname'))
->where('elections.complaints_deadline', '<=', DB::raw('CURRENT_TIMESTAMP'))
->distinct()->get();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论