Чуть больше о SQL

Описание базовых возможностей с примерами.

Структура

Таблицы

DROP TABLE IF EXISTS  `artists`;

CREATE TABLE `artists` (
  `id` int 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 IF EXISTS  `albums`;

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

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

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

DROP TABLE IF EXISTS  `songs`;

CREATE TABLE `songs` (
  `id` int unsigned NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `artist_id` int 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 IF EXISTS `albums_songs`;

CREATE TABLE `albums_songs` (
  `album_id` int unsigned NOT NULL,
  `song_id` int 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 {
    protected $has_many = array('songs');
}

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

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

Select

Простой

$song = new Song(3);

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

// Theory of a Dead Man
print_r($song->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->fetch_array().

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

print_r($song->fetch_array());

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

        )

    [1] => 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[0]->title);

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

Хотя, в примере выше нет ничего глубокого, поэтому для сокращения запросов лучше использовать sql->select().

$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->fetch_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, то прибавится ещё 1, то есть всего 3 запроса и не более. Для максимального сокращения запросов используйте sql->fetch_array(), он поможет избежать подсчёта и количество запросов сократится на один запрос.

Подзапрос в select

Исключительно для справки: MySQL не кеширует подзапросы.

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

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

print_r($artist->fetch_array());

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

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

Для "чистых" подзапросов можно вместо объекта SQL вставлять SQL_Query.

$song = sql::query('(select count(*) from ?t where artist_id = artist.id) as _songs', 'songs');

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

print_r($artist->fetch_array());

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

Подзапрос в 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->fetch_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
        `subquery_songs`.id,
        count(*) as count,

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

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

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

Подзапрос в where

Они поддерживаются, но адекватного примера я не придумал.

Магия

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

Поля *_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 это объект ArrayObject.

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'

Мутаторы

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

class Song extends SQL {
    protected $has_one = array('artist');
    protected $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 {
    protected $has_one = array('artist');
    protected $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);

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