Ako Milan strávil týždeň bláznivých nápadov

MySQL QueryBuilder pre Profesiu

Motivácia

public function verziaA()
{
    $query = 'select id, title from article where is_published = 1';

    if ($this->shouldDisplayDate) {
        // potrebujem do zoznamu fieldov pridať stĺpec date_published
    }

    return $this->databaseManager->fetchAll($query);
}

public function verziaB()
{
    $fields = ['id', 'title'];

    $query = 'from article where is_published = 1';

    if ($this->shouldDisplayDate) {
        $fields[] = 'date_published';
    }

    return $this->databaseManager->fetchAll(
        'select '
        . implode(', ', $fields) . ' '
        . $query
    );
}

Motivácia 2

public function heredocSyntax()
{
    $query = <<<SQL
select
    id, title
from
    article
where
    is_published = 1
SQL;

    return $this->databaseManager->fetchAll($query);
}

Motivácia 3

public function whereIn($ids)
{
    $query = 'select * from article where id ' . QueryUtils::in($ids);

    return $this->databaseManager->fetchAll($query);
}

Môj cieľ

  1. vytvoriť query builder pre select, insert, update, delete dotazy
  2. urobiť nový spôsob escapovania premenných a pritom ponechať možnosť bindovania
  3. umožniť zadávanie where podmienok pomocou poľa

 

Výsledok 1. cieľa

Máme QueryBuilder pre SELECT

Máme QueryBuilder pre INSERT

Nestihol som UPDATE a DELETE

SelectQueryBuilder

$query = $this->databaseManager->select('a.id, a.title')
    ->select('a.date_published')
    ->from('article')->as('a')
    ->where('a.is_published = 1')
    ->where('a.is_approved = 1')
    ->orderBy('a.date_published desc');

if ($showAuthors) {
   $query->select('u.firstname, u.lastname');
   $query->join('users')->as('u')->on('u.id = a.user_id');
}

$result = $this->databaseManager->fetchAll($query);

InsertQueryBuilder

$query = $this->databaseManager->insertInto('article', [
    'title'   => 'Lorem Ipsum',
    'user_id' => 7,
]);

$this->databaseManager->query($query);

$query = $this->databaseManager->insertInto('article', [
    [
        'title'   => 'Lorem Ipsum',
        'user_id' => 7,
    ],
    [
        'title'   => 'Star Trek Ipsum',
        'user_id' => 42,
    ],
]);

$this->databaseManager->query($query);

InsertQueryBuilder

$query = $this->databaseManager->insertInto('article')
    ->set('title', 'Lorem Ipsum');

$query->set('user_id', 7);

$this->databaseManager->query($query);
$query = $this->databaseManager->insertInto('article', [
    'title' = 'Lorem Ipsum',
    'slug' => 'lorem-ipsum',
]);

$query->onDuplicateKeyUpdate(
    "slug = concat(values(slug), '-', values(id))"
);

InsertQueryBuilder

$query = $this->databaseManager->insertInto('article')
    ->values(['title' => 'Lorem Ipsum']);


if ($insertMore) {
    $query->values(['title' => 'Star Trek Ipsum']);
}

if ($insertEvenMore) {
    $query->values([
        [
            'title'   => 'Star Wars Ipsum',
        ],
        [
            'title'   => 'Firefly Ipsum',
        ],
    ]);
}

$this->databaseManager->query($query);

Výsledok 2. cieľa

Ostalo zachované doterajšie bindovanie premenných

Pridaný nový spôsob escapovania na spôsob printf

Doterajšie bindovanie premenných

$query = $this->databaseManager->select('id, title')
    ->from('article')
    ->where('user_id = :userId')
    ->where('slug = :slug');

$this->databaseManager->fetchAll($query, [
   'userId' => $user->getId(),
   'slug'   => $request->query->get('slug'),
]);

Escapovanie na spôsob printf

$query = $this->databaseManager->select('id, title')
    ->from('article')
    ->where('user_id = %i', $user->getId())
    ->where('slug = %s', $request->query->get('slug'));


$query->where(
    'is_published = %i and published_at >= %dt',
    $isPublished,
    new \DateTime('2018-09-01 00:00:00')
);

$manager->insertInto('article', [
    'title%s'         => $title,
    'user_id%i'       => $userId, 
    'published_at%dt' => new \DateTime(),
]);

Escapovanie na spôsob printf

Existujúce modifikátory:

%i integer
%f float
%s string (berie do úvahy PDO pripojenie, encoding atď)
%d dátum (string alebo DateTime)
%dt dátum a čas (string alebo DateTime)
%sql žiadne escapovanie (vhodné pre neštandardné query)
%% percento (napr. pre like dotazy)

Výsledok 3. cieľa

Prvá skvelá vec:

$query->where([
    'is_published' => 1,
    'user_id%in'   => [4, 5, 6],
]);

// where
//     is_published = 1
//     and user_id in (4, 5, 6)

Výsledok 3. cieľa

Druhá skvelá vec

$query->where('%or', [
    'is_published' => 1,
    'user_id%in'   => [4, 5, 6],
    '%and' => [
        'is_published' => 0,
        'user_id'      => 7,
    ]
]);

// where
//     is_published = 1
//     or user_id in (4, 5, 6)
//     or (is_published = 0 and user_id = 7)

Ani jedna sa mi nepodarila

Zdrojáky

  • samostatná knižnica
  • do Profesie sa nainštaluje cez composer (nestihol som)
  • git repo: https://git.profesia.sk/library/query-builder

Ďakujem za pozornosť

MySQL QueryBuilder

By Milan Herda

MySQL QueryBuilder

  • 550

More from Milan Herda