Database > Query Builder

Query Builder vs Eloquent ORM

종종 헷갈리는 것이 Query Builder 와 Eloquent ORM 이라는 용어이다.

• Query Builder : DB를 이용하여 직접적으로 query를 생성한다.
• Eloquent ORM: 모델을 정의하여 관계도를 만들어 사용한다(One To One, One To many....)

Query Builder 에서는 join등을 이용하여 연관데이타를 가져오지만 Eloquent ORM 에서는 Model에서 설정된 관계도를 이용하여 데이타를 호출하는 것이 가장 큰 차이점이다.

Query Log 사용하기

• query를 화면에 디스플레이 할때 사용

use DB;
DB::enableQueryLog();
// 다양한 query
$querylog = DB::getQueryLog();
var_dump($querylog);

dd(DB::getQueryLog());

SELECT

->select('id', 'name', 'region')
->select('reples.*', 'profiles.thumbnail_image', 'users.username')
DB::select('select * from users where id = :id', ['id' => 1]);

$sql = "( 6371 * acos ( cos( radians( ? ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( ? ) ) + sin( radians( ? ) ) * sin( radians( latitude ) ) ) ) AS distance";
$query = DB::table('profiles')
  ->select(DB::raw($sql))
  ->setBindings([ $lat, $lng, $lat ])
  ->having('distance', '<', $radius);

SELECT RAW

DB::raw를 사용하면 좀더 다양한 결과를 가져올 수 있다.

->select(DB::raw("count(id) as total_game"))
->select(DB::raw('SUM(amount) as totla'))
->select(DB::raw("sum(case when (result='S' and writer='id') or (result='F' and challenger='id2') then 1 else 0 end)) as win")
->select(DB::raw("(select count(item) from item where item = '1111' and status not like '%U%' and id = '233')  as cnt"))
DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = :somevariable"), array(
  'somevariable' => $someVariable,
));

->select(DB::raw("
  (select GROUP_CONCAT(themes.name separator ', ') from corporations_themes
  left join themes
  on themes.id =  corporations_themes.sector_id
  where corporations_themes.code = corporations.code )
  as thems"))

DB::raw 대신에 selectRaw 도 사용 가능하다.

$orders = DB::table('orders')
  ->selectRaw('price * ? as price_with_tax', [1.0825])
  ->get();

addSelect

• 이미 쿼리 빌더 인스턴스를 가지고 있고 존재하는 select 절에 선택할 컬럼을 추가하려면, addSelect 메소드를 사용할 수 있습니다.

->addSelect('name')

Table Alias

$results = DB::table('rooms as r')
MyModel::from('games as g' )->orderBy('g.id', 'desc'); <!-- deleted_at 이 null 이 아닐경우 문제 발생
->withTrashed() and ->whereNull('table_alias.deleted_at')   이경우 이렇게 query를 변경해 주어야 한다.

$withdrawals = Withdrawal::from( 'withdrawals as w' )
->withTrashed()
->whereNull('w.deleted_at')"

Join

Left Join

DB::table('rooms')
->leftJoin('bookings', function($join)
{
  $join->on('rooms.id', '=', 'bookings.room_type_id');
  $join->on('arrival','>=',DB::raw('2012-05-01'));
  $join->on('arrival','<=',DB::raw('2012-05-10'));
  $join->on('games.ended', '=', \DB::raw('Y'));
  // $join->on('games.ended', '=',Y'); not work
})

->leftJoin('users', function($join) use ($clientId){
  $join->on('users.id', '=', 'tb2.user_id');
  $join->where('users.client_id', '=', $clientId);
})

->leftjoin('users', function($join){
  $join->on('reples.user_id', '=', 'users.id');
}) 

->leftJoin('game as g', function($join){
  $join->on('b.no', '=','g.no');
  $join->on('b.date', '=', 'g.date');
})

DB::table('participants as p1')
  ->select('p1.thread_id as id', 'p1.user_id', 'p1.last_read', 'p1.created_at', 'p1.updated_at', 'p1.deleted_at')
  ->leftJoin('users as u', 'u.id', '=', 'p1.user_id')
  ->leftJoin('profiles as p', 'p.user_id', '=', 'p1.user_id')
  ->leftJoin(\DB::raw('(SELECT * FROM messages as m1 where m1.id = (select max(id) from messages as m2 where m1.thread_id = m2.thread_id)) as mt'), function($join){
      $join->on('mt.thread_id', '=', 'p1.thread_id');
    })
  ->orderBy('p1.created_at', 'desc')
  ->get();

findOrFail

Field::withTrashed()->findOrFail($field_id);

역순으로 데이타 가져오기

$messages  = $thread->getMessages()->reverse();

UPDATE

$this->memoRepo->get_model()->where('me_id', $row->me_id)->update(['me_flag' =>DB::raw("CONCAT_WS(',',me_flag,'SD')")]);
DB::table('subject_user')->where('user_id', $value)->update(['auth_teacher' => 1]);

$client = \DB::table('users')  -------  ->first();
$client->save(); // Not work
"$client = \App\Models\Users::  -------  ->first();
$client->save(); // work

DB::table('my_table')
->where('rowID', 1)
->update([
  'column1' => DB::raw('column1 + 2'),
  'column2' => DB::raw('column2 + 10'),
  'column3' => DB::raw('column3 + 13'),
  'column4' => DB::raw('column4 + 5'),
])

Carbon->update(['item_status'=>'U', 'used_date'=>Carbon::now()])

Delete

Model::where()->delete();

FIRST

->first();echo $user->name;

MAX

->max('thread');

MIN

->min('thread');

INCREMENT

• increment 는 update, save등과 사용할 수 없다.

->increment('wr_comment', 1, ['wr_last' => date("Y-m-d H:i:s")]);
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->increment('votes', 1, ['name' => 'John']);

DECREMENT

decrement 는 update, save등과 사용할 수 없다.

DB::table('users')->decrement('votes');

DISTINCT

->distinct()

GROUPBY

->groupBy('status'); 
->groupBy('first_name', 'status');
->groupBy(\DB::raw('HOUR(plays.created_at)'));

HAVING

->having('account_id', '>', 100)

SUM

->select('department', DB::raw('SUM(price) as total_sales'))

get() 및 first() 사용시 empty 에 대한 대응

$user = User::where('mobile', Input::get('mobile'))->first(); // model or null
if (!$user) {
  // Do stuff if it doesn't exist.
}

$user = User::where('mobile', Input::get('mobile'))->get();
if (!$user->isEmpty()) {
}

DB 관련

statement

use DB;

DB::statement("optimize table sessions");

$users = DB::connection('foo')->select(...);
$pdo = DB::connection()->getPdo();
$users = DB::select('select * from users where active = ?', [1]);
DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);
$affected = DB::update('update users set votes = 100 where name = ?', ['John']);
$deleted = DB::delete('delete from users');
DB::statement('drop table users');
DB::table('users')->update(['votes' => 1]);
DB::table('posts')->delete();

DB Transaction

DB::beginTransaction(); // transaction 시작
DB::rollBack(); // 롤백
DB::commit(); // 커및

DB::beginTransaction();
try {
  $user = User::create(['email' => $request->email, 'name' => $request->name]);
  DB::commit();
} catch (\Exception $e) {
  DB::rollback();
  Log::info($e->getMessage());
  return null;
}

실전예제들

최근 결과에 대한 내용을 가져오기

$asset = Asset::select()
->join(DB::raw("(SELECT MAX(id) AS max_id FROM assets GROUP BY assets.user_id, assets.c_currency) assets_max"), 'assets.id', '=', 'assets_max.max_id')
->get();

여러개의 주문상품을 주문아이디별로 그룹화하기

위와 같이 그룹으로 처리

$items = MarketOrder::select(
    'market_orders.o_id', 'market_orders.qty', 'market_orders.price', 
    'orders.count'
  )
  ->Join(DB::raw("(SELECT o_id, COUNT(o_id) as count, MAX(id) as max_id FROM market_orders GROUP BY market_orders.o_id) orders"), function($join)
  {
    $join->on('market_orders.id', 'orders.max_id');
  })

Sub Query

DB::select(DB::raw("SELECT min(yyyymmdd) as yyyymmdd FROM (
  SELECT yyyymmdd FROM trading_volume_sector GROUP BY yyyymmdd ORDER BY yyyymmdd DESC LIMIT 0, 3
) ast"));

$users = DB::table('users')
  ->where('agent', $contract->branch2)
  ->whereIn('id', function($query) {
    $query->select('user_id')
      ->from('users_roles')
      ->where('role_id', 8);
  })
  ->count();
  

Table Merge

$pointTbl = DB::table('users_points as p')->select('p.*', DB::raw("null as valid"), DB::raw("null as used"), DB::raw("null as flag"), DB::raw("'p' as tbl"));
$bonusTbl = DB::table('users_bonus as b')->select('b.*', DB::raw("'b' as tbl"));
$mergeTbl = $pointTbl->unionAll($bonusTbl);

$points = DB::table(DB::raw("({$mergeTbl->toSql()}) AS mg"))->select('mg.*')->mergeBindings($mergeTbl)->
orderBy('created_at', 'desc')
->where('mg.user_id', $user->id)
->where('mg.point', '>', 0);
$points = $points->simplePaginate($take)->appends(request()->query());

$purchasesTbl = DB::table('purchases as p')->select('p.user_id', 'p.trader_id', 'p.symbol', 'p.input_amount as amount', 'p.total', 'p.fee', 'p.status', 'p.created_at', DB::raw("'p' as tbl"));
$salesTbl = DB::table('sales as s')->select('s.user_id', 's.trader_id', 's.symbol', 's.amount', 's.price as total', 's.fee', 's.status', 's.created_at', DB::raw("'s' as tbl"));
$mergeTbl = $purchasesTbl->unionAll($salesTbl);
$settles = DB::table(DB::raw("({$mergeTbl->toSql()}) AS mg"))->select(DB::raw("CASE WHEN(mg.tbl = 's') THEN '판매' WHEN(mg.tbl = 'p') THEN '구매' ELSE '' END as tbl"), 'mg.symbol', 'mg.amount', 'mg.fee', 'mg.total', 'u.name', 'tuser.name as t_name', 'mg.created_at')->mergeBindings($mergeTbl)
->leftjoin('users as u', function($join){
  $join->on('mg.user_id', '=', 'u.id');
})
->leftjoin('users as tuser', function($join){
  $join->on('mg.trader_id', '=', 'tuser.id');
})
->orderBy('mg.created_at', 'desc');

$settles = $settles->whereIn('mg.status', ['P', 'D']);      
$settles = $settles->where('u.tester', '!=', 1 );

$settles = $settles->whereRaw("DATE(mg.created_at) >= '".$this->from_date."' AND DATE(mg.created_at)<= '".$this->to_date."'" );

Model 에서 Scopes 를 사용할 경우

• 이 부분에 대해서는 Model에서 상세히 다룰 예정입니다.

whereProviderId($value) = where(provider_id, $value)

Full Query

$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = '$someVariable'") );
$results =DB::select("SELECT * FROM some_table WHERE some_col = '$someVariable'"); // DB::raw 없이도 사용가능

Collection 사용하기

$result = collect($result)->pluck('code', 'inst_cd'); // ->toArray();

Pluck

결과값을 배열로 반환

$agents = \DB::table('contracts')->where('branch2', $user->agent)->pluck('branch3'); // ->toArray();

->whereIn('users.agent', $agents); // 반환된 배열값을 whereIn 조건절에 바로 사용할 수 있다.