Примеры работы с SQL

Несколько примеров работы с SQL, "подводные камни", полезные советы.

Структура

Таблицы

DROP TABLE `artists`;

CREATE TABLE `artists` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
);

INSERT INTO `artists` (`id`, `name`) VALUES
(1, 'Theory of a Dead Man'),
(2, 'Teddy Pendergrass'),
(3, 'Ben E. King'),
(4, 'Patrice Rushen'),
(5, 'Bobby Byrd'),
(6, 'Society''s Bag'),
(7, 'Leee John'),
(8, 'Angelo Badalamenti'),
(9, 'Nina Simone'),
(10, 'Martina Topley-Bird');

-- --------------------------------------------------------

DROP TABLE `albums`;

CREATE TABLE `albums` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `albums` (`id`, `title`) VALUES
(1, 'Fahrenheit Soundtrack');

-- --------------------------------------------------------

DROP TABLE `songs`;

CREATE TABLE `songs` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `artist_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `artist_id` (`artist_id`)
);

INSERT INTO `songs` (`id`, `title`, `artist_id`) VALUES
(1, 'Santa Monica', 1),
(2, 'Say Goodbye', 1),
(3, 'No Way Out', 1),
(4, 'No Surprise', 1),
(5, 'Love TKO', 2),
(6, 'Street Tough', 3),
(7, 'Hang It Up', 4),
(8, 'Try it Again', 5),
(9, 'Let it Crawl', 6),
(10, 'Just an Illusion', 7),
(11, 'Lucas'' Main Theme', 8),
(12, 'Carla''s Main Theme', 8),
(13, 'No Good Man', 9),
(14, 'Sandpaper Kisses', 10);

-- --------------------------------------------------------

DROP TABLE `albums_songs`;

CREATE TABLE `albums_songs` (
  `album_id` int(10) unsigned NOT NULL,
  `song_id` int(10) unsigned NOT NULL,
  KEY `album_id` (`album_id`,`song_id`)
);

INSERT INTO `albums_songs` (`album_id`, `song_id`) VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 6),
(1, 7),
(1, 8),
(1, 9),
(1, 10),
(1, 11),
(1, 12),
(1, 13),
(1, 14);

Поле связи песни и автора song_id - именно в единственном роде. Таблица связей albums_songs называется так, а не иначе потому что буква A в алфавите гораздо раньше S.

Модели

class Artist extends SQL {
    public $has_many = array('songs');
}

class Song extends SQL {
    public $has_one = array('artist');
    public $has_and_belongs_to_many = array('albums');
}

class Album extends SQL {
    public $has_and_belongs_to_many = array('songs');
}

Если вы вдруг назовёте одно из полей таблицы has_and_belongs_to_many, например, то доступ к полю, а не к свойству обьекта должен осуществляться как к массиву: $object['has_and_belongs_to_many'], а не $object->has_and_belongs_to_many.

Доступ к обьектам как к массивам более желателен, ибо чисто теоретически ArrayAccess должен быть быстрее магических __get() и __set().

Select

Простой

$song = new Song(3);

// No Way Out
print_r($song->title);

// Theory of a Dead Man
print_r($song->artist->name);

// No Way Out
print_r($song[3]->title);

// Theory of a Dead Man
print_r($song[3]->artist->name);

// No Way Out
print_r($song[3]['title']);

// Theory of a Dead Man
print_r($song[3]['artist']['name']);

Проблема в том, что при таких запросах выбираются все поля. Поэтому следует использовать sql->select(), в котором и указать нужные для выборки поля. К тому же использование sql->select() сократит кол-во запросов с over 9000 до одного-двух.

$song = new Song(3);

$song->select('song.title', 'artist.name');

// No Way Out
print_r($song->title);

// Theory of a Dead Man
print_r($song->artist->name);

Для получения конечного массива следует использовать sql->as_array() (для конечного неизменяемого обьекта - sql->as_object()).

$song = new Song;
$song->select('title', 'artist.name');
$song->where('id > ?', 3);
$song->where('id < ?', 6);

print_r($song->as_array());

/* Array
(
    [4] => Array
        (
            [title] => No Surprise
            [artist] => Array
                (
                    [name] => Theory of a Dead Man
                )

        )

    [5] => Array
        (
            [title] => Love TKO
            [artist] => Array
                (
                    [name] => Teddy Pendergrass
                )

        )

) */

Непростой

Если связей много и они глубоки, то "сосисочный" метод - самое оно.

$artist = new Artist(7);

// Leee John
print_r($artist->name);

// Just an Illusion
print_r($artist->songs[10]->title);

// Fahrenheit Soundtrack
print_r($artist->songs[10]->albums[1]->title);

Обратите внимание: каждый идентификатор при таком подходе - реальный идентификатор записи в базе.

Но при использовании метода sql->select() доступ осуществляется по псевдо-идентификаторам массива, где 0 это первая запись.

$artist = new Artist(7);
$artist->select('name', 'songs.title', 'songs.albums.title');

// Leee John
print_r($artist->name);

// Just an Illusion
print_r($artist->songs[0]->title);

// Fahrenheit Soundtrack
print_r($artist->songs[0]->albums[0]->title);

Анальный

В построении запроса категорически нерекомендуется использовать алиасы с точкой.

$test = new Song(2);
$test->select('id', 'now() as `_some.date`');

print_r($test->as_array());

/* Array
(
    [id] => 2
    [some] => Array
        (
            [date] => 2009-09-20 08:04:10
        )

) */

Так делать можно, конечно. Но не надо. Все конструкции с точкой скрипт кушает и обрабатывает, исключая конструкции с кавычками. Подобная возможность не выпелена только из-за внутренних потребностей класса, когда он внутри себя строит запросы на себе же. Вот такой онанизм в кубе.

Так же не рекомендуется использовать префикс _ (знак нижнего подчёркивания) у таблиц и полей.

Циклы

$artist = new Artist(8);

// Angelo Badalamenti
print_r($artist->name);

foreach ($artist->songs as $k => $song)
    print_r($k.') '.$song->title);

/* 0) Lucas' Main Theme
1) Carla's Main Theme */

В таком случае будет создан дополнительный запрос на каждую итерацию цикла. В примере у артиста две песни значит +2 запроса. При использовании метода sql->select() количество запросов сократится до двух.

$artist = new Artist(8);
$artist->select('name', 'songs.title');

// Angelo Badalamenti
print_r($artist->name);

foreach ($artist->songs as $k => $song)
    print_r($k.') '.$song->title);

/* 0) Lucas' Main Theme
1) Carla's Main Theme */

Почему два запроса, а не один? Потому что при использовании limit и where MySQL обрезает столбцы. И это всё логично до тех пор пока мы не хотим получить всех артистов отфильтрованых по песням. В этом случае мы получим артистов, но у них будут только выбранные в фильтре песни. Поэтому SQL при запросах типа many сначала получает идентификаторы артистов учитывая фильтрацию, а потом получает всех артистов и все их песни по полученым ранее идентификаторам. Вариант не лучшей, но альтернативу тому вижу лишь в подзапросах, а это не путь Джедая.

Циклы в циклах

$artists = new Artist;
$artists->select('name', 'songs.title');

foreach ($artists as $i => $artist){
    print_r($i.') '.$artist->name);

    foreach ($artist->songs as $k => $song)
        print_r("\t".$k.')'.$song->title);
}

/* 0) Theory of a Dead Man
    0) Santa Monica
    1) Say Goodbye
    2) No Way Out
    3) No Surprise
1) Teddy Pendergrass
    0) Love TKO
2) Ben E. King
    0) Street Tough
3) Patrice Rushen
    0) Hang It Up
4) Bobby Byrd
    0) Try it Again
5) Society's Bag
    0) Let it Crawl
6) Leee John
	0) Just an Illusion
7) Angelo Badalamenti
    0) Lucas' Main Theme
    1) Carla's Main Theme
8) Nina Simone
	0) No Good Man
9) Martina Topley-Bird
    0) Sandpaper Kisses */

В данном случае будет 2 запроса. Если будут условия where и/или limit, то прибавится ещё 2, то есть 4 запроса - максимум. Для максимального сокращения запросов используйте sql->as_array() или sql->as_object().

Подзапрос в select

Исключительно для справки: MySQL не кэширует подзапросы. Не надо обвинять SQL, если ваш запрос начнёт тормозить.

$song = new Song;
$song->select('count(*)');
$song->where('artist_id = artist.id');

$artist = new Artist(1);
$artist->select('id', 'name', $song);

print_r($artist->as_array());

/* Array
(
    [id] => 1
    [name] => Theory of a Dead Man
    [_songs] => 4
) */

Обратите внимание: 1) в sql->where() используется нехарактераная для скрипта конструкция, она не принесёт ничего хорошего, когда связи описаны, но тут полезна и работает как надо; 2) таблица songs автоматически получила префикс _ (знак нижнего подчёркивания). Такой префикс получают все подзапросы.

Подзапрос в join

Просто хочу обрать внимание на то, что не надо использовать такой запрос тупо для сокращение общего количества запросов. Это выйдет боком: MySQL не кэширует подзапросы и у вас получится меньше запросов, но в итоге более тормознутых.

$song = new Song;
$song->select('id', 'count(*) as count');
$song->where('id > 0');
$song->group_by('artist_id');

$artist = new Artist(1);
$artist->select('id', 'name', '_songs.count');
$artist->join($song);

print_r($artist->as_array());

/* Array
(
    [id] => 1
    [name] => Theory of a Dead Man
    [_songs] => Array
        (
            [count] => 4
        )

) */

Сам запрос будет выглядеть так (отформатировал его ручёнками для наглядности).

select
    `artist`.id,
    `artist`.name,
    `_songs`.count as `_songs.count`,
    `artist`.id as array_key_1,
    null as array_key_2
from `artists` as `artist`

-- Это оно пошло
left join (
    select
        `join_songs`.id,
        count(*) as count,

        -- artist_id это связующие поле и если его явно не указать
        -- оно всё равно будет участвовать в выборке
        `join_songs`.artist_id
    from `songs` as `join_songs`
    where (`join_songs`.id > 0)
    group by `join_songs`.artist_id
) as `_songs` on (`_songs`.artist_id = `artist`.id)

-- Продолжение основного запроса
where (`artist`.id = '1')

И да, внутри джоина могуть быть другие джоины.

Магия

Хм, "магия" это когда чего-то нет, но оно появляется.

Поля *_ids

Поля sql->*_ids ормируются для обьектов имеющих связь типа has_and_belongs_to_many.

$song = new Song(10);

// 7; Никакой магии, такое поле существует
print_r($song->artist_id);

// Array ([0] => 1); Магия. Такого поля у таблицы song нет,
// зато есть целая таблица связей откуда данные и получены
print_r($song->albums_ids);

Обратите внимание: sql->*_ids на данный момент возвращают array, но полного доступа такой массив не имеет.

$song = new Song(7);

// Array ([0] => 1); То есть это как бы массив, но...
print_r($song->albums_ids);

// ... но к нему нет динамического доступа

$song->albums_ids[] = 2;
$song->albums_ids[] = 5;

// По-прежнему Array ([0] => 1)
print_r($song->albums_ids);

$song->albums_ids += array(2, 5);

// По-прежнему Array ([0] => 1)
print_r($song->albums_ids);

Как тогда сделать, например, добавление/редактирование альбома и при этом одновременно добавлять песни и класть их в этот альбом? Пока через третью переменную.

$songs_ids = array();

$album = new Album;
$album->title = 'New Album';

$song = new Song;
$song->title = 'New Song #1';
$songs_ids[] = $song;

$song = new Song;
$song->title = 'New Song #2';
$songs_ids[] = $song;

$album->songs_ids = $songs_ids;

// 2
print_r($album->save());

sql->by_*()

Методы sql->by_*() являются синонимами sql->where() для простых сравнений (равно и in()).

$song = new Song;

// Синоним для $song->where('id = ?', 10);
$song->by_id(10);

// Синоним для $song->where('id in(?a)', array(1, 5, 10));
$song->by_id(array(1, 5, 10));

// Синоним для $song->where('artist.name = ?', 'Leee John');
$song->by_artist_name('Leee John');

// в итоге where в запросе будет выглядеть как-то так:
// `song`.id = 10 and `song`.id in(1, 5, 10) and `artist`.name = 'Leee John'

Триггеры

Работа триггеров (named scope из RoR, но по мне так действие вызывающее ряд иных действий - это триггер) тоже проста, но полезна. Триггеры создаются в моделе, как массив и после превращаются в функции. При чём тут магия, если триггеры-то есть? Ну, когда иллюзионист достаёт кролика из пустой шляпы это не значит, что кролика нет.

class Song extends SQL {
    public $has_one = array('artist');
    public $has_and_belongs_to_many = array('albums');

    // Это оно
    public $trigger = array(
        'test' => array(
            'limit' => 5,
            'order_by' => 'id'
        )
    );
}

$song = new Song(10);

// where ... order by id limit 5
print_r($song->test()->get());

// То же самое, но без триггера
print_r($song->order_by('id')->limit(5)->get());

Триггерам можно задавать значения.

class Song extends SQL {
    public $has_one = array('artist');
    public $has_and_belongs_to_many = array('albums');

    public $trigger = array(
        'test' => array(
            'limit' => '?', // Это оно
            'order_by' => 'id'
        )
    );
}

$song = new Song(10);

// where ... order by id limit 20
print_r($song->test(20)->get());

Мутаторы

Не знал как это назвывается поэтому остановился на "мутаторах".

class Song extends SQL {
    public $has_one = array('artist');
    public $has_and_belongs_to_many = array('albums');

    // Это оно
    protected _get_title(){
        return self::_get('title').' [_get_title]';
    }
}

$song = new Song(10);

// Just an Illusion [_get_title]
print_r($song->title);

// Just an Illusion [_get_title]
print_r($song['title']);

То же самое можно и для установки.

class Song extends SQL {
    public $has_one = array('artist');
    public $has_and_belongs_to_many = array('albums');

    // Это оно
    protected function _set_title($value){
        self::_set('title', md5($value));
    }
}

$song = new Song(10);

// Just an Illusion
print_r($song->title);

// Test
print_r($song->title = 'Test');

// 0cbc6611f5540bd0809a388dc95a615b; В базу вставится именно в таком виде
print_r($song->title);

В мутаторах надо вызывать методы управления потому что это даёт более широкий простор для действий, как для несуществующих полей, так и для нестандартных значений. И то, и то можно будет обработать самостоятельно и как угодно.