Why don't you try "Query Builder"

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

Transcript

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

   View Slide

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

   View Slide

3. View Slide

4. 2VFSZ#VJMEFS
   

   View Slide

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

   View Slide

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

   View Slide

7. 4BNQMF%BUBCBTF
   

   View Slide

8. View Slide

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

   View Slide

10. 8IBU`T
    2VFSZ#VJMEFS
    

    View Slide

11. 1JD DPOTUSVDUJPO
    
    

    View Slide

12. 1JD DPOTUSVDUJPO
    
    

    View Slide

13. "UPPMUPCVJME42-
    

    View Slide

14. View Slide

15. SELECT name, price

    FROM books

    WHERE name LIKE '%PHP%'

    ORDER BY price DESC

    LIMIT 10
    

    View Slide

16. SELECT name, price

    FROM books

    WHERE name LIKE '%PHP%'

    ORDER BY price DESC

    LIMIT 10
    

    View Slide

17. &BDIQBSUPG42-
    IBTB30-&
    

    View Slide

18. $builder->table('books')

    ->where('name', 'like', '%PHP%')

    ->orderBy('name', 'desc')

    ->take(10)
    ->select('name', 'price');
    

    View Slide

19. SELECT `name`, `price`

    FROM `books`

    WHERE `name` LIKE '%PHP%'

    ORDER BY `price` DESC

    LIMIT 10
    

    View Slide

20. DELETE
    FROM `books`

    WHERE `name` LIKE '%PHP%'

    ORDER BY `price` DESC

    LIMIT 10
    

    View Slide

21. $builder->table('books')

    ->where('name', 'like', '%PHP%')

    ->orderBy('price', 'desc')

    ->take(10)
    ->select('name', 'price');
    

    View Slide

22. $builder->table('books')

    ->where('name', 'like', '%PHP%')

    ->orderBy('price', 'desc')

    ->take(10)
    ->delete();
    

    View Slide

23. $builder->table('books')

    ->where('name', 'like', '%PHP%')

    ->orderBy('price', 'desc')

    ->take(10)
    ->select('name', 'price');
    

    View Slide

24. $builder->table('books')

    ->where('name', 'like', '%PHP%')

    ->orderBy('price', 'desc')

    ->take(10)
    ->select('name', ‘price')
    ->get();
    

    View Slide

25. #FOFpUT
    

    View Slide

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

    View Slide

27. &BTZUPFEJU
    

    View Slide

28. %FNP
    

    View Slide

29. 4FDVSF
    

    View Slide

30. $name = 'PHPΤϯδχΞཆ੒ಡຊ';
    

    $sql = "

    SELECT `name`, `price` 

    FROM `books` 

    WHERE `name` = $name

    ORDER BY `price` DESC
    ";

    

    $sth = $pdo->prepare($sql);

    $sth->execute();
    

    View Slide

31. View Slide

32. View Slide

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();
    

    View Slide

34. View Slide

35. $name = "t' OR 't' = 't";

    

    $sql = <<SELECT `name`, `price`

    FROM `books`

    WHERE `name` = ?

    ORDER BY `price` DESC

    SQL;

    

    $sth = $pdo->prepare($sql);

    $sth->execute([$name]);
    

    View Slide

36. View Slide

37. $name = "t' OR 't' = 't";
    

    $builder->table('books')

    ->select('name', 'price')

    ->where('name', '=', $name)

    ->orderBy('name', 'desc');
    

    View Slide

38. &BTZUPSFBE
    

    View Slide

39. SELECT `name`, `price` 

    FROM `books` 

    WHERE `name` LIKE ‘%PHP%'
    AND `price` > 2000

    ORDER BY `price` DESC 

    LIMIT 10
    

    View Slide

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();
    

    View Slide

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]);
    

    View Slide

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);
    

    View Slide

43. View Slide

44. 3FVTBCMF
    

    View Slide

45. SELECT `name`, `price` 

    FROM `books` 

    WHERE `price` < 2000
    

    View Slide

46. $cheap_books =

    $builder->table('books')

    ->where('price', '<', 2000);
    

    View Slide

47. $cheap_books->orderBy('name');

    $cheap_books->orderBy('price');

    $cheap_books->orderBy('price', 'desc');

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

    View Slide

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

    View Slide

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

    View Slide

50. $cheap_books->count();

    $cheap_books->max('price');

    $cheap_books->min('price');

    $cheap_books->average('price');

    $cheap_books->sum('price');
    

    View Slide

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

    View Slide

52. )PXUPBQQMZ
    2VFSZ#VJMEFS
    UPZPVSPXOBQQT
    

    View Slide

53. {

    "require": {

    "illuminate/database": "5.2.*"

    }

    }
    

    View Slide

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

    View Slide

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();
    

    View Slide

56. 4PNFNPSFFYBNQMFT
    

    View Slide

57. +0*/
    

    View Slide

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` >= ?
    

    View Slide

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'

    );
    

    View Slide

60. */4&35
    

    View Slide

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);
    

    View Slide

62. INSERT INTO `books`(`name`, `author`, `price`) VALUES(?, ?, ?)

    .

    .

    .

    VS
    

    INSERT INTO `books`(`name`, `author`, `price`)
    VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?)......
    

    View Slide

63. NZTRMJ T
    1%0 T
    2VFSZ#VJMEFS NT
    

    View Slide

64. 61%"5&3"8
    

    View Slide

65. UPDATE `books`

    SET `name` = CONCAT('ʲ͍҆Αʂʳ', `name`)

    WHERE `price` < 3000
    

    View Slide

66. $cheap_books->update([

    'name' =>"CONCAT('ʲ͍҆Αʂʳ' , `name`)"

    ]);
    

    View Slide

67. View Slide

68. $cheap_books->update([

    'name' => $builder->raw(
    "CONCAT('ʲ͍҆Αʂʳ' , `name`)"
    )

    ]);
    

    View Slide

69. View Slide

70. "OZ2VFTUJPOT
    

    View Slide