Форум MySQL

как можно сделать выборку объявлений по автомобилям сортировкой по цене с определёным выбором валюты также как на сайте auto.ru ?

я сделал, но это если объявление добавлять и привязывать жестко к одной валюте(тоесть валюта не указывается вовсе, в системе заложено изначально цена ввиде USD, и рядом при указании цены есть пометка - указывать только валюте USD).

А теперь заказчик попросил сделать чтобы при добавлении объявления можно было выбирать валюту одну из трёх ( РУБ | USD | EURO) выпадающем списке. Но теперь возникла проблема с выводом. Ранее я заранее знал что объявления были привязаны жёстко к одной валюте, то я легко мог выводить объявления сортировкой ORDER BY price desc и при отображении цены пересчитывал на выбранную валюту. Теперь же всё усложнилось надо не только пересчитать, это не проблема, проблема в другом, допустим если кто-то добавил объявление №1 с ценой 1000 РУБ а другой добавил объявление №2 с ценой 1000 USD и когда будем выводить при валюте "USD", то по логике объявление под №2 должно быть выше чем №1 но поскольку для mysql цифра 1000 у обоих объявлений одна и таже а о валюте ей ничего не известно, а как сделать чтобы можно было учитывать и валюту тоже?

Да, в БД оба поля цена и валюта значатся так

`currency` int(11) NOT NULL DEFAULT '0',  `price` double(15,0) NOT NULL DEFAULT '0',

массив валют

$arCurrency  = array (    array('id' => '1','name' => 'РУБ'),    array('id' => '2','name' => 'USD'),    array('id' => '3','name' => 'EURO'),  );

соответственно в поле "currency" хранится ID валюты

при отображении Вы валюту пересчитываете.
Следовательно у Вас имеется таблица с курсами валют.
значит нужно её подключить FROM goods a JOIN rates b ON a.currency=b.id
и упорядочить ORDER BY a.price * b.rate

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

Создал я таблицу currency

Таблица: currency ------------- id     name     coeff 1     RUB       2     USD     32,51 3     EURO     45,49

курс доллара и евро я беру с сайта
http://www.cbr.ru/scripts/XML_daily.asp
курс рубля там нет

условие запроса такой. Нужно выбрать записи по доллару по возрастанию

SELECT a.* FROM `auto` a JOIN `currency` c ON a.currency=c.id ORDER BY a.price * c.coeff desc

результат:

Цена, $ --------- 70,000$ 10,007$ 1,000$ 500€ 20,000руб. 10,000руб. 500,000руб. напротив каждой цены указана валюта которая была выбрана при добавлении. Отделяющую запятую подставляет PHP при выводе. В базе цена хранится так 500000 если для последней записи из примера

видим что доллар и евро номально а вот с рублём не в порядке, почему-то цена 500,000руб. находится в самом конце хотя должна быть между 70,000$ и 10,007$

как я понимаю это из-за того что для рубля не указан курс, но какой он должен быть или как его высчитывать?

а у рубля у вас коэффициент 1?

для рубли ни какого коэффициента не указанно, пустое поле, а как его высчитать?

для рубля 1.
вы ведь фактически (по методу, который показал вам Trianon) переводите в рубли.

во, здорово, указал 1 теперь всё в порядке, спасибо:)

теперь таблица валюты выглядит таким образом

id     name     coeff 1     RUB     1 2     USD     32,51 3     EURO    45,49

а почему именно цифра 1 почему ни какая другая?

на этот вопрос я вам ответил выше:
"вы ведь переводите в рубли."

а ну да, не сообразил, спасибо

заметил такое, сейчас запрос как JOIN я решил добавить в начале ещё LEFT тоесть получилось так

SELECT a.* FROM `auto` a LEFT  JOIN `currency` c ON a.currency=c.id ORDER BY a.price * c.coeff desc

проверил результат вроде тот же, что лучьше использовать совместно с LEFT или без него?

LEFT означает выдать результат из левой таблицы (auto) даже если в правой таблице (currency) подходящих строк нет.
К примеру как в случае с этим самым рублем.

SELECT a.*    FROM `auto` a      LEFT  JOIN `currency` c ON a.currency=c.id    ORDER BY a.price * COALESCE(c.coeff, 1.00) DESC

будет работать даже если строку с рублем = 1 в правую таблицу не поместить.

без LEFT рублевых строк выдано не будет.

спасибо за разъяснение, значит в моём случаи как я понял нужно использовать в месте с LEFT, мне только левая(auto) таблица нужна для вывода. Насчёт функции COALESCE правда мне ничего не известно, попытался найти, толи плохо искал, как я понял что-то связанно с NULL пустыми строками..

да и ещё один вопросик, в друг после решу сделать совместную выборку по диапазону цены "от" и "до", то мне останется добавить BETWEEN таким образом?
от 1 тыс. до 10 тыс.

SELECT a.* FROM `auto` a LEFT JOIN `currency` c ON a.currency=c.id where a.price BETWEEN 1000 AND 10000 ORDER BY a.price * c.coeff desc

но вроде что-то не так, вывело запись где указана цена 16 тыс. евро

не верю.
Показывайте дамп таблицы.


ps. Такой фильтр имеет смысл лишь применительно к пересчитанной цене.
Иначе у Вас спутаются рубли , доллары, евро и проч.
Выражение с произведеним из ORDER BY просится к выносу в список SELECT. Неужели не чувствуете?

дамп немного урезал, убрал лишние колонки, оставил несколько

Это не дамп. Это некий отчет.
Дамп - это последовательность из операторов CREATE TABLE и INSERT
Снимается phpMyAdmin в разделе Экспорт

А отчет этот из поста уберите . Ленту рвет.

извиняюсь, сейчас сделал экспорт двух таблиц auto и currency и приложил файлом

но решить данную задачу както можно?
к примеру на странице
http://cars.auto.ru/cars/used/honda/accord/

можно заметить просмотр по различным валютам так же там присутствует два поля для выбора поиска по диапазону цены "Цена от" и "Цена до"

1. записи 16 тыс. евро я в дампе не нашел . поэтому подтвердить или опровергнуть утверждение не могу.
2. коэффициент курса валюты в таблице лежит в неправильно м формате и поле неправильного типа. Из-за чего от курса используется лишь его целая часть (без дробной) . Такую ошибку Вы будете искать годами, потому что проявляется она в мелочах.
3. задачу решить безусловно можно.

спасибо за подсказку, верно, я не обратил внимание что нужно было у коэфициента заменить запятую на точку, а какой лучше тип указать для этого поля(коэфициент)?

насчёт 16 тыс. верно, в дампе этой цифры нет, она была вычислена PHP по курсу.

>спасибо за подсказку, верно, я не обратил внимание что нужно было у коэфициента заменить запятую на точку, а какой лучше тип указать для этого поля(коэфициент)?
DECIMAL
аналогично и с полем price
>
>насчёт 16 тыс. верно, в дампе этой цифры нет, она была вычислена PHP по курсу.
Вы сказали, что выполняете фильтр с помощью WHERE BETWEEN AND
Там у Вас совсем даже не вычисляемые поля, а исходные.
Сервер отрабатывает в строгом соответствии с текстом запроса.
подсказка:
SELECT а.* , (цена * COALESCE(курс, 1) ) AS result
...
WHERE result BETWEEN ... AND ...
ORDER BY result

спасибо что помогаете, самому мне сложно разобраться

я попытался сделать по последнему примеру, получилось вот что

этот запрос работает нормально

SELECT a.*,(a.price * COALESCE(c.coeff, 1) ) AS price_new FROM `auto` a LEFT JOIN `currency` c ON a.currency=c.id ORDER BY price_new asc

но если добавить where price_new BETWEEN 300 AND 50000

SELECT a.*,(a.price * COALESCE(c.coeff, 1) ) AS price_new FROM `auto` a LEFT JOIN `currency` c ON a.currency=c.id where price_new BETWEEN 300 AND 50000 ORDER BY price_new asc

выдаётся такая ошибка:
Unknown column 'price_new' in 'where clause'

может я где-то не так что-то указал?

Костыль: поменяйте слово where на слово having

[horisontal scrolling ЛАП on]

во, здорово, теперь вроде всё нормально работает, спасибо:)

по поводу COALESCE(c.coeff, 1) я так понял цифра 1 должна быть всегда или в зависимости от просматриваемой валюты подставлять соответствующий курс, ну то есть если просматриваем по доллару то в место 1 подставляем курс доллара 32.05 и если просматриваем по евро то курс евро 44.99 Или же тут всегда только 1 должна стоять?

да и какое значение указать для типа поля price decimal(0,0) сколько цифр оставить перед запятой и после? для коэффициента(coeff ) поставил так decimal(3,2) хотел decimal(2,2) но почему-то 2 заменилось автоматом на 3

DECIMAL(всего_цифр, цифр_после_точки)
32.05 - DECIMAL(4, 2)
На первый вопрос отвечать не буду.
Вы мешаете в кучу инструмент SQL и логику обработки данных.
Если Вам непонятна функция COALESCE() - лучше внесите все возможные курсы в таблицу.
Хотя смысл её очень прост. Она вернет значение первого же (слева направо) определенного аргумента.