webew
Войти » Регистрация
 
PHP
MySQL/MariaDB

Легкий ORM для PHP и MySQL

31 декабря 2013, 0:28

В статье приводится реализация объектно-реляционного отображения, выполненная в виде небольшой библиотеки с простым и гибким интерфейсом. Код библиотеки доступен в виде одного php-файла. В качестве интерфейса к базе данных используются удобные функции PHP для работы с MySQL.

Объектно-реляционное отображение (англ. object-relational mapping или ORM) — техника программирования, которая позволяет разработчику абстрагироваться от механизмов работы хранилища данных и писать код в терминах логики конкретного приложения. В частности, заменять написание SQL-запросов вызовом специальных функций/методов.1

Поиск по параметрам

Самая частая задача при разработке веб-приложений — это построение списка на основании некоторых условий. Такой список может быть разбит на страницы и определенным образом отсортирован.

В качестве примера рассмотрим получение списка товаров для страницы каталога в интернет-магазине, относящейся к определенному производителю.

Предположим, товары хранятся вот в такой таблице:

mysql> SELECT * FROM products;
+----+-----------+-------+----------+-----------------+-------+
| id | title     | price | discount | manufacturer_id | text  |
+----+-----------+-------+----------+-----------------+-------
|  1 | Веник     |   100 |        0 |               1 |       |
|  2 | Швабра    |   500 |        0 |               1 |       |
|  3 | Чайник    |  1500 |       10 |               2 |       |
|  4 | Совок     |   150 |        0 |               1 |       |
|  5 | Телевизор |  5000 |       30 |               3 |       |
|  6 | Ведро     |   200 |        0 |               1 |       |
+----+-----------+-------+----------+-----------------+-------+

Страница каталога снабжена формой поиска, позволяющей ограничивать список товаров по некоторым параметрам:

Цена от до скидка
Название (начинается с)

HTML-код формы:

<form id="example">
    Цена от <input name="price_final[min]">
    до <input name="price_final[max]">
    <input name="has_discount" type="checkbox"> скидка
    <button>OK</button>
    <br>
    Название (начинается с) <input name="title">
</form>

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

require_once 'ORM.php';

$query = array(
        "manufacturer_id = 2",
        'where' => $_GET,
        'orderby' => '[ord]',
        'limit' => [ 'p', ['N',20] ],
    );
$P = new Products;
$products = $P->find($query);

Методу find() передается массив с условиями поиска: конкретные значения параметров, указания по сортировке и LIMIT, а также ограничения выборки, задаваемые вручную.

Ключ where этого массива содержит значения параметров поиска, из которых по определенным правилам формируются условия конечного SQL-запроса (см. конфигурация параметров поиска). При вставке в запрос значения параметров автоматически экранируются2, лишние ключи (для которых в конфигурации нет соответствующего параметра) просто игнорируются.

Остальные условия поиска будут подробно рассмотрены в соответствующих разделах статьи (произвольные условия, LIMIT, сортировка).

Класс Products унаследован от специального абстрактного класса библиотеки и содержит в своем определении отображение параметров поиска (читай — полей формы) на структуру базы данных:

Class Products extends _List {
   
    public $tables = array(
            "FROM products p",
            // здесь могут быть также JOIN с другими таблицами
        );
   
    public $columns = array(
            'id' => array(
                    'sql' => "p.id",
                    'sort' => '* DESC',
                ),
            'price_final' => array(
                    'sql' => "price * (100 - discount) / 100",
                    'type' => '[min,max]',
                    'sort' => TRUE
                ),
            'title' => array(
                    'type' => 'LIKE%',
                    'sort' => TRUE
                ),
            'has_discount' => array(
                    'sql' => "discount > 0",
                    'type' => 'on-off'
                ),
        );
   
}

Конфигурация параметров поиска

Теперь обратимся к массиву $columns и рассмотрим конфигурацию каждого из полей детально.

Поле, являющееся уникальным идентификатором записи в главной таблице, обязательно должно присутствовать и следовать в списке первым. Именно поэтому id упоминается в конфигурационном массиве, хотя его нет в форме.

Ключ type определяет, как конкретно указанное SQL-выражение будет сопоставлено значению соответствующего параметра (эти значения входят в условия для построения списка (массив $query) и передаются в ключе where). Сопоставление бывает четырех видов.

Равенство

type, не установленный вовсе (как в случае с id) или равный FALSE в результате даст условие со знаком «равно», а если значения параметра является не скалярной величиной, а массивом, то IN.

Например, если бы $_GET содержал в ключе id число 10, получилось бы условие

WHERE (p.id = 10)

а если бы — массив array("Атос", "Д'Артаньян"), то3

WHERE (p.id IN ('Атос', \'Артаньян'))

Сопоставление типа LIKE

Другой вариант сопоставления параметра содержит конфигурация поля title:

'title' => array(
        'type' => 'LIKE%',
        ...
    )

Такая конфигурация даст условие вида

title LIKE 'параметр%'

где 'параметр' — это экранированный $_GET['title']4.

Если $_GET['title'] является массивом, условие будет составлено для каждого из его элементов, и затем все они объединены через OR (получится как бы IN для LIKE):

(title LIKE 'параметр[0]%') OR (title LIKE 'параметр[1]%') OR ...

Аналогичным образом можно использовать '%LIKE' и '%LIKE%'.

Интервальное сопоставление

Конфигурация поля price_final является примером интервального сопоставления:

'price_final' => array(
        'sql' => "price * (100 - discount) / 100",
        'type' => '[min,max]',
        ...
    )

Указанные ключи параметра ограничивают выборку: первый — снизу, второй — сверху. Условие в данном случае примет вид:

price * (100 - discount) / 100 >= $_GET[price_final][min]
AND
price * (100 - discount) / 100 <= $_GET[price_final][max]

Вместо min и max можно указывать любые другие имена ключей, нужно лишь следить за тем, чтобы они совпадали с именами полей формы.

Можно указать только один ключ (например, '[min,]'), в этом случае будет возможность ограничить выборку только с одной стороны.

Если квадратные скобки заменить на круглые — '(min,max)' — нестрогое неравенство заменится на строгое (так же, как обозначают интервалы в математике). При этом они не обязательно должны быть одинаковыми: с одной стороны неравенство может быть строгим, а с другой — нет.

Сопоставление типа «включено/выключено»

Для такого сопоставления SQL-выражение просто вставляется в запрос в неизменном виде, но лишь если соответствующий параметр присутствует в запросе и отличен от FALSE.

Для поля has_discount с конфигурацией

'has_discount' => array(
        'sql' => "discount > 0",
        'type' => 'on-off'
    )

в запрос попадет условие WHERE (discount > 0).

Это сопоставление лучше всего подходит для полей формы типа checkbox.

Произвольные условия

Ограничения списка могут не только формироваться на основании параметров поиска, но и быть прямо указаны вручную.

В рассматриваемом примере страница каталога относится к какому-то конкретному производителю. В этом случае условие на manufacturer_id должно выполняться всегда — независимо от содержимого $_GET:

$query = array(
        "manufacturer_id = 2",
        'where' => $_GET,
        ...
    );

Таких произвольных условий может быть сколько угодно. Все они войдут запрос непосредственно в том виде, в котором указаны, без изменений.

Разбивка на страницы, LIMIT, получение общего количества строк

Для разбивки на страницы в условия для построения списка нужно включать элемент limit. В случае, если номер страницы передается в GET-параметре p, а количество записей на странице может содержаться в параметре N, код может выглядеть следующим образом:5

$query = array(
        'limit' => array(
                'page'     => ( isset($_GET['p']) ? $_GET['p'] : 1),
                'per_page' => ( isset($_GET['N']) ? $_GET['N'] : 20),
            ),
        ...
    );

То же самое можно записать в сокращенной форме (как это и сделано в примере):

$query = array(
        'limit' => [ 'p', ['N',20] ], // [...] - короткий вариант объявления массивов
        'where' => $_GET,             // в PHP с версии 5.4
        ...
    );

При такой форме записи имена ключей в limit относятся к массиву с параметрами поиска (where).

Запись вида [ 'p', ['N',20,100] ] устанавливает для количества записей ограничение сверху.

Можно указывать числовые значения явно: 'limit' => '0, 20' или 'limit' => 100. Такая запись перейдет в LIMIT запроса в неизменном виде.

Общее количество строк без учета LIMIT можно получить, передав методу find() необязательный второй аргумент — переменную, в которую это количество будет записано:6

// $count необязательно должна быть установлена ранее
$products = $P->find($query, $count);

Сортировка

Указывать сортировку в условиях запроса следует в ключе orderby. При этом можно ссылаться на элементы массива $columns. Например:

$query = array(
        'orderby' => "{*price_final DESC*}, id DESC",
        'where' => ...
        ...
    );

Фрагмент {*price_final DESC*} заменится на соответствующее SQL-выражение из массива $columns.

Чтобы дать возможность управлять сортировкой через параметры GET-запроса страницы, в ключ orderby нужно поместить специальную метку:

$query = array(
        'orderby' => '&ord',
        'where' => $_GET,
        ...
    );

Здесь ord — ключ массива $_GET, содержащий текущие параметры сортировки.

Соответственно, адрес страницы должен иметь вид ...&ord=(имя поля из конфигурации) (направление). Например, ...&ord=price_final DESC. Метка в orderby заменится SQL-выражением для выбранного поля7, в результате запрос примет вид:

ORDER BY price * (100 - discount) / 100 DESC

Можно комбинировать метку и постоянные части:

$query = array(
        ...
        'orderby' => "&ord, discount > 0 DESC, ...",
    );

А также задавать сортировку по умолчанию для случая, если она не указана через GET-параметр:

$query = array(
        ...
        'orderby' => "&ord || discount > 0, id DESC",
    );

Получение и обработка данных

Набор полей списка

Каждому параметру поиска соответствует одноименное поле в результирующем массиве данных. Взглянем еще раз на конфигурацию8:

public $columns = array(
    'id' => array(
            'sql' => "p.id",
            ...
        ),
    'price_final' => array(
            'sql' => "price * (100 - discount) / 100",
            ...
        ),
    'title' => array(
            ...
        ),
    'has_discount' => array(
            'sql' => "discount > 0",
            ...
        ),
);

Вот как примерно выглядит результат работы метода findIds():

    ...

    [5] => Array
        (
            [id] => 5
            [price_final] => 35000
            [title] => Телевизор
            [has_discount] => 1
        )

    [4] => Array
        (
            [id] => 4
            [price_final] => 1500
            [title] => Совок
            [has_discount] => 0
        )
    ...

Если поле нужно в качестве параметра поиска, но среди конечных данных не требуется, его можно исключить из результирующего массива, поместив в начало ключа специальную последовательность @@:

public $columns = array(
    'id' => ...
    ...
    '@@: has_discount' => array(
            'sql' => "discount > 0",
            ...
        ),
);

При такой конфигурации элемент has_discount среди полученных данных будет отсутствовать.

Получение данных одиночной сущности

Для получения данных одиночной сущности по её идентификатору служит метод getSingle():

$P = new Products;
$product = $P->getSingle($product_id);

Поля, которые для списка не требутся и нужны только для случая одиночной сущности, можно выделить явным образом. Их объявление должно начинаться с последовательности @::

public $columns = array(
    ...
    "@: p.text",
);

Поле p.text при поиске и работе со списками запрашиваться из БД не будет, что даёт определенный выигрыш в быстродействии.

Пост-обработка данных

К каждой строке, полученной из БД, могут быть применены дополнительные преобразования. Для этого нужно определить метод processData(). Например:

class Products extends _List {
   ...
   function processData($row) {
       $row['url'] = "/products/$row[id].html";
       return $row;
   }
}

Каждой записи результирующих данных добавится ключ url

    ...

    [5] => Array
        (
            [id] => 5
            [price_final] => 35000
            [title] => Телевизор
            [has_discount] => 1
            [url] => /products/5.html
        )

    [4] => Array
        (
            [id] => 4
            [price_final] => 1500
            [title] => Совок
            [has_discount] => 0
            [url] => /products/4.html
        )
    ...

Получение специальных данных

Периодически бывает нужно получить не просто список сущностей со всеми полями, а какой-то специальный набор их данных. Для этого служит метод getSpecialData().

На вход метод принимает набор SQL-выражений для будущих колонок результата, а также массив с условиями поиска (аналогичный тому, что используется для методов find() и findId()).

Например, для заданных условий поиска получить ценовой диапазон и количество товаров со скидкой от 50% можно с помощью следующего кода:

$query = array(-
        "manufacturer_id = 2",
        'where' => $_GET,
        ...
    );
$special_data = $P->getSpecialData(
        array(
            "SUM(discount >= 50) AS sale_count",
            "MIN(&price_final) AS price_min",
            "MAX(&price_final) AS price_max",
        ),
        $query
    );

Здесь &price_final — ссылка на SQL-выражение в массиве $columns.

Содержимое $special_data будет примерно следующим:

Array
(
    [0] => Array
        (
            [sale_count] => 35
            [price_min] => 1000
            [price_max] => 12500
        )

)

Если результат заведомо состоит из одной строки, удобно использовать разыменовывание массива:

$special_data = $P->getSpecialData(...)[0];

Другой пример — получение всех производителей (точнее, их идентификаторов), товары которых удовлетворяют определенным условиям:

$query = ...;
$special_data = $P->getSpecialData(
        "DISTINCT p.manufacturer_id",
        $query
    );

Здесь методу передается одна колонка вместо массива, что является инструкцей вернуть вместо табличного результата колоночный:

Array
(
    [0] => 2
    [1] => 5
    [2] => 10
)

Связанные сущности

Под связанной понимается сущность другого рода, на которую через уникальный идентификатор ссылается некоторое поле данной сущности.

Например, у каждого товара есть производитель, id которого хранится в поле manufacturer_id. Производителям соответствует свой собственный класс Manufacturers, также унаследованный от _List, где описывается вся логика работы с ними: набор полей, параметры поиска, пост-обработка данных и т.д.

Чтобы этой логикой пользоваться в классе товаров, нужно установить его связь с классом производителей. Это делается через соответствующее поле (в данном случае — manufacturer_id), которому в конфигурации устанавливается свойство related:

class Products extends _List {
    public $columns = array(
        ...
        'manufacturer_id' => array(
            'related' => array('manufacturer' => 'Manufacturers')
            // 'имя ключа' => 'класс' (объяснение см. далее по тексту)
        ),
    );
    ...
}

class Manufacturers extends _List {
    public $tables = array(
        "FROM manufacturers m",
    );
   
    public $columns = array(
        "id",
        "title",
        "country_id",
    );
   
    function processData($row) {
       $row['url'] = "/manufacturers/$row[id].html";
       return $row;
   }
    ...
}

Установление такой связи даёт две важных возможности.

Поиск по свойствам связанных сущностей

Среди параметров поиска могут быть относящиеся именно к производителям, а не только к самим товарам.

При передаче методу find() в составе $query['where'] они должны быть сгруппированы в подмассив manufacturer (имя подмассива указывается в конфигурации в качестве ключа в related — см. выше).

Например, включать в форму поиска поле для указания страны производителя нужно с атрибутом name="manufacturer[country_id]":

Цена от до скидка
Название (начинается с)
Страна-производитель:
<form>
    Цена от <input name="price_final[min]">
         до <input name="price_final[max]">
    <input name="has_discount" type="checkbox"> скидка
    <br>
    Название (начинается с) <input name="title">
    <br>
    Страна производства:
    <select name="manufacturer[country_id]">
        <option value="1">Россия</option>
        <option value="2">США</option>
        <option value="3">Германия</option>
    </select>
    <button>OK</button>
</form>

При этом сам вызов find() не меняется никак:

$query = array(
        'where' => $_GET,
        'orderby' => '[ord]',
        'limit' => [ 'p', ['N',20] ],
    );
$P = new Products;
$products = $P->find($query);

Единственное, что требуется — составить форму так, чтобы параметры поиска, относящиеся к производителю, приходили в подмассиве $_GET['manufacturer'].

Получение данных связанных сущностей

У каждой записи из $products автоматически появится подмассив manufacturer:

   ...

    [5] => Array
        (
            [id] => 5
            [price_final] => 35000
            [title] => Телевизор
            [has_discount] => 1
            [url] => /products/5.html
            [manufacturer] => Array
                (
                    [id] => 3
                    [title] => SONY
                    [country_id] => 7
                    [url] => /manufacturers/3.html
                )
        )

    [4] => Array
        (
            [id] => 4
            [price_final] => 1500
            [title] => Совок
            [has_discount] => 0
            [url] => /products/4.html
            [manufacturer] => Array
                (
                    [id] => 1
                    [title] => Полимербытхимпром
                    [country_id] => 1
                    [url] => /manufacturers/1.html
                )
        )
    ...

Добавление, изменение и удаление записей

write()

Для добавления записей и изменения их данных служит метод write(). В качестве аргументов он принимает массив полей записи и её идентификатор:

$P = new Products;
$P->write($_POST, $_GET['id']); // данные пришли из формы со страницы вида ?id=...

Если идентификатор не указывать, будет добавлена новая запись, а в ответ возращен ее идентификатор:

$P = new Products;
$new_id = $P->write($_POST);

При записи данные экранируются (используется mysql_write_row()), однако никаких проверок метод в себя не включает. Для этого рекомендуется использовать специальный инструмент для проверки правильности заполнения веб-форм.

delete()

Метод delete() в качестве аргумента принимает уникальный идентификатор (автоматически экранируется) и просто удаляет соответствующую запись из таблицы, возвращая TRUE в случае успеха:

$id = ...;
$P = new Products;
$P->delete($id);

Дополнительные сведения и возможности

В основе данной библиотеки лежит подход к построению списков, сущность которого заключается в следующем: сначала из БД с учетом условий, сортировки и LIMIT извлекаются уникальные идентификаторы строк, после чего для этих строк запрашиваются остальные данные. Обоснованию и разъяснению этого подхода посвящена отдельная статья.

Чтобы вместо полных данных строк получить только их идентификаторы, нужно воспользоваться методом findIds(), интерфейс которого полностью аналогичен таковому метода find():

$product_ids = $P->findIds($query, $count);

Для получения полных данных списка на основе уникальных идентификаторов служит метод getList():9

$products = $P->getList($product_ids);
// $product_ids внутри метода будут проэкранированы

getList() может также принимать строку SQL-запроса, минуя стадию получения идентификаторов:10

$products = $P->getList("SELECT id FROM products ...");

При наличии режимов сортировки, описываемых сложными SQL-выражениями, бывает удобно пользоваться

1. В классической реализации объектно-реляционного отображения каждой строке базы данных соответствует экземпляр объекта. Предлагаемая реализация не является классической: каждой строке здесь соответствует просто ассоциативный массив данных.

2. Используется функция mysql_escape().

3. Каждое из условий заключается в круглые скобки, чтобы ограничить действие оператора OR, если таковой встретится в выражении. То же самое касается произвольных условий (см. соответствующий раздел).

4. Следует отметить, что в конфигурации поля title отсутствует ключ sql. В таких случаях в качестве SQL-выражения используется непосредственно имя параметра.

5. Нумерация страниц начинается с единицы.

6. Если переменная для записи общего количества строк передана, будет выполнен отдельный запрос вида SELECT COUNT(*). Вопреки распространенной практике, такой вариант обладает более высокой производительностью, чем использование SQL_CALC_FOUND_ROWS. Подробнее см. http://sqlinfo.ru/forum/viewtopic.php?pid=38337

7. Если GET-параметр сортировки не соответствует ни одному из ключей массива $columns, он будет просто проигнорирован.

8. Конфигурация в примере имеет полную форму записи. Можно использовать также сокращенные формы:

public $columns = array(

    // все нижеуказанные варианты записи равнозначны
   
    'column' => [ // полная запись
        'sql' => 'table.column',
    ],
   
    'column' => 'table.column',
   
    'table.column',
   
    'column', // если среди всех задействованных таблиц нет колонок с одинаковыми именами,
              // указывать таблицу в явном виде необязательно
    ...
);

9. Метод find(), собственно говоря, состоит из вызова findIds() и getList().

10. Из-за этой возможности важно проверять пользовательские данные в случае, если они передаются методу напрямую. Например, для $P->getList($_GET['ids']) важно убедиться, что в GET-запросе передается именно массив чисел, а не строка, иначе возможна SQL-инъекция.


© Все права на данную статью принадлежат порталу webew.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в печатных изданиях допускается только с разрешения редакции.
Добавить комментарий
Отображение комментариев: Древовидное | Плоское

1234ru

В версии 1.90 исправлен небольшой баг.

История предыдущих значимых изменений
===========================================

1.80: упразднены свойства $columnsForSingle, $tablesForSingle.

1.71: добавлен метод для получения специальных данных — getSpecialData().

1.62:
Для сокращенного варианта limit вместо строки используется массив:
$query = array(
      'limit' => 'p, N:20',  // до версии 1.62
      ...
 );
$query = array(
      'limit' => ['p', ['N',20] ], // с версии 1.62
 );

Добавлена возможность ограничивать максимальное количество записей, отдаваемое в ответ на запрос, с помощью нотации вида ['varname', default, max]:
$query = array(
          'limit' => ['p', ['N',20,100] ],
          ...
     );


1.60:
- добавлены методы write() и delete()
- введен более удобный синтаксис для сортировки
- добавлен параметр related для связи с другими классами, свойства класса $columnsForSearch и $columnsForList заменены одним общим свойством $columns
- работает "умный" JOIN: на первичной стадии поиска (получение идентификаторов записей) подключаются только те таблицы, которые упоминаются в условиях или сортировке

1.12: исправлена потенциальная уязвимость при использовании сопоставления типа LIKE%: символы % и _ в строках-аргументах дополнительно экранируются слэшами.
То, что не убивает нас, делает нас инвалидами.
19.03.2016, 07:14
Ответить
NO USERPIC

ElenaGr

Не работает. Во-первых, не находит класс Products. Когда я вместо класса Products вызываю _List, выдает ошибку
Fatal error: Cannot instantiate abstract class _List

Можно работающий пример?
11.04.2016, 10:07
Ответить

1234ru

Цитата:
Fatal error: Cannot instantiate abstract class _List

Ошибка возникает, потому что создавать экземпляры объектов абстрактных классов (к которым относится _List) нельзя - на то они и абстрактные.

ElenaGr
не находит класс Products

Значит, у вас в коде он не объявлен.

Примеры есть в начале статьи. Но если вы скажете, как у вас называется таблица и какие в ней колонки - напишем класс специально для вас :)
То, что не убивает нас, делает нас инвалидами.
11.04.2016, 11:41
Ответить
NO USERPIC

ElenaGr

У меня очень большая таблица, больше 11 млн. записей. Селекты сильно тормозят. С классами работала мало, поэтому и не понимаю, что от меня хотят. Объявление класса нашла, вставила, но ошибка теперь, что не хватает памяти, а всего только id попросила вывести. Если вставлю еще данные, воoбще все повиснет.
11.04.2016, 12:16
Ответить

1234ru

Цитата:
Объявление класса нашла, вставила, но ошибка теперь, что не хватает памяти

Уже хорошо - значит, класс вы составили правильно.

Однако при таких объемах ошибка эта вполне ожидаема.

Давайте-ка разберемся, что вам вообще требуется? Какие задачи вы хотите решить?
Например, зачем вам понадобились все 11 млн. сразу, что вы с ними собираетесь делать?

Возможно, при таких объемах вам понадобятся немного другие приемы. Расскажите, в общем :)

Дальнейшее обсуждение вынесено в отдельную ветку: http://webew.ru/posts/5608.webew
То, что не убивает нас, делает нас инвалидами.
11.04.2016, 12:33
Ответить
Добавить комментарий
Отображение комментариев: Древовидное | Плоское
© 2008—2017 webew.ru, связаться: x собака webew.ru
Сайт использует Flede и соответствует стандартам WAI-WCAG 1.0 на уровне A.
Rambler's Top100

Реклама: