在Laravel中使用eloquent创建查询

Question

Ok, so I'm needing to create a query below in eloquent or Laravel in some matter. I'm just not sure how I would create something like this:

select * from 
(select * from Bulk 
  where Login = 'moga' and ApptDate='2015-02-25' 
  order by FormatTime asc) as A
group by Name

This query will run just fine as a raw query in the database, I'm just not sure how I would pull this query off using query helper or eloquent.

Answer 1

You can use either Eloquent or Query Builder. Here's an example with the Query Builder:

DB::table(DB::raw('(select * from Bulk where Login = "moga" and ApptDate="2015-02-25" order by FormatTime asc) as A'))
    ->groupBy('Name')
    ->get();

With Eloquent you'd use ModelName::from() instead of DB::table(). But keeping in mind that DB::raw does not escape variable values inserted into the query string, you could build the nested query using the builder as well, to make sure you're not vulnerable to SQL injection:

$from = DB::table('Bulk')
    ->where('Login', $login)
    ->where('ApptDate', $date)
    ->orderBy('FormatTime')
    ->toSql();

DB::table(DB::raw('(' . $from  . ') A'))->groupBy('Name')->toSql();