Сайт справки

JDatabaseDriver – выборка данных и получение результата

Перед прочтением этого материала рекомендуем вам ознакомиться со следующими материалами:

Содержание

Вступление

Работу с базой данных условно можно разделить на две группы:

  • выборка данных и получение результата
  • вставка, обновление и удаление данных

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

Вставка, обновление и удаление данных рассматривается в этом материале.

Запрос

Перед тем, как начать работу с базой данных, нам необходимо получить экземпляр класса JDatabaseDriver (объект коннектора базы данных) и экземпляр класса JDatabaseQuery (объект запросов):

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

aa

Выборка данных из одной таблицы

Вот как выглядит выборка данных из одной таблицы:

// Получить подключение к базе данных.
$db = JFactory::getDbo();
  
// Создать новый объект запроса.
$query = $db->getQuery(true);
  
// Выбрать все записи из таблицы профиля пользователя,
// где ключ начинается с "custom.".
// Упорядочить по полю ordering .
$query->select($db->quoteName(
							[
								'user_id', 
								'profile_key', 
								'profile_value', 
								'ordering'
							]
						));
$query->from($db->quoteName('#__user_profiles'));
$query->where($db->quoteName('profile_key') . ' LIKE ' . $db->quote('custom.%'));
$query->order('ordering ASC');
  
// Сбросить запрос, используя наш недавно заполненный объект запроса.
$db->setQuery($query);
  
// Загрузить результаты в виде списка объектов stdClass 
// (дополнительные параметры извлечения данных см. Ниже).
$results = $db->loadObjectList();

(Здесь функция quoteName() добавляет соответствующие кавычки вокруг имен столбцов, чтобы избежать конфликтов с любым зарезервированным словом базы данных, сейчас или в будущем.)

Запрос также может быть объединен для дальнейшего упрощения:

$query
    ->select($db->quoteName(
				[
					'user_id', 
					'profile_key', 
					'profile_value', 
					'ordering'
				]
		))
    ->from($db->quoteName('#__user_profiles'))
    ->where($db->quoteName('profile_key') . ' LIKE ' . $db->quote('custom.%'))
    ->order('ordering ASC');

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

Группировка может быть достигнута очень просто. Следующий запрос будет подсчитывать количество статей в каждой категории.

$query
    ->select(array('catid', 'COUNT(*)'))
    ->from($db->quoteName('#__content'))
    ->group($db->quoteName('catid'));

Ограничение может быть установлено на запрос с помощью «setLimit». Например, в следующем запросе будет возвращено до 10 записей.

$query
    ->select($db->quoteName(
				[
					'user_id', 
					'profile_key', 
					'profile_value', 
					'ordering'
				]
			))
    ->from($db->quoteName('#__user_profiles'))
    ->setLimit('10');

Выборка данных из нескольких таблиц

Используя методы JDatabaseQuery join, мы можем выбирать записи из нескольких связанных таблиц. Общий метод "join" принимает два аргумента; тип соединения (inner, outer, left, right) и условие соединения. В следующем примере вы заметите, что мы можем использовать все ключевые слова, которые мы обычно использовали бы, если бы писали собственный SQL-запрос, включая ключевое слово AS для псевдонимов таблиц и ключевое слово ON для создания связей между таблицами. Также обратите внимание, что псевдоним таблицы используется во всех методах, которые ссылаются на столбцы таблицы (select, where, order).

// Получить подключение к БД.
$db = JFactory::getDbo();
 
// Создать новый объект запроса.
$query = $db->getQuery(true);
 
// Выбрать все статьи для пользователей, чье имя начинается с «а».
// Упорядочить по дате создания.
// Обратите внимание, что если поставить «a» в качестве второго параметра, 
// будет сгенерировано `#__content` AS `a`
$query
    ->select(array('a.*', 'b.username', 'b.name'))
    ->from($db->quoteName('#__content', 'a'))
    ->join('INNER', $db->quoteName('#__users', 'b') . 
		' ON ' . $db->quoteName('a.created_by') . 
		' = ' . $db->quoteName('b.id'))
    ->where($db->quoteName('b.username') . ' LIKE ' . $db->quote('a%'))
    ->order($db->quoteName('a.created') . ' DESC');
 
// Сбросить запрос, используя наш недавно заполненный объект запроса.
$db->setQuery($query);
 
// Загрузить результаты в виде списка объектов stdClass 
// (дополнительные параметры получения данных см. ниже).
$results = $db->loadObjectList();

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

Мы можем использовать несколько объединений для запроса более чем двух таблиц:

$query
    ->select(array('a.*', 'b.username', 'b.name', 'c.*', 'd.*'))
    ->from($db->quoteName('#__content', 'a'))
    ->join('INNER', $db->quoteName('#__users', 'b') . ' ON ' . 
		$db->quoteName('a.created_by') . ' = ' . $db->quoteName('b.id'))
    ->join('LEFT', $db->quoteName('#__user_profiles', 'c') . ' ON ' . 
		$db->quoteName('b.id') . ' = ' . $db->quoteName('c.user_id'))
    ->join('RIGHT', $db->quoteName('#__categories', 'd') . ' ON ' . 
		$db->quoteName('a.catid') . ' = ' . $db->quoteName('d.id'))
    ->where($db->quoteName('b.username') . ' LIKE ' . $db->quote('a%'))
    ->order($db->quoteName('a.created') . ' DESC');

Обратите внимание, как сцепление делает исходный код намного более читабельным для этих более длинных запросов.

В некоторых случаях вам также потребуется использовать предложение AS при выборе элементов, чтобы избежать конфликтов имен столбцов. В этом случае несколько операторов выбора могут быть объединены в цепочку с использованием второго параметра $db->quoteName.

$query
    ->select('a.*')
    ->select($db->quoteName('b.username', 'username'))
    ->select($db->quoteName('b.name', 'name'))
    ->from($db->quoteName('#__content', 'a'))
    ->join('INNER', $db->quoteName('#__users', 'b') . ' ON ' . 
		$db->quoteName('a.created_by') . ' = ' . $db->quoteName('b.id'))
    ->where($db->quoteName('b.username') . ' LIKE ' . $db->quote('a%'))
    ->order($db->quoteName('a.created') . ' DESC');

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

$query
    ->select(array('a.*'))
    ->select($db->quoteName(array('b.username', 'b.name'), array('username', 'name')))
    ->from($db->quoteName('#__content', 'a'))
    ->join('INNER', $db->quoteName('#__users', 'b') . ' ON ' . 
		$db->quoteName('a.created_by') . ' = ' . $db->quoteName('b.id'))
    ->where($db->quoteName('b.username') . ' LIKE ' . $db->quote('a%'))
    ->order($db->quoteName('a.created') . ' DESC');

Использование OR в запросах.

При использовании нескольких предложений WHERE в запросе они будут обрабатываться как AND.

Так, например, приведенный ниже запрос вернет результаты, в которых поля 'name' И поле 'state' совпадают.

$query = $db
    ->getQuery(true)
    ->select('COUNT(*)')
    ->from($db->quoteName('#__my_table'))
    ->where($db->quoteName('name') . " = " . $db->quote($value)
    ->where($db->quoteName('state') . " = " . $db->quote($state));

Чтобы использовать предложение WHERE в качестве OR, запрос можно записать следующим образом.

$query = $db
    ->getQuery(true)
    ->select('COUNT(*)')
    ->from($db->quoteName('#__my_table'))
    ->where($db->quoteName('name') . " = " . $db->quote($name_one), 'OR')
    ->where($db->quoteName('name') . " = " . $db->quote($name_two));

это также может быть написано следующим образом.

 

$query = $db
    ->getQuery(true)
    ->select('COUNT(*)')
    ->from($db->quoteName('#__my_table'))
    ->where($db->quoteName('name') . " = " . $db->quote($name_one)
    ->orWhere($db->quoteName('name') . " = " . $db->quote($name_two));

 

Выборка данных с подзапросом.

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

$subQuery = $db->getQuery(true);
$query    = $db->getQuery(true);
 
// Составляем подзапрос
$subQuery->select('*')
   ->from($db->quoteName('#__sub_table'))
   ->where($db->quoteName('subTest') . ' = 1');
 
// Составляем основной запрос
$query->select('*')
    ->from($db->quoteName('#__table'))
    ->where($db->quoteName('state') . ' = 1')
    ->where($db->quoteName('subCheckIn') . ' IN (' . $subQuery . ')')
    ->order($db->quoteName('ordering') . ' ASC');
 
// Устанавливаем запрос
$db->setQuery($query);

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

SELECT * 
FROM `#__table` 
WHERE `state` = 1 
	AND `subCheckIn` IN 
	( 
		SELECT * 
		FROM `#__sub_table` 
		WHERE `subTest` = 1
	) 
ORDER BY `ordering` ASC

Обратите внимание, что объект $subQuery автоматически был преобразован в строку благодаря методу __toString() класса JDatabaseQuery.

Получение результата

Класс для базы данных содержит много методов для работы с набором результатов запроса.

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

Передоложим, что у нас в таблице #__users есть три записи:

id, name, username, email, ...
837, 'Ольга', 'joomla', 'joomla[]test.ru', ...
838, 'Василий', 'demo', 'demo[]test.ru', ...
839, 'Дмитрий', 'dima', 'dima[]test.ru', ...

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

Результат с единичным значением

loadResult()

Метод loadResult() используется для получения единичного значения, например значение единичного поля из единичной строки таблицы (или из первой найденной строки).

Это часто является результатом запроса 'count' для получения нескольких записей:

$db = JFactory::getDbo();
$query = $db
    ->getQuery(true)
    ->select('COUNT(*)')
    ->from($db->quoteName('#__users'))
    ->where($db->quoteName('name') . " = " . $db->quote($value));
 
// Сбросить запрос, используя наш недавно заполненный объект запроса.
$db->setQuery($query);
$count = $db->loadResult();
или где вы просто ищете одно поле из одной строки таблицы (или, возможно, одно поле из первой строки возврата).
$query->select($db->quoteName('username'))
    ->from($db->quoteName('#__users'))
    ->where($db->quoteName('id') . ' = 839');
// Сбросить запрос, используя наш недавно заполненный объект запроса.
$db->setQuery($query);
$result = $db->loadResult();

Результат:

dima

Результат с единичной строкой

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

loadRow()

Метод loadRow() возвращает индексированный массив единичной строки таблицы. Индексы массива пронумерованы и начинаются с нуля.

$query
    ->select(
        $db->quoteName(
            array('username', 'name', 'email')
        )
    )
    ->from($db->quoteName('#__users'))
    ->where($db->quoteName('id') . ' = 839');
 
$db->setQuery($query);
$result = $db->loadRow();

Результат:

Array
(
    [0] => dima
    [1] => Дмитрий
    [2] => dima[]test.ru
)

loadAssoc()

Метод loadAssoc() возвращает ассоциированный массив единичной строки таблицы. Индексами массива являются названия полей.

...
$db->setQuery($query);
$result = $db->loadAssoc();

Результат:

Array
(
    [username] => dima
    [name] => Дмитрий
    [email] => dima[]test.ru
)

loadObject($class = 'stdClass')

Метод loadObject() возвращает PHP-объект единичной строки таблицы.

...
$db->setQuery($query);
$result = $db->loadObject();

Результат:

stdClass Object
(
    [username] => dima
    [name] => Дмитрий
    [email] => dima[]test.ru
)

Параметр $class позволяет задать имя класса объекта. Будет создан экземпляр указанного класса и заполнен полученными свойствами.

Результат с единичной колонкой

Каждый из этих методов возвращает единичную колонку из базы данных.

loadColumn($offset = 0)

Метод loadColumn() возвращает индексированный массив единичной колонки таблицы. Индексы массива пронумерованы и начинаются с нуля.

$query->select($db->quoteName('username'))
    ->from($db->quoteName('#__users'));
 
$db->setQuery($query);
$result = $db->loadColumn();

Результат:

Array
(
    [0] => joomla
    [1] => demo
    [2] => dima
)

Если в выборке участвует несколько колонок, то при помощи параметра $offset можно задать, какая именно колонка попадет в результат.

$query
    ->select(
        $db->quoteName(
            array('username', 'name', 'email')
        )
    )
    ->from($db->quoteName('#__users'));
 
$db->setQuery($query);
$result = $db->loadColumn(2);

Результат:

Array
(
    [0] => joomla[]test.ru
    [1] => demo[]test.ru
    [2] => dima[]test.ru
)

Таким образом, в результат выборки попала третья колонка email.

Мы также можем производить итерацию по колонкам:

for ($i = 0; $i <= 2; $i++)
{
    $column = $db->loadColumn($i);
    print_r($column);
}

Результат:

Array
(
    [0] => joomla
    [1] => demo
    [2] => dima
)
 
Array
(
    [0] => Ольга
    [1] => Василий
    [2] => Дмитрий
)
 
Array
(
    [0] => joomla[]test.ru
    [1] => demo[]test.ru
    [2] => dima[]test.ru
)

Результат с несколькими строками

Каждый из этих методов возвращает несколько строк из базы данных.

loadRowList($key = null)

Метод loadRowList() возвращает индексированный массив индексированных массивов из нескольких строк таблицы.

...
$db->setQuery($query);
$result = $db->loadRowList();

Результат:

Array
(
    [0] => Array
        (
            [0] => joomla
            [1] => Ольга
            [2] => joomla[]test.ru
        )
 
    [1] => Array
        (
            [0] => demo
            [1] => Василий
            [2] => demo[]test.ru
        )
 
    [2] => Array
        (
            [0] => dima
            [1] => Дмитрий
            [2] => dima[]test.ru
        )
)

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

...
$db->setQuery($query);
$result = $db->loadRowList(0);

Результат:

Array
(
    [joomla] => Array
        (
            [0] => joomla
            [1] => Ольга
            [2] => joomla[]test.ru
        )
 
    [demo] => Array
        (
            [0] => demo
            [1] => Василий
            [2] => demo[]test.ru
        )
 
    [dima] => Array
        (
            [0] => dima
            [1] => Дмитрий
            [2] => dima[]test.ru
        )
)

Мы видим, что ключом массива стало первое поле username.

loadAssocList($key = null, $column = null)

Метод loadAssocList() возвращает индексированный массив ассоциированных массивов из нескольких строк таблицы.

...
$db->setQuery($query);
$result = $db->loadAssocList();

Результат:

Array
(
    [0] => Array
        (
            [username] => joomla
            [name] => Ольга
            [email] => joomla[]test.ru
        )
 
    [1] => Array
        (
            [username] => demo
            [name] => Василий
            [email] => demo[]test.ru
        )
 
    [2] => Array
        (
            [username] => dima
            [name] => Дмитрий
            [email] => dima[]test.ru
        )
)

Параметр $key позволяет нам задать имя ключа массивов. Ключом должно быть валидное имя колонки.

...
$db->setQuery($query);
$result = $db->loadAssocList('username');

Результат:

Array
(
    [joomla] => Array
        (
            [username] => joomla
            [name] => Ольга
            [email] => joomla[]test.ru
        )
 
    [demo] => Array
        (
            [username] => demo
            [name] => Василий
            [email] => demo[]test.ru
        )
 
    [dima] => Array
        (
            [username] => dima
            [name] => Дмитрий
            [email] => dima[]test.ru
        )
)

Если задан параметр $column, то в результат попадают записи с указанным в параметре именем колонки.

...
$db->setQuery($query);
$result = $db->loadAssocList('username', 'email');

Результат:

Array
(
    [joomla] => joomla[]test.ru
    [demo] => demo[]test.ru
    [dima] => dima[]test.ru
)

loadObjectList($key = '', $class = 'stdClass')

Метод loadObjectList() возвращает индексированный массив PHP-объектов из нескольких строк таблицы.

...
$db->setQuery($query);
$result = $db->loadObjectList();

Результат:

Array
(
    [0] => stdClass Object
        (
            [username] => joomla
            [name] => Ольга
            [email] => joomla[]test.ru
        )
 
    [1] => stdClass Object
        (
            [username] => demo
            [name] => Василий
            [email] => demo[]test.ru
        )
 
    [2] => stdClass Object
        (
            [username] => dima
            [name] => Дмитрий
            [email] => dima[]test.ru
        )
)

Параметр $key позволяет нам задать имя ключа массивов. Ключом должно быть валидное имя колонки.

...
$db->setQuery($query);
$result = $db->loadObjectList('username');

Результат:

Array
(
    [joomla] => stdClass Object
        (
            [username] => joomla
            [name] => Ольга
            [email] => joomla[]test.ru
        )
 
    [demo] => stdClass Object
        (
            [username] => demo
            [name] => Василий
            [email] => demo[]test.ru
        )
 
    [dima] => stdClass Object
        (
            [username] => dima
            [name] => Дмитрий
            [email] => dima[]test.ru
        )
)

Параметр $class позволяет задать имя класса объекта. Будут созданы экземпляры указанного класса и заполнены полученными свойствами.

Дополнительные методы получения результатов.

getNumRows()

getNumRows() вернет количество строк результата, найденных последним запросом SELECT или SHOW и ожидающих чтения. Чтобы получить результат от getNumRows(), вы должны запустить ее после запроса и до получения результатов. Чтобы получить количество строк, затронутых запросом INSERT, UPDATE, REPLACE или DELETE, используйте getAffectedRows().

. . .
$db->setQuery($query);
$db->execute();
$num_rows = $db->getNumRows();
print_r($num_rows);
$result = $db->loadRowList();

вернёт

3

Примечание: getNumRows() действительна только для операторов типа SELECT или SHOW, которые возвращают фактический набор результатов. Если вы запустите getNumRows() после loadRowList() - или любого другого метода извлечения - вы получите предупреждение PHP:

 

Warning: mysql_num_rows(): 80 is not a valid MySQL result resource 
in libraries\joomla\database\database\mysql.php on line 344

 

Пример Кода модуля

Ниже приведен код простого модуля Joomla, который вы можете установить и запустить для демонстрации использования функциональности JDatabase, и который вы можете адаптировать для экспериментов с некоторыми концепциями, описанными выше. Если вы не знаете, как разработать и установить модуль Joomla, то вам поможет руководство по созданию простого модуля.

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

В папке mod_db_select создайте следующие 2 файла:

mod_db_select.xml

<?xml version="1.0" encoding="utf-8"?>
<extension type="module" version="3.1" client="site" method="upgrade">
    <name>Database select query demo</name>
    <version>1.0.1</version>
    <description>Code demonstrating use of Joomla Database class to 
	perform SQL SELECT queries</description>
    <files>
        <filename module="mod_db_select">mod_db_select.php</filename>
    </files>
</extension>
mod_db_select.php
<?php
defined('_JEXEC') or die('Restricted Access');
 
use Joomla\CMS\Factory;
 
$db = Factory::getDbo();
 
$me = Factory::getUser();
 
$query = $db->getQuery(true);
 
$query->select($db->quoteName(array('name', 'email')))
    ->from($db->quoteName('#__users'))
    ->where($db->quoteName('id') . ' != ' . $db->quote($me->id))
    ->order($db->quoteName('name') . ' ASC');
 
$db->setQuery($query);
 
echo $db->replacePrefix((string) $query);
 
$results = $db->loadAssocList();
 
foreach ($results as $row) {
    echo "<p>" . $row['name'] . ", " . $row['email'] . "<br></p>";
}

Приведенный выше код выбирает и выводит имя пользователя и электронную почту записей в таблице пользователей Joomla, кроме записей текущего пользователя, вошедшего в систему. Метод Factory::getUser() возвращает объект user текущего вошедшего в систему пользователя, а если он не вошел, то пустой объект user, поле id которого установлено в ноль.

Выражение $db->replacePrefix((string) $query) возвращает фактический SQL-запрос, и его вывод может быть полезен при отладке.

Заархивируйте каталог mod_db_select для создания mod_db_select.zip .

В вашем администраторе Joomla перейдите в раздел Установка расширений и на вкладке Загрузка файла пакета выберите этот zip-файл, чтобы установить этот пример модуля журнала.

Сделайте этот модуль видимым, отредактировав его (нажмите на него на странице Модулей), затем:

  1. придание своему статусу Published
  2. выбор позиции на странице для ее отображения
  3. на вкладке назначение меню укажите страницы, на которых оно должно отображаться

Когда вы посещаете веб-страницу сайта, вы должны увидеть модуль в выбранной вами позиции, и он должен вывести инструкцию SQL SELECT и последовательность значений name, email из таблицы пользователей Joomla.

Смотрите также

Наверх

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