How to write a nested join query using distinct method and concat two columns into one in laravel query builder

huangapple go评论64阅读模式
英文:

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 &lt;= CURRENT_TIMESTAMP;

I have written the above query in laravel controller like the following

$positions = DB::table(&#39;positions&#39;)
        -&gt;join(&#39;elections&#39;, &#39;positions.eid&#39;, &#39;=&#39;, &#39;elections.eid&#39;)
        -&gt;join(&#39;candidates&#39;, &#39;positions.pid&#39;, &#39;=&#39;, &#39;candidates.pid&#39;)
        -&gt;join(&#39;erole2&#39;, &#39;candidates.uname&#39;, &#39;=&#39;, &#39;erole2.clogin&#39;)
        -&gt;select(&#39;positions.pid&#39;, &#39;positions.eid&#39;, &#39;positions.name as position_name&#39;, &#39;elections.name as election_name&#39;, &#39;candidates.uname&#39;, &#39;firstname&#39;, &#39;surname&#39;)
        -&gt;where(&#39;elections.complaints_deadline&#39;, &#39;&lt;=&#39;, &#39;CURRENT_TIMESTAMP&#39;)
        -&gt;distinct()-&gt;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

 -&gt;select(&#39;positions.pid&#39;, &#39;positions.eid&#39;, &#39;positions.name as position_name&#39;, &#39;elections.name as election_name&#39;, &#39;candidates.uname&#39;, &#39;&quot;CONCAT(&#39;firstname&#39;,&#39;surname&#39;) AS fullname&quot;&#39;)

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(&#39;positions&#39;)
    -&gt;join(&#39;elections&#39;, &#39;positions.eid&#39;, &#39;=&#39;, &#39;elections.eid&#39;)
    -&gt;join(&#39;candidates&#39;, &#39;positions.pid&#39;, &#39;=&#39;, &#39;candidates.pid&#39;)
    -&gt;join(&#39;erole2&#39;, &#39;candidates.uname&#39;, &#39;=&#39;, &#39;erole2.clogin&#39;)
    -&gt;select(&#39;positions.pid&#39;, &#39;positions.eid&#39;, &#39;positions.name as position_name&#39;, &#39;elections.name as election_name&#39;, &#39;candidates.uname&#39;, DB::raw(&#39;CONCAT(firstname, &quot; &quot;, surname) AS fullname&#39;))
    -&gt;where(&#39;elections.complaints_deadline&#39;, &#39;&lt;=&#39;, DB::raw(&#39;CURRENT_TIMESTAMP&#39;))
    -&gt;distinct()-&gt;get();

huangapple
  • 本文由 发表于 2023年7月10日 19:44:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/76653400.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定