Why don't you try "Query Builder"

4584c227ef4ea69e789e5f53e0476418?s=47 omoon
May 21, 2016

Why don't you try "Query Builder"

PHPカンファレンス福岡2016のトークで使ったスライドです
関連レポジトリ)https://github.com/omoon/phpconfuk2016

4584c227ef4ea69e789e5f53e0476418?s=128

omoon

May 21, 2016
Tweet

Transcript

  1. 8IZEPO`UZPVUSZ 2VFSZ#VJMEFS 1)1$POGFSFODF'VLVPLB .BZ  4PUBSP0.63"

  2. "CPVU.F w4PUBSP0.63" w0TBLB +BQBO w,BOTBJ1)16TFST(SPVQ4UB⒎ w1)1   w-BSBWBM wPNPPOPSH

    w!PNPPO
  3. None
  4. 2VFSZ#VJMEFS

  5. 2VFTUJPOT w%PZPVLOPX42-  w.Z42-1PTUHSF42-0SBDMFPUIFST  w1%0 w,OPX2VFSZ#VJMEFS w6TF2VFSZ#VJMEFS w6TF03.

  6. /PUF w1%0 w*MMVNJOBUF%BUBCBTF w.Z42-

  7. 4BNQMF%BUBCBTF

  8. None
  9. 5PQJDT w8IBUJTl2VFSZ#VJMEFSz  w#FOFpUT w)PXUPBQQMZ2VFSZ#VJMEFSUPZPVSPXOBQQ w4PNFFYBNQMFT

  10. 8IBU`T 2VFSZ#VJMEFS

  11. 1JD DPOTUSVDUJPO

  12. 1JD DPOTUSVDUJPO

  13. "UPPMUPCVJME42-

  14. None
  15. SELECT name, price
 FROM books
 WHERE name LIKE '%PHP%'
 ORDER

    BY price DESC
 LIMIT 10
  16. SELECT name, price
 FROM books
 WHERE name LIKE '%PHP%'
 ORDER

    BY price DESC
 LIMIT 10
  17. &BDIQBSUPG42- IBTB30-&

  18. $builder->table('books')
 ->where('name', 'like', '%PHP%')
 ->orderBy('name', 'desc')
 ->take(10) ->select('name', 'price');

  19. SELECT `name`, `price`
 FROM `books`
 WHERE `name` LIKE '%PHP%'
 ORDER

    BY `price` DESC
 LIMIT 10
  20. DELETE FROM `books`
 WHERE `name` LIKE '%PHP%'
 ORDER BY `price`

    DESC
 LIMIT 10
  21. $builder->table('books')
 ->where('name', 'like', '%PHP%')
 ->orderBy('price', 'desc')
 ->take(10) ->select('name', 'price');

  22. $builder->table('books')
 ->where('name', 'like', '%PHP%')
 ->orderBy('price', 'desc')
 ->take(10) ->delete();

  23. $builder->table('books')
 ->where('name', 'like', '%PHP%')
 ->orderBy('price', 'desc')
 ->take(10) ->select('name', 'price');

  24. $builder->table('books')
 ->where('name', 'like', '%PHP%')
 ->orderBy('price', 'desc')
 ->take(10) ->select('name', ‘price') ->get();

  25. #FOFpUT

  26. #FOFpUT w&BTZUPFEJU w4FDVSF w&BTZUPSFBE w3FVTBCMF

  27. &BTZUPFEJU

  28. %FNP

  29. 4FDVSF

  30. $name = 'PHPΤϯδχΞཆ੒ಡຊ'; 
 $sql = "
 SELECT `name`, `price`

    
 FROM `books` 
 WHERE `name` = $name
 ORDER BY `price` DESC ";
 
 $sth = $pdo->prepare($sql);
 $sth->execute();
  31. None
  32. None
  33. $name = "t' OR 't' = 't";
 
 $sql =

    <<<'SQL'
 SELECT `name`, `price`
 FROM `books`
 WHERE `name` = $name
 ORDER BY `price` DESC
 SQL;
 
 $sth = $pdo->prepare($sql);
 $sth->execute();
  34. None
  35. $name = "t' OR 't' = 't";
 
 $sql =

    <<<SQL
 SELECT `name`, `price`
 FROM `books`
 WHERE `name` = ?
 ORDER BY `price` DESC
 SQL;
 
 $sth = $pdo->prepare($sql);
 $sth->execute([$name]);
  36. None
  37. $name = "t' OR 't' = 't"; 
 $builder->table('books')
 ->select('name',

    'price')
 ->where('name', '=', $name)
 ->orderBy('name', 'desc');
  38. &BTZUPSFBE

  39. SELECT `name`, `price` 
 FROM `books` 
 WHERE `name` LIKE

    ‘%PHP%' AND `price` > 2000
 ORDER BY `price` DESC 
 LIMIT 10
  40. $sql = "
 SELECT `name`, `price` 
 FROM `books` 


    WHERE `name` LIKE :name
 AND `price` > :price 
 ORDER BY `price` DESC 
 LIMIT :limit 
 ";
 
 $sth = $pdo->prepare($sql);
 $sth->bindParam(':name', $name);
 $sth->bindParam(':price', $price);
 $sth->bindParam(':limit', $limit, PDO::PARAM_INT);
 $sth->execute();
  41. $name = '%PHP%';
 $price = 2000;
 $limit = 10; 


    $sql = "SELECT `name`, `price` 
 FROM `books` 
 WHERE `name` LIKE ? AND `price` > ?
 ORDER BY `price` DESC 
 LIMIT ?";
 
 $sth = $pdo->prepare($sql);
 $sth->execute([$name, $price, $limit]);
  42. $name = '%PHP%';
 $price = 2000; $limit = 10;
 


    $builder->table('books')
 ->select('name', 'price')
 ->where('name', 'like', $name)
 ->where('price', '>', $price)
 ->orderBy('name', 'desc')
 ->take($limit);
  43. None
  44. 3FVTBCMF

  45. SELECT `name`, `price` 
 FROM `books` 
 WHERE `price` <

    2000
  46. $cheap_books =
 $builder->table('books')
 ->where('price', '<', 2000);

  47. $cheap_books->orderBy('name');
 $cheap_books->orderBy('price');
 $cheap_books->orderBy('price', 'desc');
 $cheap_books->orderBy('price', ‘desc') ->take(10);

  48. $cheap_books->orderBy('price', ‘desc') ->take(10) ->select();

  49. $cheap_books->orderBy('price', ‘desc') ->take(10) ->delete();

  50. $cheap_books->count();
 $cheap_books->max('price');
 $cheap_books->min('price');
 $cheap_books->average('price');
 $cheap_books->sum('price');

  51. #FOFpUT w&BTZUPFEJU w4FDVSF w&BTZUPSFBE w3FVTBCMF

  52. )PXUPBQQMZ 2VFSZ#VJMEFS UPZPVSPXOBQQT

  53. {
 "require": {
 "illuminate/database": "5.2.*"
 }
 }

  54. require_once '/path/to/vendor/autoload.php';

  55. use Illuminate\Database\Capsule\Manager as Builder; class Connection
 {
 private $host =

    'localhost';
 private $username = 'user';
 private $password = 'pass';
 private $database = 'database';
 public function getBuilder()
 {
 $builder = new Builder();
 $builder->addConnection([
 'driver' => 'mysql',
 'host' => $this->host,
 'database' => $this->database,
 'username' => $this->username,
 'password' => $this->password,
 'charset' => 'utf8',
 'collation' => 'utf8_unicode_ci',
 'prefix' => '',
 ]);
 $builder->setAsGlobal();
 $builder->setFetchMode(PDO::FETCH_ASSOC);
 return $builder->connection();
 }
 } $builder = (new Connection())->getBuilder();
  56. 4PNFNPSFFYBNQMFT

  57. +0*/

  58. SELECT
 `books`.`name` AS `book_name`,
 `books`.`price`,
 `authors`.`name` AS `author_name`,
 `authors`.`age`
 FROM

    `books` LEFT JOIN `authors`
 ON `books`.`author_id` = `authors`.`id`
 WHERE `books`.`price` >= ?
  59. $results = $builder->table('books')
 ->leftJoin('authors', 'books.author_id', '=', 'authors.id')
 ->where('books.price', '>=', 5000)


    ->select(
 'books.name as book_name',
 'books.price',
 'authors.name as author_name',
 'authors.age'
 );
  60. */4&35

  61. $values = [];
 for ($i = 0; $i < 10000;

    $i++) {
 $values[] = [
 'name' => 'name' . $i,
 'author_id' => rand(1, 10),
 'price' => rand(500, 10000),
 ];
 }
 
 $sql = "INSERT INTO books (name, author_id, price) values (?, ?, ?)”; 
 $sth = $pdo->prepare($sql);
 foreach ($values as $value) {
 $sth->execute(array_values($value));
 }
 
 $builder->table('books')->insert($values);
  62. INSERT INTO `books`(`name`, `author`, `price`) VALUES(?, ?, ?)
 .
 .


    .
 VS 
 INSERT INTO `books`(`name`, `author`, `price`) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?)......
  63. NZTRMJ T 1%0 T 2VFSZ#VJMEFS NT

  64. 61%"5&3"8

  65. UPDATE `books`
 SET `name` = CONCAT('ʲ͍҆Αʂʳ', `name`)
 WHERE `price` <

    3000
  66. $cheap_books->update([
 'name' =>"CONCAT('ʲ͍҆Αʂʳ' , `name`)"
 ]);

  67. None
  68. $cheap_books->update([
 'name' => $builder->raw( "CONCAT('ʲ͍҆Αʂʳ' , `name`)" )
 ]);

  69. None
  70. "OZ2VFTUJPOT