Сортировка запроса

[artem2103]

Всем доброго времени суток! Подскажите пожалуйста, как в функции модели можно сделать сразу сортировку по какому-то из параметров, например по p.sort_order
 

public function getProductsByData( $colum = 'isbn', $dt = 1 ) {
        $product_data = array();

        $query = $this->db->query("SELECT p.*, p.product_id AS p_id, p.image AS p_image, pd.name AS pd_name, pd.description AS description, 
                               pd.tag AS tag, pd.meta_title AS m_title, pd.meta_description AS m_description, pd.meta_h1 AS m_h1, p2s.*, m.*, 
                   (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, 
                   (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating 
                               FROM " . DB_PREFIX . "product p 
                               LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) 
                   LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) 
                   LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) 
                   WHERE p.$colum = '" . (int)$dt . "' 
                   AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
                   AND p.status = '1' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");


        if( $query->num_rows > 0 ) {

            foreach( $query->rows as $res ) {

                $product_data[] = array(
                    'product_id'      => $res['p_id'],
                    'name'            => $res['pd_name'],
                    'description'     => $res['description'],
                    'tag'             => $res['tag'],
                    'title'           => $res['m_title'],
                    'm_description'   => $res['m_description'],
                    'm_h1'            => $res['m_h1'],
                    'model'           => $res['model'],
                    'ean'             => $res['ean'],
                    'isbn'            => $res['isbn'],
                    'mpn'             => $res['mpn'],
                    'quantity'        => $res['quantity'],
                    'image'           => $res['p_image'],
                    'price'           => $res['price'],
                    'special'         => $res['special'],
                    'points'          => $res['points'],
                    'weight'          => $res['weight'],
                    'weight_class_id' => $res['weight_class_id'],
                    'length'          => $res['length'],
                    'length_class_id' => $res['length_class_id'],
                    'width'           => $res['width'],
                    'height'          => $res['height'],
                    'subtract'        => $res['subtract'],
                    'minimum'         => $res['minimum'],
                    'rating'          => round($res['rating']),
                    'sort_order'      => $res['sort_order'],
                    'status'          => $res['status'],
                    'date_added'      => $res['date_added'],
                    'date_modified'   => $res['date_modified'],
                    'viewed'          => $res['viewed']
                );
            }

            return $product_data;
        } else {
            return false;
        }
    }

 

[chukcha]

А что говорит стандартный метод?

[artem2103]

09.01.2023 в 18:26, chukcha сказал:

А что говорит стандартный метод?

А какой именно стандартный метод? 

[chukcha]

getProducts

[artem2103]

09.01.2023 в 18:33, chukcha сказал:

getProducts

$sql .= " GROUP BY p.product_id";

		$sort_data = array(
			'pd.name',
			'p.model',
			'p.quantity',
			'p.price',
			'rating',
			'p.sort_order',
			'p.date_added'
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
				$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
			} elseif ($data['sort'] == 'p.price') {
				$sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
			} else {
				$sql .= " ORDER BY " . $data['sort'];
			}
		} else {
			$sql .= " ORDER BY p.sort_order";
		}

 

[chukcha]

Что в коде не ясно?

[artem2103]

Да вот не понятен вопрос, как сортировка происходит в данном случае.