470 lines
12 KiB
PHP
470 lines
12 KiB
PHP
<?php
|
|
/**
|
|
* @package Joomla.Component.Builder
|
|
*
|
|
* @created 4th September, 2022
|
|
* @author Llewellyn van der Merwe <https://dev.vdm.io>
|
|
* @git Joomla Component Builder <https://git.vdm.dev/joomla/Component-Builder>
|
|
* @copyright Copyright (C) 2015 Vast Development Method. All rights reserved.
|
|
* @license GNU General Public License version 2 or later; see LICENSE.txt
|
|
*/
|
|
|
|
namespace VDM\Joomla\Database;
|
|
|
|
|
|
use VDM\Joomla\Utilities\ArrayHelper;
|
|
use VDM\Joomla\Interfaces\LoadInterface;
|
|
use VDM\Joomla\Abstraction\Database;
|
|
|
|
|
|
/**
|
|
* Database Load
|
|
*
|
|
* @since 3.2.0
|
|
*/
|
|
final class Load extends Database implements LoadInterface
|
|
{
|
|
/**
|
|
* Load data rows as an array of associated arrays
|
|
*
|
|
* @param array $select Array of selection keys
|
|
* @param array $tables Array of tables to search
|
|
* @param array|null $where Array of where key=>value match exist
|
|
* @param array|null $order Array of how to order the data
|
|
* @param int|null $limit Limit the number of values returned
|
|
*
|
|
* @return array|null
|
|
* @since 3.2.0
|
|
**/
|
|
public function rows(array $select, array $tables, ?array $where = null,
|
|
?array $order = null, ?int $limit = null): ?array
|
|
{
|
|
// set key if found
|
|
$key = '';
|
|
if (isset($select['key']))
|
|
{
|
|
if (is_string($select['key']))
|
|
{
|
|
$key = $select['key'];
|
|
}
|
|
unset($select['key']);
|
|
}
|
|
|
|
// check if we can get many rows
|
|
if ($this->many($select, $tables, $where, $order, $limit))
|
|
{
|
|
// return associated arrays from the table records
|
|
return $this->db->loadAssocList($key);
|
|
}
|
|
|
|
// data does not exist
|
|
return null;
|
|
}
|
|
|
|
/**
|
|
* Load data rows as an array of objects
|
|
*
|
|
* @param array $select Array of selection keys
|
|
* @param array $tables Array of tables to search
|
|
* @param array|null $where Array of where key=>value match exist
|
|
* @param array|null $order Array of how to order the data
|
|
* @param int|null $limit Limit the number of values returned
|
|
*
|
|
* @return array|null
|
|
* @since 3.2.0
|
|
**/
|
|
public function items(array $select, array $tables, ?array $where = null,
|
|
?array $order = null, ?int $limit = null): ?array
|
|
{
|
|
// set key if found
|
|
$key = '';
|
|
if (isset($select['key']))
|
|
{
|
|
if (is_string($select['key']))
|
|
{
|
|
$key = $select['key'];
|
|
}
|
|
unset($select['key']);
|
|
}
|
|
|
|
// check if we can get many rows
|
|
if ($this->many($select, $tables, $where, $order, $limit))
|
|
{
|
|
// return associated arrays from the table records
|
|
return $this->db->loadObjectList($key);
|
|
}
|
|
|
|
// data does not exist
|
|
return null;
|
|
}
|
|
|
|
/**
|
|
* Load data row as an associated array
|
|
*
|
|
* @param array $select Array of selection keys
|
|
* @param array $tables Array of tables to search
|
|
* @param array|null $where Array of where key=>value match exist
|
|
* @param array|null $order Array of how to order the data
|
|
*
|
|
* @return array|null
|
|
* @since 3.2.0
|
|
**/
|
|
public function row(array $select, array $tables, ?array $where = null, ?array $order = null): ?array
|
|
{
|
|
// check if we can get one row
|
|
if ($this->one($select, $tables, $where, $order))
|
|
{
|
|
return $this->db->loadAssoc();
|
|
}
|
|
|
|
// data does not exist
|
|
return null;
|
|
}
|
|
|
|
/**
|
|
* Load data row as an object
|
|
*
|
|
* @param array $select Array of selection keys
|
|
* @param array $tables Array of tables to search
|
|
* @param array|null $where Array of where key=>value match exist
|
|
* @param array|null $order Array of how to order the data
|
|
*
|
|
* @return object|null
|
|
* @since 3.2.0
|
|
**/
|
|
public function item(array $select, array $tables, ?array $where = null, ?array $order = null): ?object
|
|
{
|
|
// check if we can get one row
|
|
if ($this->one($select, $tables, $where, $order))
|
|
{
|
|
return $this->db->loadObject();
|
|
}
|
|
|
|
// data does not exist
|
|
return null;
|
|
}
|
|
|
|
/**
|
|
* Get the max value based on a filtered result from a given table
|
|
*
|
|
* @param string $field The field key
|
|
* @param string $tables The tables
|
|
* @param array $filter The filter keys
|
|
*
|
|
* @return int|null
|
|
* @since 3.2.0
|
|
**/
|
|
public function max($field, array $tables, array $filter): ?int
|
|
{
|
|
// only do check if we have the table set
|
|
if (isset($tables['a']))
|
|
{
|
|
// get the query
|
|
$query = $this->query(["all" => "MAX(`$field`)"], $tables, $filter);
|
|
|
|
// Load the max number
|
|
$this->db->setQuery($query);
|
|
$this->db->execute();
|
|
|
|
// check if we have values
|
|
if ($this->db->getNumRows())
|
|
{
|
|
return (int) $this->db->loadResult();
|
|
}
|
|
}
|
|
|
|
// data does not exist
|
|
return null;
|
|
}
|
|
|
|
/**
|
|
* Count the number of items based on filter result from a given table
|
|
*
|
|
* @param string $tables The table
|
|
* @param array $filter The filter keys
|
|
*
|
|
* @return int|null
|
|
* @since 3.2.0
|
|
**/
|
|
public function count(array $tables, array $filter): ?int
|
|
{
|
|
// only do check if we have the table set
|
|
if (isset($tables['a']))
|
|
{
|
|
// get the query
|
|
$query = $this->query(["all" => 'COUNT(*)'], $tables, $filter);
|
|
|
|
// Load the max number
|
|
$this->db->setQuery($query);
|
|
$this->db->execute();
|
|
|
|
// check if we have values
|
|
if ($this->db->getNumRows())
|
|
{
|
|
return (int) $this->db->loadResult();
|
|
}
|
|
}
|
|
|
|
// data does not exist
|
|
return null;
|
|
}
|
|
|
|
/**
|
|
* Load one value from a row
|
|
*
|
|
* @param array $select Array of selection keys
|
|
* @param array $tables Array of tables to search
|
|
* @param array|null $where Array of where key=>value match exist
|
|
* @param array|null $order Array of how to order the data
|
|
*
|
|
* @return mixed
|
|
* @since 3.2.0
|
|
**/
|
|
public function value(array $select, array $tables, ?array $where = null, ?array $order = null)
|
|
{
|
|
// check if we can get one value
|
|
if ($this->one($select, $tables, $where, $order))
|
|
{
|
|
return $this->db->loadResult();
|
|
}
|
|
|
|
// data does not exist
|
|
return null;
|
|
}
|
|
|
|
/**
|
|
* Load many
|
|
*
|
|
* @param array $select Array of selection keys
|
|
* @param array $tables Array of tables to search
|
|
* @param array|null $where Array of where key=>value match exist
|
|
* @param array|null $order Array of how to order the data
|
|
* @param int|null $limit Limit the number of values returned
|
|
*
|
|
* @return bool
|
|
* @since 3.2.0
|
|
**/
|
|
protected function many(array $select, array $tables, ?array $where = null,
|
|
?array $order = null, ?int $limit = null): bool
|
|
{
|
|
// only do check if we have the table set
|
|
if (isset($tables['a']))
|
|
{
|
|
// get the query
|
|
$query = $this->query($select, $tables, $where, $order, $limit);
|
|
|
|
// Load the items
|
|
$this->db->setQuery($query);
|
|
$this->db->execute();
|
|
|
|
// check if we have values
|
|
if ($this->db->getNumRows())
|
|
{
|
|
return true;
|
|
}
|
|
}
|
|
|
|
// data does not exist
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* Load one
|
|
*
|
|
* @param array $select Array of selection keys
|
|
* @param array $tables Array of tables to search
|
|
* @param array|null $where Array of where key=>value match exist
|
|
* @param array|null $order Array of how to order the data
|
|
*
|
|
* @return bool
|
|
* @since 3.2.0
|
|
**/
|
|
protected function one(array $select, array $tables, ?array $where = null, ?array $order = null): bool
|
|
{
|
|
// only do check if we have the table set
|
|
if (isset($tables['a']))
|
|
{
|
|
// get the query
|
|
$query = $this->query($select, $tables, $where, $order);
|
|
|
|
// Load the item
|
|
$this->db->setQuery($query, 0, 1);
|
|
$this->db->execute();
|
|
|
|
// check if we have values
|
|
if ($this->db->getNumRows())
|
|
{
|
|
return true;
|
|
}
|
|
}
|
|
|
|
// data does not exist
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* Get the query object
|
|
*
|
|
* @param array $select Array of selection keys
|
|
* @param array $tables Array of tables to search
|
|
* @param array|null $where Array of where key=>value match exist
|
|
* @param array|null $order Array of how to order the data
|
|
* @param int|null $limit Limit the number of values returned
|
|
*
|
|
* @return object|null The query object (DatabaseQuery)
|
|
* @since 3.2.0
|
|
**/
|
|
protected function query(array $select, array $tables, ?array $where = null,
|
|
?array $order = null, ?int $limit = null): ?object
|
|
{
|
|
$query = $this->db->getQuery(true);
|
|
|
|
// check if we have an all selection set
|
|
if (isset($select['all']))
|
|
{
|
|
// all selection example array: ['all' => ['a.*', 'b.*']]
|
|
if (ArrayHelper::check($select['all']))
|
|
{
|
|
foreach ($select['all'] as $select_all)
|
|
{
|
|
// set target selection
|
|
$query->select(
|
|
$select_all
|
|
);
|
|
}
|
|
}
|
|
// all selection example string: ['all' =>'a.*']
|
|
elseif (is_string($select['all']))
|
|
{
|
|
// set target selection
|
|
$query->select(
|
|
$select['all']
|
|
);
|
|
}
|
|
unset($select['all']);
|
|
}
|
|
|
|
// load the table where join
|
|
if (ArrayHelper::check($select))
|
|
{
|
|
// set target selection
|
|
$query->select(
|
|
$this->db->quoteName(
|
|
array_keys($select),
|
|
array_values($select)
|
|
)
|
|
);
|
|
}
|
|
|
|
// set main table
|
|
$query->from($this->db->quoteName($this->getTable($tables['a']), 'a'));
|
|
|
|
// remove main table
|
|
unset($tables['a']);
|
|
|
|
// load the table where join
|
|
if (ArrayHelper::check($tables))
|
|
{
|
|
foreach ($tables as $as => $table)
|
|
{
|
|
$query->join(
|
|
'LEFT', $this->db->quoteName(
|
|
$this->getTable($table['name']), $as
|
|
) . ' ON (' . $this->db->quoteName($table['join_on'])
|
|
. ' = ' . $this->db->quoteName($table['as_on']) . ')'
|
|
);
|
|
}
|
|
}
|
|
|
|
// load the table where getters
|
|
if (ArrayHelper::check($where))
|
|
{
|
|
foreach ($where as $key => $value)
|
|
{
|
|
if (ArrayHelper::check($value))
|
|
{
|
|
if (isset($value['value']) && isset($value['operator']))
|
|
{
|
|
// check if value needs to be quoted
|
|
$quote = $value['quote'] ?? true;
|
|
if (!$quote)
|
|
{
|
|
if (ArrayHelper::check($value['value']))
|
|
{
|
|
// add the where by array
|
|
$query->where($this->db->quoteName($key) . ' ' .
|
|
$value['operator'] . ' (' .
|
|
implode(',', $value['value'])
|
|
. ')'
|
|
);
|
|
}
|
|
else
|
|
{
|
|
// add the where
|
|
$query->where($this->db->quoteName($key) . ' ' .
|
|
$value['operator'] . ' ' . $value['value']);
|
|
}
|
|
}
|
|
else
|
|
{
|
|
if (ArrayHelper::check($value['value']))
|
|
{
|
|
// add the where by array
|
|
$query->where($this->db->quoteName($key) . ' ' .
|
|
$value['operator'] . ' (' .
|
|
implode(',',
|
|
array_map(
|
|
fn($val) => $this->quote($val),
|
|
$value['value']
|
|
)
|
|
)
|
|
. ')'
|
|
);
|
|
}
|
|
else
|
|
{
|
|
// add the where
|
|
$query->where($this->db->quoteName($key) . ' ' .
|
|
$value['operator'] . ' ' . $this->quote($value['value']));
|
|
}
|
|
}
|
|
}
|
|
else
|
|
{
|
|
// we should through an exception
|
|
// for security we just return nothing for now
|
|
return null;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
// add the where
|
|
$query->where($this->db->quoteName($key) .
|
|
' = ' . $this->quote($value));
|
|
}
|
|
}
|
|
}
|
|
|
|
// load the row ordering
|
|
if (ArrayHelper::check($order))
|
|
{
|
|
foreach ($order as $key => $direction)
|
|
{
|
|
// add the ordering
|
|
$query->order($this->db->quoteName($key) .
|
|
' ' . $direction);
|
|
}
|
|
}
|
|
|
|
// only return a limited number
|
|
if (is_numeric($limit))
|
|
{
|
|
$query->setLimit($limit);
|
|
}
|
|
|
|
return $query;
|
|
}
|
|
|
|
}
|
|
|