Сайт справки

JDatabaseQuery - конструктор SQL-запросов

Перед прочтением этого материала советуем вам ознакомиться с материалом JDatabase – абстрактный уровень базы данных.

Содержание

Общая информация

Абстрактный класс JDatabaseQuery позволяет создавать SQL-запросы в объектно-ориентированном виде и является составляющей как Joomla! CMS, так и Joomla! Framework. Его методы инкапсулируют язык запросов, скрывая специфический синтаксис от разработчика и увеличивая гибкость кода. Методы могут складываться в цепочку, один за другим, что значительно упрощает код и улучшает его читаемость. Все это напоминает конструктор, где отдельные детали (методы) образуют единую конструкцию (запрос).

Именно объектно-ориентированный поход более предпочтителен при разработке расширений.

В CMS класс JDatabaseQuery расположен в /libraries/joomla/database/query.php, а конкретные реализации для различных движков базы данных в /libraries/joomla/database/query. Вот как выглядит диаграмма наследования класса JDatabaseQuery:

 

Диаграмма наследования класса JDatabaseQuery
 

Для получения объекта запросов необходимо использовать метод getQuery() класса JDatabaseDriver:

// Получаем объект коннектора базы данных (JDatabaseDriver)
$db = JFactory::getDbo();
 
// Получам объект запросов (JDatabaseQuery)
$query = $db->getQuery(true);

Метод getQuery() получает текущий объект запроса (либо строку запроса) или новый объект запроса, если параметр установлен в true.

Составление запросов

Предположим, что нам необходимо выбрать из таблицы #__content все опубликованные записи. Вот как будет выглядеть запрос, составленный с помощью JDatabaseQuery:

$query->select('*');
$query->from($db->quoteName('#__content'));
$query->where($db->quoteName('state') . ' = 1');

Полученный SQL-запрос:

SELECT * FROM `#__content` WHERE `state` = 1

Для сокращения написания кода мы можем использовать цепочку методов:

$query->select('*')
    ->from($db->quoteName('#__content'))
    ->where($db->quoteName('state') . ' = 1');

Или вот такой полный вариант с получением объекта запроса:

$query = JFactory::getDbo()
    ->getQuery(true)
    ->select('*')
    ->from($db->quoteName('#__content'))
    ->where($db->quoteName('state') . ' = 1');

Допустим, мы хотим добавить к запросу выборку по создателю (created_by) с ID = 42. Для этого необходимо добавить еще один where() метод:

$query->select('*');
$query->from($db->quoteName('#__content'));
$query->where($db->quoteName('state') . ' = 1');
$query->where($db->quoteName('created_by') . ' = 42');

Полученный SQL-запрос:

SELECT * FROM `#__content` WHERE `state` = 1 AND `created_by` = 42

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

// Выбираем значения из #__content
$query->select('a.*');
$query->from($db->quoteName('#__content', 'a'));
$query->where($db->quoteName('a.state') . ' = 1');
$query->where($db->quoteName('a.created_by') . ' = 42');
 
// Присоединяем #__users
$query->select($db->quoteName('b.username'));
$query->leftJoin(
    $db->quoteName('#__users', 'b') 
    . ' ON ' 
    . $db->quoteName('b.id') 
    . ' = ' . $db->quoteName('a.created_by')
);
 
// Сортируем по title
$query->order($db->quoteName('a.title') . ' ASC');

В итоге мы все равно получаем корректно составленный SQL-запрос:

SELECT 
	`a`.*,
	`b`.`username` 
FROM 
	`#__content` AS `a` 
LEFT JOIN 
	`#__users` AS `b` ON `b`.`id` = `a`.`created_by` 
WHERE 
	`a`.`state` = 1 
	AND `a`.`created_by` = 42 
ORDER BY 
	`a`.`title` ASC

А вот как выглядит запрос на обновление данных в таблице:

$query->update($db->quoteName('#__users'))
    ->set(
        array(
            $db->quoteName('name') . ' = ' . $db->quote('Dima'), 
            $db->quoteName('email') . ' = ' . $db->quote('test[]test.ru')
        )
    )
    ->where($db->quoteName('id') . ' = 42');

Полученный SQL-запрос:

UPDATE `#__users` SET `name` = 'Dima' , `email` = 'test[]test.ru' WHERE `id` = 42

Вы никогда не должны смешивать в одном запросе select и insert, update, delete.

Основные методы для составления запросов

Давайте рассмотрим отдельно каждый из основных методов для составления запросов.

select($columns)

Метод добавляет одну колонку или массив колонок к оператору SELECT запроса. Метод может быть вызван несколько раз в одном и том же запросе:

$query->select('a.*')->select('b.id');
$query->select(array('a.*', 'b.id'));

SQL-запрос:

SELECT a.*,b.id

from($tables, $subQueryAlias = null)

Метод добавляет таблицу или массив таблиц к оператору FROM запроса. Несмотря на то, что можно передать массив, рекомендуется использовать явные присоединения (join).

$query->select('*')
    ->from('#__a');

SQL-запрос:

SELECT * FROM #__a

В качестве первого параметра может выступать объект JDatabaseQuery (или его дочерний класс) при использовании подзапроса в операторе FROM. В этом случае необходимо передать параметр $subQueryAlias, который будет являться алиасом подзапроса.

where($conditions, $glue = 'AND')

Метод добавляет одно условие или массив условий к оператору WHERE запроса.

$query->where('a = 1')->where('b = 2');
$query->where(array('a = 1', 'b = 2'));

SQL-запрос:

WHERE a = 1 AND b = 2

Параметр $glue является связующим оператором для соединения нескольких условий.

$query->where('a = 1', 'OR')->where('b = 2');

SQL-запрос:

WHERE a = 1 OR b = 2

Связующий оператор устанавливается при первом использовании и не может быть изменен.

$query->where('a = 1')->where('b = 2', 'OR')->where('c = 3');

SQL-запрос:

WHERE a = 1 AND b = 2 AND c = 3

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

$query->where('a = 1 OR b = 2 AND c = 3);

SQL-запрос:

WHERE a = 1 OR b = 2 AND c = 3

join($type, $conditions)

Метод добавляет к запросу оператор JOIN. Первый параметр – это тип присоединения, второй – условие присоединения или массив условий.

$query->join('LEFT', '#__b ON b.id = a.b_id')
    ->join('INNER', '#__c ON c.id = a.c_id');

SQL-запрос:

LEFT JOIN #__b ON b.id = a.b_id INNER JOIN #__c ON c.id = a.c_id
$query->join(
    'LEFT', array(
        '#__b ON b.id = a.b_id',
        '#__c ON c.id = a.c_id'
    )
);

SQL-запрос:

LEFT JOIN #__b ON b.id = a.b_id,#__c ON c.id = a.c_id

leftJoin(), rightJoin(), innerJoin(), outerJoin()

Методы предназначены для упрощения работы с оператором JOIN. Все они являются различными типами присоединения и принимают единственный параметр $condition - условие присоединения:

$query->leftJoin('#__b ON b.id = a.b_id')
    ->rightJoin('#__c ON c.id = a.c_id')
    ->innerJoin('#__d ON d.id = a.d_id')
    ->outerJoin('#__e ON e.id = a.e_id');

SQL-запрос:

SELECT 
	a.* 
FROM 
	#__a 
LEFT JOIN 
	#__b ON b.id = a.b_id 
RIGHT JOIN 
	#__c ON c.id = a.c_id 
INNER JOIN 
	#__d ON d.id = a.d_id 
OUTER JOIN 
	#__e ON e.id = a.e_id

order($columns)

Метод добавляет колонку или массив колонок сортировки к оператору ORDER BY запроса.

$query->order('id DESC')->order('title ASC ');
$query->order(array('id DESC', 'title ASC'));

SQL-запрос:

ORDER BY id DESC,title ASC

group($columns)

Метод добавляет колонку или массив колонок сортировки к оператору GROUP BY запроса.

$query->group('id')->group('title');
$query->group(array('id', 'title'));

SQL-запрос:

GROUP BY id,title

having($conditions, $glue = 'AND')

Метод добавляет условия или массив условий к оператору HAVING запроса.

$query->group('id')
    ->having('COUNT(id) > 5');

SQL-запрос:

GROUP BY id HAVING COUNT(id) > 5

Параметр $glue является связующим оператором для соединения нескольких условий.

$query->group('id')
    ->having('COUNT(id) > 5')
    ->having('COUNT(id) < 10');

SQL-запрос:

GROUP BY id HAVING COUNT(id) > 5 AND COUNT(id) < 10

Связующий оператор устанавливается при первом использовании и не может быть изменен.

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

$query->group('id')
    ->having('COUNT(id) > 5 OR COUNT(id) < 10);

SQL-запрос:

GROUP BY id HAVING COUNT(id) > 5 AND COUNT(id) < 10

update($table)

Метод добавляет таблицу к оператору UPDATE запроса.

$query->update('#__a');

SQL-запрос:

UPDATE #__a

set($conditions, $glue = ',')

Метод добавляет строку условия или массив строк к оператору SET запроса.

$query->set('a = 1')->set('b = 2');
$query->set(array('a = 1', 'b = 2');

SQL-запрос:

SET a = 1 , b = 2

Параметр $glue является связующим оператором для соединения нескольких условий.

Связующий оператор устанавливается при первом использовании и не может быть изменен.

insert($table, $incrementField=false)

Метод добавляет таблицу к оператору INSERT запроса. Параметр $incrementField позволяет задать имя поля, которое необходимо инкрементировать.

$query->insert('#__a');

SQL-запрос:

INSERT INTO #__a

Вместе с методом insert() применяются следующие методы:

columns($columns)

Метод добавляет колонку или массив колонок, которые будут использованы для оператора INSERT INTO.

values($values)

Добавляет кортеж или массив кортежей, которые будут использованы в качестве значений для оператора INSERT INTO.

$query->insert('#__a')
    ->columns('id, title')
    ->values('1,2');

SQL-запрос:

INSERT INTO #__a (id, title) VALUES (1,2)

Также вы можете использовать метод set():

$query->insert('#__a')
    ->set('a = 1*2');

SQL-запрос:

INSERT INTO #__a SET a = 1*2

delete($table = null)

Метод добавляет таблицу к оператору DELETE запроса.

$query->delete('#__a')
    ->where('id = 1');

SQL-запрос:

DELETE FROM #__a WHERE id = 1

Другие методы

clear($clause = null)

Метод очищает данные запроса. Предположим, мы составили запрос и выполнили его, а затем хотим составить еще один запрос для выполнения. Если не использовать метод clear(), то второй составленный запрос соединится с первым. Поэтому не забывайте использовать clear() между запросами:

$query->clear();

Если вы хотите очистить только определенную часть запроса (оператор), то передайте её через параметр $clause:

$query->clear('select');
$query->clear('values');

union($query, $distinct = false, $glue = '')

Добавляет запрос к оператору UNION для объединения с текущим запросом. Первый параметр – это строка или объект JDatabaseQuery, второй параметр – возвращать только разные строки или нет, третий параметр - связующий оператор для соединения нескольких условий.

Для возврата только разных строк можно использовать метод unionDistinct($query, $glue = ''), который добавляет запрос к оператору UNION DISTINCT для объединения с текущим запросом.

unionAll($query, $distinct = false, $glue = '')

Добавляет запрос к оператору UNION ALL для объединения с текущим запросом.

Доступен с Joomla 3.

Подробная документация по использованию методов union находится в процессе перевода.

В Joomla в плоть до версии 3.3 метод union не работает, поскольку в нем ошибка. Если требуется генерировать команду UNION им надо создать дочерний класс и переопределить в нем пару методов. Подробности в статье Исправление метода union класса JDatabaseQuery.

concatenate($values, $separator = null)

Метод производит конкатенацию массива имен колонок или имен переменных.

$query->select(
    $query->concatenate(
        array('a', 'b')
    )
);

SQL-запрос:

SELECT CONCAT(a,b)

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

$query->select(
    $query->concatenate(
        array('a', 'b'), ';'
    )
);

SQL-запрос:

SELECT CONCAT_WS(';', a, b)

dump()

Метод создает отформатированных дамп запроса для диагностики. При этом префикс '#__' заменяется на реальный префикс таблиц.

echo $query->dump();

charLength($field, $operator = null, $condition = null)

Метод получает количество символов в строке. Первый параметр – это значение поля. Второй параметр – оператор сравнения между числовым значением charLength() и параметром $condition. Третий параметр – числовое значение для сравнения со значением charLength(). Второй и третий параметры доступны с Joomla 3.

$query->select($query->charLength('a', '>', 3));

SQL-запрос:

CHAR_LENGTH(a)> 3

length($value)

Метод получает длину строки в байтах.

query->where($query->length('a').' > 3');

call($columns)

Метод добавляет колонку или массив колонок к оператору CALL запроса. Доступен с Joomla 3.

exec($columns)

Метод добавляет колонку или массив колонок, к оператору EXEC запроса. Доступен с Joomla 3.

format($format)

Метод находит и заменяет sprintf-токены в форматированной строке. Доступен с Joomla 3.

$query->format('SELECT %1$n FROM %2$n WHERE %3$n = %4$a', 'foo', '#__foo', 'bar', 1);

SQL-запрос:

SELECT `foo` FROM `#__foo` WHERE `bar` = 1

currentTimestamp()

Метод получает текущую дату и время.

$query->where('published_up < ' . $query->currentTimestamp());

dateAdd($date, $interval, $datePart)

Метод добавляет интервал к заданной дате. Доступен с Joomla 3.

Для извлечения отдельных частей из колонки типа datetime, можно воспользоваться соответствующими методами: year(), month(), day(), hour(), minute(), second(). Все они принимают один параметр – название колонки, которая содержит необходимые данные для извлечения. Эти методы доступны с Joomla 3.

$query->select(
    $query->month(
        $db->quoteName('dateColumn')
    )
);

Подробнее http://www.mysql.ru/docs/man/Date_and_time_functions.html

$query->update('#__a');

Наверх

© Сайт справки 2024