Upgrade to Pro — share decks privately, control downloads, hide ads and more …

The Database Abstraction Layer of Yii Framework 2.0

The Database Abstraction Layer of Yii Framework 2.0

In this talk I will introduce the database abstraction layer of Yii Framework 2.0.

I will explain the need for an abstraction layer and guide you through the class model of the implementation in Yii, where each layer builds on top of the other. Yii has three abstraction layers which are SQL Commands, QueryBuilder, and ActiveRecord.

I will show how the design of the implementation in Yii allows a lot of flexibility even on high abstraction levels. The ActiveRecord Layer even allows non-SQL database backends like Elasticsearch, MongoDB and Redis. Discussion of the ActiveRecord layer includes defining relations and reusing defined queries in different situations.

Presented on Yiiconf 2017 in Moscow.

Video is available on https://yiiconf.ru/ru/offers/offer/292

Carsten Brandt

June 16, 2017
Tweet

More Decks by Carsten Brandt

Other Decks in Programming

Transcript

  1. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    The Database Layer of Yii Framework 2.0
    Carsten Brandt
    June 16, 2017 - Yiiconf, Ìîñêâà
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  2. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Table of Contents
    1 Why do we need DB abstraction?
    2 Yii DB abstraction layers
    DAO
    Query Builder
    Active Record
    3 Switching between layers
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  3. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Why do we need DB abstraction?
    code simplication and readablity
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  4. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Why do we need DB abstraction?
    code simplication and readablity
    reusable code and MVC separation
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  5. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Why do we need DB abstraction?
    code simplication and readablity
    reusable code and MVC separation
    security enhancements
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  6. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    3 Layers
    DAO
    Database Access Objects
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  7. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    3 Layers
    DAO
    Database Access Objects
    Query Builder
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  8. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    3 Layers
    DAO
    Database Access Objects
    Query Builder
    Active Record
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  9. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    3 Layers
    DAO
    Database Access Objects
    Query Builder
    Active Record
    yii\db\Command
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  10. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    3 Layers
    DAO
    Database Access Objects
    Query Builder
    Active Record
    yii\db\Command
    yii\db\Query
    uses
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  11. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    3 Layers
    DAO
    Database Access Objects
    Query Builder
    Active Record
    yii\db\Command
    yii\db\Query
    yii\db\ActiveQuery
    uses
    extends
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  12. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db = new C o n n e c t i o n ( [
    ' dsn ' =
    > ' m y s q l : h o s t=l o c a l h o s t ; dbname=example ' ,
    ' username ' =
    > ' cebe ' ,
    ' p a s s w o r d ' =
    > ' 1 3 3 7 ' ,
    ] ) ;
    $db−>open ( ) ;
    $command = $db−>createCommand ( " SELECT ∗ FROM u s e r " ) ;
    $ u s e r = $command−>queryOne ( ) ;
    $ u s e r s = $command−>q u e r y A l l ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  13. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db = new C o n n e c t i o n ( [
    ' dsn ' =
    > ' m y s q l : h o s t=l o c a l h o s t ; dbname=example ' ,
    ' username ' =
    > ' cebe ' ,
    ' p a s s w o r d ' =
    > ' 1 3 3 7 ' ,
    ] ) ;
    $db−>open ( ) ;
    $command = $db−>createCommand ( " SELECT ∗ FROM u s e r " ) ;
    $ u s e r = $command−>queryOne ( ) ;
    $ u s e r s = $command−>q u e r y A l l ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  14. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db = new C o n n e c t i o n ( [
    ' dsn ' =
    > ' m y s q l : h o s t=l o c a l h o s t ; dbname=example ' ,
    ' username ' =
    > ' cebe ' ,
    ' p a s s w o r d ' =
    > ' 1 3 3 7 ' ,
    ] ) ;
    $db−>open ( ) ;
    $command = $db−>createCommand ( " SELECT ∗ FROM u s e r " ) ;
    $ u s e r = $command−>queryOne ( ) ;
    $ u s e r s = $command−>q u e r y A l l ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  15. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db = new C o n n e c t i o n ( [
    ' dsn ' =
    > ' m y s q l : h o s t=l o c a l h o s t ; dbname=example ' ,
    ' username ' =
    > ' cebe ' ,
    ' p a s s w o r d ' =
    > ' 1 3 3 7 ' ,
    ] ) ;
    $db−>open ( ) ;
    $command = $db−>createCommand ( " SELECT ∗ FROM u s e r " ) ;
    $ u s e r = $command−>queryOne ( ) ;
    $ u s e r s = $command−>q u e r y A l l ( ) ;
    yii\db\Connection PHP PDO
    uses
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  16. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db = new C o n n e c t i o n ( [
    ' dsn ' =
    > ' m y s q l : h o s t=l o c a l h o s t ; dbname=example ' ,
    ' username ' =
    > ' cebe ' ,
    ' p a s s w o r d ' =
    > ' 1 3 3 7 ' ,
    ] ) ;
    $db−>open ( ) ;
    $command = $db−>createCommand ( " SELECT ∗ FROM u s e r " ) ;
    $ u s e r = $command−>queryOne ( ) ;
    $ u s e r s = $command−>q u e r y A l l ( ) ;
    yii\db\Connection PHP PDO
    uses
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  17. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db = new C o n n e c t i o n ( [
    ' dsn ' =
    > ' m y s q l : h o s t=l o c a l h o s t ; dbname=example ' ,
    ' username ' =
    > ' cebe ' ,
    ' p a s s w o r d ' =
    > ' 1 3 3 7 ' ,
    ] ) ;
    $db−>open ( ) ;
    $command = $db−>createCommand ( " SELECT ∗ FROM u s e r " ) ;
    $ u s e r = $command−>queryOne ( ) ;
    $ u s e r s = $command−>q u e r y A l l ( ) ;
    yii\db\Connection PHP PDO
    uses
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  18. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db = new C o n n e c t i o n ( [
    ' dsn ' =
    > ' m y s q l : h o s t=l o c a l h o s t ; dbname=example ' ,
    ' username ' =
    > ' cebe ' ,
    ' p a s s w o r d ' =
    > ' 1 3 3 7 ' ,
    ] ) ;
    $db−>open ( ) ;
    $command = $db−>createCommand ( " SELECT ∗ FROM u s e r " ) ;
    $ u s e r = $command−>queryOne ( ) ;
    $ u s e r s = $command−>q u e r y A l l ( ) ;
    yii\db\Connection PHP PDO
    uses
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  19. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db = new C o n n e c t i o n ( [
    ' dsn ' =
    > ' m y s q l : h o s t=l o c a l h o s t ; dbname=example ' ,
    ' username ' =
    > ' cebe ' ,
    ' p a s s w o r d ' =
    > ' 1 3 3 7 ' ,
    ] ) ;
    $db−>open ( ) ;
    $command = $db−>createCommand ( " SELECT ∗ FROM u s e r " ) ;
    $ u s e r = $command−>queryOne ( ) ;
    $ u s e r s = $command−>q u e r y A l l ( ) ;
    yii\db\Connection PHP PDO
    uses
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  20. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db−>createCommand()−> i n s e r t ( ' u s e r s ' , [
    ' name ' =
    > ' CeBe ' ,
    ' e m a i l ' =
    > ' m a i l @ c e b e . cc ' ,
    ' c r e a t e d _ a t ' =
    > t i m e ( ) ,
    ' updated_at ' =
    > t i m e ( ) ,
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> u p d a t e (
    ' u s e r s ' , // t a b l e
    [ ' name ' =
    > ' C a r s t e n ' ] , // SET
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> d e l e t e (
    ' u s e r s ' , // t a b l e
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  21. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db−>createCommand()−> i n s e r t ( ' u s e r s ' , [
    ' name ' =
    > ' CeBe ' ,
    ' e m a i l ' =
    > ' m a i l @ c e b e . cc ' ,
    ' c r e a t e d _ a t ' =
    > t i m e ( ) ,
    ' updated_at ' =
    > t i m e ( ) ,
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> u p d a t e (
    ' u s e r s ' , // t a b l e
    [ ' name ' =
    > ' C a r s t e n ' ] , // SET
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> d e l e t e (
    ' u s e r s ' , // t a b l e
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  22. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db−>createCommand()−> i n s e r t ( ' u s e r s ' , [
    ' name ' =
    > ' CeBe ' ,
    ' e m a i l ' =
    > ' m a i l @ c e b e . cc ' ,
    ' c r e a t e d _ a t ' =
    > t i m e ( ) ,
    ' updated_at ' =
    > t i m e ( ) ,
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> u p d a t e (
    ' u s e r s ' , // t a b l e
    [ ' name ' =
    > ' C a r s t e n ' ] , // SET
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> d e l e t e (
    ' u s e r s ' , // t a b l e
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  23. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db−>createCommand()−> i n s e r t ( ' u s e r s ' , [
    ' name ' =
    > ' CeBe ' ,
    ' e m a i l ' =
    > ' m a i l @ c e b e . cc ' ,
    ' c r e a t e d _ a t ' =
    > t i m e ( ) ,
    ' updated_at ' =
    > t i m e ( ) ,
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> u p d a t e (
    ' u s e r s ' , // t a b l e
    [ ' name ' =
    > ' C a r s t e n ' ] , // SET
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> d e l e t e (
    ' u s e r s ' , // t a b l e
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  24. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db−>createCommand()−> i n s e r t ( ' u s e r s ' , [
    ' name ' =
    > ' CeBe ' ,
    ' e m a i l ' =
    > ' m a i l @ c e b e . cc ' ,
    ' c r e a t e d _ a t ' =
    > t i m e ( ) ,
    ' updated_at ' =
    > t i m e ( ) ,
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> u p d a t e (
    ' u s e r s ' , // t a b l e
    [ ' name ' =
    > ' C a r s t e n ' ] , // SET
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> d e l e t e (
    ' u s e r s ' , // t a b l e
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  25. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db−>createCommand()−> i n s e r t ( ' u s e r s ' , [
    ' name ' =
    > ' CeBe ' ,
    ' e m a i l ' =
    > ' m a i l @ c e b e . cc ' ,
    ' c r e a t e d _ a t ' =
    > t i m e ( ) ,
    ' updated_at ' =
    > t i m e ( ) ,
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> u p d a t e (
    ' u s e r s ' , // t a b l e
    [ ' name ' =
    > ' C a r s t e n ' ] , // SET
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> d e l e t e (
    ' u s e r s ' , // t a b l e
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  26. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db−>createCommand()−> i n s e r t ( ' u s e r s ' , [
    ' name ' =
    > ' CeBe ' ,
    ' e m a i l ' =
    > ' m a i l @ c e b e . cc ' ,
    ' c r e a t e d _ a t ' =
    > t i m e ( ) ,
    ' updated_at ' =
    > t i m e ( ) ,
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> u p d a t e (
    ' u s e r s ' , // t a b l e
    [ ' name ' =
    > ' C a r s t e n ' ] , // SET
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> d e l e t e (
    ' u s e r s ' , // t a b l e
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  27. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db−>createCommand()−> i n s e r t ( ' u s e r s ' , [
    ' name ' =
    > ' CeBe ' ,
    ' e m a i l ' =
    > ' m a i l @ c e b e . cc ' ,
    ' c r e a t e d _ a t ' =
    > t i m e ( ) ,
    ' updated_at ' =
    > t i m e ( ) ,
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> u p d a t e (
    ' u s e r s ' , // t a b l e
    [ ' name ' =
    > ' C a r s t e n ' ] , // SET
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> d e l e t e (
    ' u s e r s ' , // t a b l e
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  28. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db−>createCommand()−> i n s e r t ( ' u s e r s ' , [
    ' name ' =
    > ' CeBe ' ,
    ' e m a i l ' =
    > ' m a i l @ c e b e . cc ' ,
    ' c r e a t e d _ a t ' =
    > t i m e ( ) ,
    ' updated_at ' =
    > t i m e ( ) ,
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> u p d a t e (
    ' u s e r s ' , // t a b l e
    [ ' name ' =
    > ' C a r s t e n ' ] , // SET
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> d e l e t e (
    ' u s e r s ' , // t a b l e
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  29. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db−>createCommand()−> i n s e r t ( ' u s e r s ' , [
    ' name ' =
    > ' CeBe ' ,
    ' e m a i l ' =
    > ' m a i l @ c e b e . cc ' ,
    ' c r e a t e d _ a t ' =
    > t i m e ( ) ,
    ' updated_at ' =
    > t i m e ( ) ,
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> u p d a t e (
    ' u s e r s ' , // t a b l e
    [ ' name ' =
    > ' C a r s t e n ' ] , // SET
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> d e l e t e (
    ' u s e r s ' , // t a b l e
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  30. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Command Usage
    $db−>createCommand()−> i n s e r t ( ' u s e r s ' , [
    ' name ' =
    > ' CeBe ' ,
    ' e m a i l ' =
    > ' m a i l @ c e b e . cc ' ,
    ' c r e a t e d _ a t ' =
    > t i m e ( ) ,
    ' updated_at ' =
    > t i m e ( ) ,
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> u p d a t e (
    ' u s e r s ' , // t a b l e
    [ ' name ' =
    > ' C a r s t e n ' ] , // SET
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    $db−>createCommand()−> d e l e t e (
    ' u s e r s ' , // t a b l e
    "name = ' C a r s t e n ' " // WHERE
    ])−> e x e c u t e ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  31. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Other DAO features
    Transactions
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  32. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Other DAO features
    Transactions
    Read/Write splitting for Master-Slave setup
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  33. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Other DAO features
    Transactions
    Read/Write splitting for Master-Slave setup
    Connection Failover
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  34. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Other DAO features
    Transactions
    Read/Write splitting for Master-Slave setup
    Connection Failover
    Yii features: Events,
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  35. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Other DAO features
    Transactions
    Read/Write splitting for Master-Slave setup
    Connection Failover
    Yii features: Events, Conguration,
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  36. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Other DAO features
    Transactions
    Read/Write splitting for Master-Slave setup
    Connection Failover
    Yii features: Events, Conguration, DI
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  37. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder
    $ q u e r y = ( new y i i \ db \ Query )
    −> s e l e c t ( [ ' name ' , ' e m a i l ' ] )
    −>from ( ' u s e r s ' )
    −>wh er e ( [ ' i d ' =
    > 4 2 ] ) ;
    $ u s e r = $ q u e r y −>one ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  38. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder
    SELECT name , email FROM users WHERE id = 42
    $ q u e r y = ( new y i i \ db \ Query )
    −> s e l e c t ( [ ' name ' , ' e m a i l ' ] )
    −>from ( ' u s e r s ' )
    −>wh er e ( [ ' i d ' =
    > 4 2 ] ) ;
    $ u s e r = $ q u e r y −>one ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  39. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder
    SELECT name , email FROM users WHERE id = 42
    $ q u e r y = ( new y i i \ db \ Query )
    −> s e l e c t ( [ ' name ' , ' e m a i l ' ] )
    −>from ( ' u s e r s ' )
    −>wh er e ( [ ' i d ' =
    > 4 2 ] ) ;
    $ u s e r = $ q u e r y −>one ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  40. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder
    SELECT name , email FROM users WHERE id = 42
    $ q u e r y = ( new y i i \ db \ Query )
    −> s e l e c t ( [ ' name ' , ' e m a i l ' ] )
    −>from ( ' u s e r s ' )
    −>wh er e ( [ ' i d ' =
    > $_GET [ ' i d ' ] ] ) ;
    $ u s e r = $ q u e r y −>one ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  41. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder
    SELECT name , email FROM users WHERE id = 42
    $ q u e r y = ( new y i i \ db \ Query )
    −> s e l e c t ( [ ' name ' , ' e m a i l ' ] )
    −>from ( ' u s e r s ' )
    −>wh er e ( ' i d = : i d ' , [ ' : i d ' =
    > $_GET [ ' i d ' ] ] ) ;
    $ u s e r = $ q u e r y −>one ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  42. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder
    SELECT name , email FROM users WHERE id = 42
    $ q u e r y = ( new y i i \ db \ Query )
    −> s e l e c t ( [ ' name ' , ' e m a i l ' ] )
    −>from ( ' u s e r s ' )
    −>wh er e ( ' i d = : i d ' , [ ' : i d ' =
    > $_GET [ ' i d ' ] ] ) ;
    $ u s e r = $ q u e r y −>one ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  43. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder - Subqueries
    $ a c t i v e Q u e r y = ( new Query )
    −> s e l e c t ( ' i d ' )
    −>from ( ' c a t e g o r i e s ' )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] ) ;
    $ q u e r y = ( new Query )
    −>from ( ' a r t i c l e ' )
    −>wh er e ( [ ' c a t e g o r y _ i d ' =
    > $ a c t i v e Q u e r y ] ) ;
    $ a r t i c l e s I n A c t i v e C a t e g o r i e s = $ q u e r y −> a l l ( ) ;
    SELECT ∗
    FROM a r t i c l e
    WHERE category_id IN (
    SELECT id FROM c a t e g o r i e s WHERE a c t i v e=1
    ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  44. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder - Subqueries
    $ a c t i v e Q u e r y = ( new Query )
    −> s e l e c t ( ' i d ' )
    −>from ( ' c a t e g o r i e s ' )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] ) ;
    $ q u e r y = ( new Query )
    −>from ( ' a r t i c l e ' )
    −>wh er e ( [ ' c a t e g o r y _ i d ' =
    > $ a c t i v e Q u e r y ] ) ;
    $ a r t i c l e s I n A c t i v e C a t e g o r i e s = $ q u e r y −> a l l ( ) ;
    SELECT ∗
    FROM a r t i c l e
    WHERE category_id IN (
    SELECT id FROM c a t e g o r i e s WHERE a c t i v e=1
    ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  45. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder - Subqueries
    $ a c t i v e Q u e r y = ( new Query )
    −> s e l e c t ( ' i d ' )
    −>from ( ' c a t e g o r i e s ' )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] ) ;
    $ q u e r y = ( new Query )
    −>from ( ' a r t i c l e ' )
    −>wh er e ( [ ' c a t e g o r y _ i d ' =
    > $ a c t i v e Q u e r y ] ) ;
    $ a r t i c l e s I n A c t i v e C a t e g o r i e s = $ q u e r y −> a l l ( ) ;
    SELECT ∗
    FROM a r t i c l e
    WHERE category_id IN (
    SELECT id FROM c a t e g o r i e s WHERE a c t i v e=1
    ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  46. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder - Subqueries
    $ a c t i v e Q u e r y = ( new Query )
    −> s e l e c t ( ' i d ' )
    −>from ( ' c a t e g o r i e s ' )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] ) ;
    $ q u e r y = ( new Query )
    −>from ( ' a r t i c l e ' )
    −>wh er e ( [ ' c a t e g o r y _ i d ' =
    > $ a c t i v e Q u e r y ] ) ;
    $ a r t i c l e s I n A c t i v e C a t e g o r i e s = $ q u e r y −> a l l ( ) ;
    SELECT ∗
    FROM a r t i c l e
    WHERE category_id IN (
    SELECT id FROM c a t e g o r i e s WHERE a c t i v e=1
    ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  47. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder - Subqueries
    $ a c t i v e Q u e r y = ( new Query )
    −> s e l e c t ( ' i d ' )
    −>from ( ' c a t e g o r i e s ' )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] ) ;
    $ q u e r y = ( new Query )
    −>from ( ' a r t i c l e ' )
    −>wh er e ( [ ' c a t e g o r y _ i d ' =
    > $ a c t i v e Q u e r y ] ) ;
    $ a r t i c l e s I n A c t i v e C a t e g o r i e s = $ q u e r y −> a l l ( ) ;
    select()
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  48. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder - Subqueries
    $ a c t i v e Q u e r y = ( new Query )
    −> s e l e c t ( ' i d ' )
    −>from ( ' c a t e g o r i e s ' )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] ) ;
    $ q u e r y = ( new Query )
    −>from ( ' a r t i c l e ' )
    −>wh er e ( [ ' c a t e g o r y _ i d ' =
    > $ a c t i v e Q u e r y ] ) ;
    $ a r t i c l e s I n A c t i v e C a t e g o r i e s = $ q u e r y −> a l l ( ) ;
    select()
    from()
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  49. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder - Subqueries
    $ a c t i v e Q u e r y = ( new Query )
    −> s e l e c t ( ' i d ' )
    −>from ( ' c a t e g o r i e s ' )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] ) ;
    $ q u e r y = ( new Query )
    −>from ( ' a r t i c l e ' )
    −>wh er e ( [ ' c a t e g o r y _ i d ' =
    > $ a c t i v e Q u e r y ] ) ;
    $ a r t i c l e s I n A c t i v e C a t e g o r i e s = $ q u e r y −> a l l ( ) ;
    select()
    from()
    join()
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  50. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder - Subqueries
    $ a c t i v e Q u e r y = ( new Query )
    −> s e l e c t ( ' i d ' )
    −>from ( ' c a t e g o r i e s ' )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] ) ;
    $ q u e r y = ( new Query )
    −>from ( ' a r t i c l e ' )
    −>wh er e ( [ ' c a t e g o r y _ i d ' =
    > $ a c t i v e Q u e r y ] ) ;
    $ a r t i c l e s I n A c t i v e C a t e g o r i e s = $ q u e r y −> a l l ( ) ;
    select()
    from()
    join()
    where()
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  51. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder - Subqueries
    $ a c t i v e Q u e r y = ( new Query )
    −> s e l e c t ( ' i d ' )
    −>from ( ' c a t e g o r i e s ' )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] ) ;
    $ q u e r y = ( new Query )
    −>from ( ' a r t i c l e ' )
    −>wh er e ( [ ' c a t e g o r y _ i d ' =
    > $ a c t i v e Q u e r y ] ) ;
    $ a r t i c l e s I n A c t i v e C a t e g o r i e s = $ q u e r y −> a l l ( ) ;
    select()
    from()
    join()
    where()
    union()
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  52. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder
    SQL as string, vs. real code
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  53. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder
    SQL as string, vs. real code
    Easier to create queries
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  54. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder
    SQL as string, vs. real code
    Easier to create queries
    More secure
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  55. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Query Builder
    SQL as string, vs. real code
    Easier to create queries
    More secure
    Separation of code
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  56. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    c l a s s U s e r e x t e n d s y i i \ db \ A c t i v e R e c o r d
    {
    p u b l i c s t a t i c f u n c t i o n tableName ( )
    {
    r e t u r n ' u s e r s ' ;
    }
    }
    users
    id name
    1 alice
    2 bob
    3 . . .
    .
    .
    .
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  57. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    c l a s s U s e r e x t e n d s y i i \ db \ A c t i v e R e c o r d
    {
    p u b l i c s t a t i c f u n c t i o n tableName ( )
    {
    r e t u r n ' u s e r s ' ;
    }
    }
    users
    id name
    1 alice
    2 bob
    3 . . .
    .
    .
    .
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  58. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    yii\db\ActiveRecord
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  59. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    yii\db\ActiveRecord yii\db\ActiveQuery
    nd()
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  60. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    yii\db\ActiveRecord yii\db\ActiveQuery
    nd()
    yii\db\Query
    extends
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  61. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    yii\db\ActiveRecord yii\db\ActiveQuery
    nd()
    yii\db\Query
    extends
    User
    extends
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  62. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    yii\db\ActiveRecord yii\db\ActiveQuery
    nd()
    yii\db\Query
    extends
    User
    extends
    UserQuery
    extends
    nd()
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  63. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    yii\db\ActiveRecord yii\db\ActiveQuery
    nd()
    yii\db\Query
    extends
    User
    extends
    UserQuery
    extends
    nd()
    yii\base\Model
    extends
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  64. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    $ u s e r = new U s e r ( ) ;
    $ u s e r −>a t t r i b u t e s = $_POST [ ' User ' ] ;
    i f ( $ u s e r −>s a v e ( ) ) {
    e c h o " s u c c e s s ! " ;
    } e l s e {
    e c h o " i n v a l i d d a t a : " . p r i n t _ r ( $ u s e r −>g e t E r r o r s ( ) ) ;
    }
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  65. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    $ u s e r = new U s e r ( ) ;
    $ u s e r −>a t t r i b u t e s = $_POST [ ' User ' ] ;
    i f ( $ u s e r −>s a v e ( ) ) {
    e c h o " s u c c e s s ! " ;
    } e l s e {
    e c h o " i n v a l i d d a t a : " . p r i n t _ r ( $ u s e r −>g e t E r r o r s ( ) ) ;
    }
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  66. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    $ u s e r = new U s e r ( ) ;
    $ u s e r −>a t t r i b u t e s = $_POST [ ' User ' ] ;
    i f ( $ u s e r −>s a v e ( ) ) {
    e c h o " s u c c e s s ! " ;
    } e l s e {
    e c h o " i n v a l i d d a t a : " . p r i n t _ r ( $ u s e r −>g e t E r r o r s ( ) ) ;
    }
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  67. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    $ u s e r = new U s e r ( ) ;
    $ u s e r −>a t t r i b u t e s = $_POST [ ' User ' ] ;
    i f ( $ u s e r −>s a v e ( ) ) {
    e c h o " s u c c e s s ! " ;
    } e l s e {
    e c h o " i n v a l i d d a t a : " . p r i n t _ r ( $ u s e r −>g e t E r r o r s ( ) ) ;
    }
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  68. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    $ u s e r = new U s e r ( ) ;
    $ u s e r −>a t t r i b u t e s = $_POST [ ' User ' ] ;
    i f ( $ u s e r −>s a v e ( ) ) {
    e c h o " s u c c e s s ! " ;
    } e l s e {
    e c h o " i n v a l i d d a t a : " . p r i n t _ r ( $ u s e r −>g e t E r r o r s ( ) ) ;
    }
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  69. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    $ a c t i v e U s e r s = U s e r : : f i n d ( )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] )
    −>o r d e r B y ( ' name ' )
    −> a l l ( ) ;
    $ u s e r = U s e r : : f i n d ()−> w he r e ( [ ' i d ' =
    > 1])−> one ( ) ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  70. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    $ a c t i v e U s e r s = U s e r : : f i n d ( )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] )
    −>o r d e r B y ( ' name ' )
    −> a l l ( ) ;
    $ u s e r = U s e r : : f i n d ()−> w he r e ( [ ' i d ' =
    > 1])−> one ( ) ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  71. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    $ a c t i v e U s e r s = U s e r : : f i n d ( )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] )
    −>o r d e r B y ( ' name ' )
    −> a l l ( ) ;
    $ u s e r = U s e r : : f i n d ()−> w he r e ( [ ' i d ' =
    > 1])−> one ( ) ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  72. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    $ a c t i v e U s e r s = U s e r : : f i n d ( )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] )
    −>o r d e r B y ( ' name ' )
    −> a l l ( ) ;
    $ u s e r = U s e r : : f i n d ()−> w he r e ( [ ' i d ' =
    > 1])−> one ( ) ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  73. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    $ a c t i v e U s e r s = U s e r : : f i n d ( )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] )
    −>o r d e r B y ( ' name ' )
    −> a l l ( ) ;
    $ u s e r = U s e r : : f i n d ()−> w he r e ( [ ' i d ' =
    > 1])−> one ( ) ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  74. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record
    $ a c t i v e U s e r s = U s e r : : f i n d ( )
    −>wh er e ( [ ' a c t i v e ' =
    > 1 ] )
    −>o r d e r B y ( ' name ' )
    −> a l l ( ) ;
    $ u s e r = U s e r : : f i n d ()−> w he r e ( [ ' i d ' =
    > 1])−> one ( ) ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  75. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    c l a s s U s e r e x t e n d s y i i \ db \ A c t i v e R e c o r d {
    // . . .
    p u b l i c f u n c t i o n g e t P r o f i l e ( ) {
    r e t u r n $ t h i s −>hasOne ( P r o f i l e : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    p u b l i c f u n c t i o n g e t P o s t s ( ) {
    r e t u r n $ t h i s −>hasMany ( P o s t : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    }
    // SELECT ∗ FROM u s e r s WHERE i d = 1 ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    // SELECT ∗ FROM p r o f i l e WHERE u s e r _ i d = 1 ;
    $ p r o f i l e = $ u s e r −> p r o f i l e ; // P r o f i l e
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ;
    $ p o s t s = $ u s e r −>p o s t s ; // P o s t [ ]
    $ p o s t Q u e r y = $ u s e r −>g e t P o s t s ()−> o r d e r B y ( ' c r e a t e d _ a t ' ) ;
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ORDER BY c r e a t e d _ a t ;
    $ p o s t s = $ p o s t Q u e r y −> a l l ( ) ; // P o s t [ ]
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  76. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    c l a s s U s e r e x t e n d s y i i \ db \ A c t i v e R e c o r d {
    // . . .
    p u b l i c f u n c t i o n g e t P r o f i l e ( ) {
    r e t u r n $ t h i s −>hasOne ( P r o f i l e : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    p u b l i c f u n c t i o n g e t P o s t s ( ) {
    r e t u r n $ t h i s −>hasMany ( P o s t : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    }
    // SELECT ∗ FROM u s e r s WHERE i d = 1 ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    // SELECT ∗ FROM p r o f i l e WHERE u s e r _ i d = 1 ;
    $ p r o f i l e = $ u s e r −> p r o f i l e ; // P r o f i l e
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ;
    $ p o s t s = $ u s e r −>p o s t s ; // P o s t [ ]
    $ p o s t Q u e r y = $ u s e r −>g e t P o s t s ()−> o r d e r B y ( ' c r e a t e d _ a t ' ) ;
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ORDER BY c r e a t e d _ a t ;
    $ p o s t s = $ p o s t Q u e r y −> a l l ( ) ; // P o s t [ ]
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  77. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    c l a s s U s e r e x t e n d s y i i \ db \ A c t i v e R e c o r d {
    // . . .
    p u b l i c f u n c t i o n g e t P r o f i l e ( ) {
    r e t u r n $ t h i s −>hasOne ( P r o f i l e : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    p u b l i c f u n c t i o n g e t P o s t s ( ) {
    r e t u r n $ t h i s −>hasMany ( P o s t : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    }
    // SELECT ∗ FROM u s e r s WHERE i d = 1 ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    // SELECT ∗ FROM p r o f i l e WHERE u s e r _ i d = 1 ;
    $ p r o f i l e = $ u s e r −> p r o f i l e ; // P r o f i l e
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ;
    $ p o s t s = $ u s e r −>p o s t s ; // P o s t [ ]
    $ p o s t Q u e r y = $ u s e r −>g e t P o s t s ()−> o r d e r B y ( ' c r e a t e d _ a t ' ) ;
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ORDER BY c r e a t e d _ a t ;
    $ p o s t s = $ p o s t Q u e r y −> a l l ( ) ; // P o s t [ ]
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  78. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    c l a s s U s e r e x t e n d s y i i \ db \ A c t i v e R e c o r d {
    // . . .
    p u b l i c f u n c t i o n g e t P r o f i l e ( ) {
    r e t u r n $ t h i s −>hasOne ( P r o f i l e : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    p u b l i c f u n c t i o n g e t P o s t s ( ) {
    r e t u r n $ t h i s −>hasMany ( P o s t : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    }
    // SELECT ∗ FROM u s e r s WHERE i d = 1 ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    // SELECT ∗ FROM p r o f i l e WHERE u s e r _ i d = 1 ;
    $ p r o f i l e = $ u s e r −> p r o f i l e ; // P r o f i l e
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ;
    $ p o s t s = $ u s e r −>p o s t s ; // P o s t [ ]
    $ p o s t Q u e r y = $ u s e r −>g e t P o s t s ()−> o r d e r B y ( ' c r e a t e d _ a t ' ) ;
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ORDER BY c r e a t e d _ a t ;
    $ p o s t s = $ p o s t Q u e r y −> a l l ( ) ; // P o s t [ ]
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  79. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    c l a s s U s e r e x t e n d s y i i \ db \ A c t i v e R e c o r d {
    // . . .
    p u b l i c f u n c t i o n g e t P r o f i l e ( ) {
    r e t u r n $ t h i s −>hasOne ( P r o f i l e : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    p u b l i c f u n c t i o n g e t P o s t s ( ) {
    r e t u r n $ t h i s −>hasMany ( P o s t : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    }
    // SELECT ∗ FROM u s e r s WHERE i d = 1 ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    // SELECT ∗ FROM p r o f i l e WHERE u s e r _ i d = 1 ;
    $ p r o f i l e = $ u s e r −> p r o f i l e ; // P r o f i l e
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ;
    $ p o s t s = $ u s e r −>p o s t s ; // P o s t [ ]
    $ p o s t Q u e r y = $ u s e r −>g e t P o s t s ()−> o r d e r B y ( ' c r e a t e d _ a t ' ) ;
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ORDER BY c r e a t e d _ a t ;
    $ p o s t s = $ p o s t Q u e r y −> a l l ( ) ; // P o s t [ ]
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  80. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    c l a s s U s e r e x t e n d s y i i \ db \ A c t i v e R e c o r d {
    // . . .
    p u b l i c f u n c t i o n g e t P r o f i l e ( ) {
    r e t u r n $ t h i s −>hasOne ( P r o f i l e : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    p u b l i c f u n c t i o n g e t P o s t s ( ) {
    r e t u r n $ t h i s −>hasMany ( P o s t : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    }
    // SELECT ∗ FROM u s e r s WHERE i d = 1 ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    // SELECT ∗ FROM p r o f i l e WHERE u s e r _ i d = 1 ;
    $ p r o f i l e = $ u s e r −> p r o f i l e ; // P r o f i l e
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ;
    $ p o s t s = $ u s e r −>p o s t s ; // P o s t [ ]
    $ p o s t Q u e r y = $ u s e r −>g e t P o s t s ()−> o r d e r B y ( ' c r e a t e d _ a t ' ) ;
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ORDER BY c r e a t e d _ a t ;
    $ p o s t s = $ p o s t Q u e r y −> a l l ( ) ; // P o s t [ ]
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  81. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    c l a s s U s e r e x t e n d s y i i \ db \ A c t i v e R e c o r d {
    // . . .
    p u b l i c f u n c t i o n g e t P r o f i l e ( ) {
    r e t u r n $ t h i s −>hasOne ( P r o f i l e : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    p u b l i c f u n c t i o n g e t P o s t s ( ) {
    r e t u r n $ t h i s −>hasMany ( P o s t : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    }
    // SELECT ∗ FROM u s e r s WHERE i d = 1 ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    // SELECT ∗ FROM p r o f i l e WHERE u s e r _ i d = 1 ;
    $ p r o f i l e = $ u s e r −> p r o f i l e ; // P r o f i l e
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ;
    $ p o s t s = $ u s e r −>p o s t s ; // P o s t [ ]
    $ p o s t Q u e r y = $ u s e r −>g e t P o s t s ()−> o r d e r B y ( ' c r e a t e d _ a t ' ) ;
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ORDER BY c r e a t e d _ a t ;
    $ p o s t s = $ p o s t Q u e r y −> a l l ( ) ; // P o s t [ ]
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  82. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    c l a s s U s e r e x t e n d s y i i \ db \ A c t i v e R e c o r d {
    // . . .
    p u b l i c f u n c t i o n g e t P r o f i l e ( ) {
    r e t u r n $ t h i s −>hasOne ( P r o f i l e : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    p u b l i c f u n c t i o n g e t P o s t s ( ) {
    r e t u r n $ t h i s −>hasMany ( P o s t : : c l a s s , [ ' u s e r _ i d ' =
    > ' i d ' ] ) ;
    }
    }
    // SELECT ∗ FROM u s e r s WHERE i d = 1 ;
    $ u s e r = U s e r : : f i n d O n e ( 1 ) ;
    // SELECT ∗ FROM p r o f i l e WHERE u s e r _ i d = 1 ;
    $ p r o f i l e = $ u s e r −> p r o f i l e ; // P r o f i l e
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ;
    $ p o s t s = $ u s e r −>p o s t s ; // P o s t [ ]
    $ p o s t Q u e r y = $ u s e r −>g e t P o s t s ()−> o r d e r B y ( ' c r e a t e d _ a t ' ) ;
    // SELECT ∗ FROM p o s t s WHERE u s e r _ i d = 1 ORDER BY c r e a t e d _ a t ;
    $ p o s t s = $ p o s t Q u e r y −> a l l ( ) ; // P o s t [ ]
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  83. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    // SELECT ∗ FROM u s e r s ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ()−> w i t h ( [ ' p r o f i l e ']) −> a l l ( ) ;
    f o r e a c h ( $ u s e r s a s $ u s e r ) {
    e c h o $ u s e r −>p r o f i l e −>name ;
    }
    // SELECT ∗ FROM u s e r s
    // JOIN p r o f i l e s AS p
    // WHERE p . s t a t u s = ' f e a t u r e d ' ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ( )
    −>j o i n W i t h ( [ ' p r o f i l e AS p ' ] )
    −>wh er e ( [ ' p . s t a t u s ' =
    > ' f e a t u r e d ' ] )
    −> a l l ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  84. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    // SELECT ∗ FROM u s e r s ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ()−> w i t h ( [ ' p r o f i l e ']) −> a l l ( ) ;
    f o r e a c h ( $ u s e r s a s $ u s e r ) {
    e c h o $ u s e r −>p r o f i l e −>name ;
    }
    // SELECT ∗ FROM u s e r s
    // JOIN p r o f i l e s AS p
    // WHERE p . s t a t u s = ' f e a t u r e d ' ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ( )
    −>j o i n W i t h ( [ ' p r o f i l e AS p ' ] )
    −>wh er e ( [ ' p . s t a t u s ' =
    > ' f e a t u r e d ' ] )
    −> a l l ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  85. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    // SELECT ∗ FROM u s e r s ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ()−> w i t h ( [ ' p r o f i l e ']) −> a l l ( ) ;
    f o r e a c h ( $ u s e r s a s $ u s e r ) {
    e c h o $ u s e r −>p r o f i l e −>name ;
    }
    // SELECT ∗ FROM u s e r s
    // JOIN p r o f i l e s AS p
    // WHERE p . s t a t u s = ' f e a t u r e d ' ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ( )
    −>j o i n W i t h ( [ ' p r o f i l e AS p ' ] )
    −>wh er e ( [ ' p . s t a t u s ' =
    > ' f e a t u r e d ' ] )
    −> a l l ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  86. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    // SELECT ∗ FROM u s e r s ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ()−> w i t h ( [ ' p r o f i l e ']) −> a l l ( ) ;
    f o r e a c h ( $ u s e r s a s $ u s e r ) {
    e c h o $ u s e r −>p r o f i l e −>name ;
    }
    // SELECT ∗ FROM u s e r s
    // JOIN p r o f i l e s AS p
    // WHERE p . s t a t u s = ' f e a t u r e d ' ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ( )
    −>j o i n W i t h ( [ ' p r o f i l e AS p ' ] )
    −>wh er e ( [ ' p . s t a t u s ' =
    > ' f e a t u r e d ' ] )
    −> a l l ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  87. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    // SELECT ∗ FROM u s e r s ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ()−> w i t h ( [ ' p r o f i l e ']) −> a l l ( ) ;
    f o r e a c h ( $ u s e r s a s $ u s e r ) {
    e c h o $ u s e r −>p r o f i l e −>name ;
    }
    // SELECT ∗ FROM u s e r s
    // JOIN p r o f i l e s AS p
    // WHERE p . s t a t u s = ' f e a t u r e d ' ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ( )
    −>j o i n W i t h ( [ ' p r o f i l e AS p ' ] )
    −>wh er e ( [ ' p . s t a t u s ' =
    > ' f e a t u r e d ' ] )
    −> a l l ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  88. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    // SELECT ∗ FROM u s e r s ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ()−> w i t h ( [ ' p r o f i l e ']) −> a l l ( ) ;
    f o r e a c h ( $ u s e r s a s $ u s e r ) {
    e c h o $ u s e r −>p r o f i l e −>name ;
    }
    // SELECT ∗ FROM u s e r s
    // JOIN p r o f i l e s AS p
    // WHERE p . s t a t u s = ' f e a t u r e d ' ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ( )
    −>j o i n W i t h ( [ ' p r o f i l e AS p ' ] )
    −>wh er e ( [ ' p . s t a t u s ' =
    > ' f e a t u r e d ' ] )
    −> a l l ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  89. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    // SELECT ∗ FROM u s e r s ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ()−> w i t h ( [ ' p r o f i l e ']) −> a l l ( ) ;
    f o r e a c h ( $ u s e r s a s $ u s e r ) {
    e c h o $ u s e r −>p r o f i l e −>name ;
    }
    // SELECT ∗ FROM u s e r s
    // JOIN p r o f i l e s AS p
    // WHERE p . s t a t u s = ' f e a t u r e d ' ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ( )
    −>j o i n W i t h ( [ ' p r o f i l e AS p ' ] )
    −>wh er e ( [ ' p . s t a t u s ' =
    > ' f e a t u r e d ' ] )
    −> a l l ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  90. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    // SELECT ∗ FROM u s e r s ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ()−> w i t h ( [ ' p r o f i l e ']) −> a l l ( ) ;
    f o r e a c h ( $ u s e r s a s $ u s e r ) {
    e c h o $ u s e r −>p r o f i l e −>name ;
    }
    // SELECT ∗ FROM u s e r s
    // JOIN p r o f i l e s AS p
    // WHERE p . s t a t u s = ' f e a t u r e d ' ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ( )
    −>j o i n W i t h ( [ ' p r o f i l e AS p ' ] )
    −>wh er e ( [ ' p . s t a t u s ' =
    > ' f e a t u r e d ' ] )
    −> a l l ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  91. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    // SELECT ∗ FROM u s e r s ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ()−> w i t h ( [ ' p r o f i l e ']) −> a l l ( ) ;
    f o r e a c h ( $ u s e r s a s $ u s e r ) {
    e c h o $ u s e r −>p r o f i l e −>name ;
    }
    // SELECT ∗ FROM u s e r s
    // JOIN p r o f i l e s AS p
    // WHERE p . s t a t u s = ' f e a t u r e d ' ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ( )
    −>j o i n W i t h ( [ ' p r o f i l e AS p ' ] )
    −>wh er e ( [ ' p . s t a t u s ' =
    > ' f e a t u r e d ' ] )
    −> a l l ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  92. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record Relations
    // SELECT ∗ FROM u s e r s ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ()−> w i t h ( [ ' p r o f i l e ']) −> a l l ( ) ;
    f o r e a c h ( $ u s e r s a s $ u s e r ) {
    e c h o $ u s e r −>p r o f i l e −>name ;
    }
    // SELECT ∗ FROM u s e r s
    // JOIN p r o f i l e s AS p
    // WHERE p . s t a t u s = ' f e a t u r e d ' ;
    // SELECT ∗ FROM p r o f i l e s WHERE i d IN ( . . . ) ;
    $ u s e r s = U s e r : : f i n d ( )
    −>j o i n W i t h ( [ ' p r o f i l e AS p ' ] )
    −>wh er e ( [ ' p . s t a t u s ' =
    > ' f e a t u r e d ' ] )
    −> a l l ( ) ;
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  93. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record NoSQL
    No JOINs?
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  94. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record NoSQL
    No JOINs? → NoSQL!
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  95. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record NoSQL
    No JOINs? → NoSQL!
    https://github.com/yiisoft/yii2-elasticsearch
    https://github.com/yiisoft/yii2-mongodb
    https://github.com/yiisoft/yii2-redis
    https://github.com/yiisoft/yii2-sphinx
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  96. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record NoSQL
    yii\db\ActiveRecordInterface
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  97. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record NoSQL
    yii\db\ActiveRecordInterface
    yii
    \elasticsearch
    \ActiveRecord
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  98. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record NoSQL
    yii\db\ActiveRecordInterface
    yii
    \elasticsearch
    \ActiveRecord yii
    \redis
    \ActiveRecord
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  99. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record NoSQL
    yii\db\ActiveRecordInterface
    yii
    \elasticsearch
    \ActiveRecord yii
    \redis
    \ActiveRecord
    yii
    \mongodb
    \ActiveRecord
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  100. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record NoSQL
    yii\db\ActiveRecordInterface
    yii
    \elasticsearch
    \ActiveRecord yii
    \redis
    \ActiveRecord
    yii
    \mongodb
    \ActiveRecord yii
    \sphinx
    \ActiveRecord
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  101. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record NoSQL
    ActiveRecord and ActiveQuery interfaces
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  102. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record NoSQL
    ActiveRecord and ActiveQuery interfaces
    common usage, select() where() orderBy() limit()
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  103. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record NoSQL
    ActiveRecord and ActiveQuery interfaces
    common usage, select() where() orderBy() limit()
    used by DataProvider
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  104. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    DAO
    Query Builder
    Active Record
    Active Record NoSQL
    ActiveRecord and ActiveQuery interfaces
    common usage, select() where() orderBy() limit()
    used by DataProvider
    Relation between SQL and NoSQL
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  105. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Switching between layers
    findBySql("SELECT ... WHERE ...")
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  106. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Switching between layers
    findBySql("SELECT ... WHERE ...")
    find()->asArray()->all()
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  107. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Switching between layers
    findBySql("SELECT ... WHERE ...")
    find()->asArray()->all()
    yii\db\Expression("plain SQL")
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  108. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Switching between layers
    findBySql("SELECT ... WHERE ...")
    find()->asArray()->all()
    yii\db\Expression("plain SQL")
    Yii philosophy: maximum exibilty.
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  109. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Conclusion / Questions?
    DAO,
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  110. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Conclusion / Questions?
    DAO, QueryBuilder,
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  111. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Conclusion / Questions?
    DAO, QueryBuilder, ActiveRecord
    Simple to start with,
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  112. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Conclusion / Questions?
    DAO, QueryBuilder, ActiveRecord
    Simple to start with, but not limited in complex situations.
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  113. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Conclusion / Questions?
    DAO, QueryBuilder, ActiveRecord
    Simple to start with, but not limited in complex situations.
    SQL,
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  114. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Conclusion / Questions?
    DAO, QueryBuilder, ActiveRecord
    Simple to start with, but not limited in complex situations.
    SQL, and NoSQL
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  115. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Conclusion / Questions?
    DAO, QueryBuilder, ActiveRecord
    Simple to start with, but not limited in complex situations.
    SQL, and NoSQL
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide

  116. Why do we need DB abstraction?
    Yii DB abstraction layers
    Switching between layers
    Conclusion / Questions?
    DAO, QueryBuilder, ActiveRecord
    Simple to start with, but not limited in complex situations.
    SQL, and NoSQL
    Contact:
    @cebe on Github
    @cebe_cc on Twitter
    [email protected] via Email
    https://github.com/yiisoft/yii2/issues
    Carsten Brandt The Database Layer of Yii Framework 2.0
    June 16, 2017 - Yiiconf, Ìîñêâ
    / 20

    View full-size slide