Laravel7の覚書 | ワクガンス

DB: NULLの検索 | whereNull | whereNotNull

検索対象はNULLのみであり、空文字は対象外なので注意すること。

	$query = ¥DB::table('nekos')->whereNull ('neko_val');
	dump($query->toSql()); // → "select * from `nekos` where `neko_val` is null"
	
	$data = $query->get();
	dump($data);
	

whereNotNull

	$query = ¥DB::table('nekos')->whereNotNull ('neko_val');
	dump($query->toSql()); // → "select * from `nekos` where `neko_val` is not null"
	
	$data = $query->get();
	dump($data);
	

2020-6-29

DB: 日時の検索 whereDate / whereMonth / whereDay / whereYear / whereTime

whereDate

	$query = ¥DB::table('nekos')->whereDate ('neko_dt', '2014-04-28');
	dump($query->toSql()); // → "select * from `nekos` where date(`neko_dt`) = ?"
	
	$data = $query->get();
	dump($data);
	

出力

whereMonth

	$query = ¥DB::table('nekos')->whereMonth ('neko_dt', '4'); // 「04」表記も検索対象
	dump($query->toSql()); // → "select * from `nekos` where month(`neko_dt`) = ?"
	
	$data = $query->get();
	dump($data);
	

whereDay

	$query = ¥DB::table('nekos')->whereDay ('neko_dt', 28);
	dump($query->toSql()); // → "select * from `nekos` where day(`neko_dt`) = ?"
	
	$data = $query->get();
	dump($data);
	

whereYear

	$query = ¥DB::table('nekos')->whereYear ('neko_dt', 2014);
	dump($query->toSql()); // → "select * from `nekos` where year(`neko_dt`) = ?"
	
	$data = $query->get();
	dump($data);
	

whereTime

	$query = ¥DB::table('nekos')->whereTime ('neko_dt', '10:05:00'); // 秒を省略して時分（10:05)での検索も可能
	dump($query->toSql()); // → "select * from `nekos` where time(`neko_dt`) = ?"
	
	$data = $query->get();
	dump($data);
	

2020-6-29

DB: ２つのカラムが同値のレコードを検索 | whereColumn

	$query = ¥DB::table('nekos')->whereColumn ('created', 'modified');
	dump($query->toSql()); // →"select * from `nekos` where `created` = `modified`"
	
	$data = $query->get();
	dump($data);
	

出力

オペレータの指定もできる

	$query = ¥DB::table('nekos')->whereColumn ('created', '<', 'modified');
	dump($query->toSql()); // →"select * from `nekos` where `created` < `modified`"
	
	$data = $query->get();
	dump($data);
	

2020-6-29

複数のWHERE条件

	$query = ¥DB::table('nekos')->where ([
			['neko_name', 'kame'],
			['neko_val', 3],
	]);
	dump($query->toSql()); // →"select * from `nekos` where (`neko_name` = ? and `neko_val` = ?)"
	
	$data = $query->get();
	dump($data);
	

2020-6-30

DB: BETWEEN演算子 | whereBetween | whereNotBetween

whereBetween

	$query = ¥DB::table('nekos')->whereBetween ('neko_val', [2,4]);
	dump($query->toSql()); // →"select * from `nekos` where `neko_val` between ? and ?"
	
	$data = $query->get();
	dump($data);
	

whereNotBetween

	$query = ¥DB::table('nekos')->whereNotBetween ('neko_val', [2,4]);
	dump($query->toSql()); // →"select * from `nekos` where `neko_val` not between ? and ?"
	
	$data = $query->get();
	dump($data);
	

2020-6-29

DB: OR節WHERE

	$query = ¥DB::table('nekos')
		->where('neko_name', '=', 'ゴボウ')
		->orWhere('neko_val', '<', 4);
		dump($query->toSql()); // →"select * from `nekos` where `neko_name` = ? or `neko_val` < ?"
	
	$data = $query->get();
	dump($data);
	

2020-6-30

DB: リスト(配列）として取得

	$query = ¥DB::table('nekos')->where('neko_val', '<', 5);
	dump($query->toSql()); // →"select * from `nekos` where `neko_val` < ?"
	
	$list = $query->pluck('neko_name');
	dump($list);
	

出力


2020-6-29

DB: 単一の値を取得

	$query = ¥DB::table('nekos')->where('id', '=', 4);
	dump($query->toSql()); // →"select * from `nekos` where `id` = ?"
	
	$value = $query->value('neko_name');
	dump($value);
	

出力


2020-6-30

DB: 一行だけ取得 | first

	$query = ¥DB::table('nekos')->where('id', '=', 4);
	dump($query->toSql()); 
	
	$value = $query->first();
	dump($value);
	

出力


2020-6-30

DB: GROUP BY句 | groupBy

	$query = ¥DB::table('nekos')
		->select(¥DB::raw('count(id) as count, neko_group'))
		->groupBy('neko_group');
	dump($query->toSql()); // → "select count(id) as count, neko_group from `nekos` group by `neko_group`"
	
	$value = $query->get();
	dump($value);
	

2020-6-30

xxx

2020-6-29