private function _buildQuery($revisionID = null)
{
$getRevision = $revisionID ?: $this->_queryBuilderFactory->getQueryBuilder()->select('MAX(revision_id)')->from('product_unit_info')->where('unit_id = product_unit.unit_id');
$this->_queryBuilder = $this->_queryBuilderFactory->getQueryBuilder()->select(['product_unit.product_id AS productID', 'product_unit.unit_id AS id', 'IFNULL(product_unit.weight_grams, product.weight_grams) AS weight', 'product_unit_info.sku AS sku', 'product_unit.barcode AS barcode', 'product_unit.visible AS visible', 'product_unit.created_at AS createdAt', 'product_unit.created_by AS createdBy', 'product_unit.updated_at AS updatedAt', 'product_unit.updated_by AS updatedBy', 'product_unit.deleted_at AS deletedAt', 'product_unit.deleted_by AS deletedBy', 'product_unit.supplier_ref AS supplierRef', 'IFNULL(product_unit_info.revision_id,1) AS revisionID', 'product_unit_info.sku AS sku', 'product_unit_stock.stock AS stock', 'product_unit_stock.location AS stockLocation', 'product_price.type AS priceType', 'product_price.currency_id AS currencyID', 'IFNULL(product_unit_price.price, product_price.price) AS price', 'product_unit_option.option_name AS optionName', 'product_unit_option.option_value AS optionValue'])->from('product_unit')->join('product', 'product_unit.product_id = product.product_id');
if (is_numeric($getRevision)) {
$this->_queryBuilder->leftJoin('product_unit_info', '
product_unit_info.unit_id = product_unit.unit_id AND
revision_id = :revisionID?i
')->addParams(['revisionID' => $getRevision]);
} else {
$this->_queryBuilder->leftJoin('product_unit_info', '
product_unit_info.unit_id = product_unit.unit_id AND
revision_id = (:revisionID?q)
')->addParams(['revisionID' => $getRevision]);
}
$this->_queryBuilder->leftJoin('product_unit_stock', 'product_unit.unit_id = product_unit_stock.unit_id')->leftJoin('product_price', 'product_unit.product_id = product_price.product_id')->leftJoin('product_unit_price', '
product_unit.unit_id = product_unit_price.unit_id AND
product_price.type = product_unit_price.type AND
product_price.currency_id = product_unit_price.currency_id
')->leftJoin('product_unit_option', '
product_unit_option.unit_id = product_unit.unit_id AND
product_unit_option.revision_id = product_unit_info.revision_id
');
if (!$this->_loadInvisible) {
$this->_queryBuilder->where('product_unit.visible = ?b', [true]);
}
if (!$this->_loadOutOfStock) {
$this->_queryBuilder->where('product_unit_stock.stock > 0');
}
if (!$this->_loadDeleted) {
$this->_queryBuilder->where('product_unit.deleted_at IS NULL');
}
}
/**
* Find results based on the search term
*
* @param string $term search terms
* @return array|File Array of File objects, or a single File object
*/
public function getBySearchTerm($term)
{
// Turn the terms into an array
$terms = explode(' ', $term);
// Set a bunch of arrays which are used below, seems a lot but it's
// becasue we have to pass through an array to the sql query so we have to do it twice
$whereName = [];
$whereTag = [];
// Loop over the terms and add them to an array to implode in the query
foreach ($terms as $key => $term) {
$whereName[] = ' file.name LIKE ?s';
$whereTag[] = ' file_tag.tag_name LIKE ?s';
$terms[$key] = '%' . trim($term) . '%';
}
$this->_setQueryBuilder();
$this->_queryBuilder->leftJoin('file_tag', 'file.file_id = file_tag.file_id')->where('(' . implode(' OR ', $whereName) . ' OR ' . implode(' OR ', $whereTag) . ')', array_merge($terms, $terms));
$this->_returnAsArray = true;
// Return the array of results.
return $this->_loadFromQuery();
}