У меня есть таблица, называемая products в базе данных MySQL. products выглядят примерно так:
id name din strength active deleted 1 APA TEST 00246374 25 1 0 4 APA BOB 00246375 50 1 0 5 APA TIRE 00246888 50 1 0 7 APA ROT 00521414 100 1 0 9 APA APA 01142124 100 1 0 6 APA CODE 00121212 150 1 0 8 APA SERV 00011145 600 1 0
Очевидно, я оставил несколько столбцов, не имеющих значения для моего вопроса. Когда я запрашиваю эту таблицу, я буду сортировать по одному из нескольких разных столбцов (пользовательский интерфейс позволяет отдельным пользователям изменять колонку сортировки и порядок), и у меня может быть предложение поиска, в этом случае я сделаю предложение LIKE на NAME и DIN.
Я хочу знать, учитывая информацию о сортировке и информацию о поиске, а также идентификатор определенного продукта (скажем, я искал 004, который возвратил 3 результата, и я просматриваю один из них), как я мог получить следующий и предыдущие продукты?
Мне нужно сделать это, потому что если пользователь нажимает на редактирование / просмотр одного из продуктов после поиска и сортировки результатов, они хотят иметь возможность циклически перебирать результаты, не переходя на предыдущую страницу.
Есть ли хороший и эффективный способ сделать это в SQL, или мне лучше использовать PHP? Любые идеи также приветствуются.
В настоящее время используется этот SQL-запрос, который испытывает проблемы, если я сортирую по столбцу strength как есть повторяющиеся значения
SELECT T.* FROM `wp_products` T INNER JOIN `wp_products` curr on curr.id = 38 AND ((T.strength = curr.strength and T.id < curr.id) OR (T.strength > curr.strength)) WHERE T.active = 1 AND T.deleted = 0 AND (T.name LIKE '%%' OR T.din LIKE '%%') ORDER BY T.strength ASC, T.id ASC LIMIT 1
Мой PHP-код (с использованием WordPress) (разработан для получения следующего элемента)
$sql = 'SELECT T.* FROM `' . $wpdb->prefix . 'apsi_products` T INNER JOIN `' . $wpdb->prefix . 'apsi_products` curr on curr.id = ' . $item->id . ' AND ((T.' . $wpdb->escape( $query['orderby'] ) . ' = curr.' . $wpdb->escape( $query['orderby'] ) . ' and T.id > curr.id) OR (T.' . $wpdb->escape( $query['orderby'] ) . ' > curr.' . $wpdb->escape( $query['orderby'] ) . ')) WHERE T.active = 1 AND T.deleted = 0 AND (T.name LIKE \'%' . $query['where'] . '%\' OR T.din LIKE \'%' . $query['where'] . '%\') ORDER BY T.' . $wpdb->escape( $query['orderby'] ) . ' ' . $query['order'] . ', T.id ASC LIMIT 1;';
Вам нужно иметь ссылку на текущую запись, а затем постепенно искать следующую запись на основе отсортированных столбцов. В приведенном ниже примере предполагается, что он отсортирован по
ORDER BY Active, DIN, NAME
Первый:
SELECT * FROM TABLE WHERE NAME LIKE '%X%' AND DIN LIKE '%%' ORDER BY Active, DIN, Name LIMIT 1;
Далее: ( убедитесь, что вы отделили CURR.ID = 6 и AND-OR с соответствующими скобками! )
SELECT * FROM TABLE T INNER JOIN TABLE CURR ON CURR.ID = 6 # the current ID being viewed AND ((T.Active = Curr.Active AND T.DIN = Curr.DIN AND T.NAME > Curr.Name) OR (T.Active = Curr.Active AND T.DIN > Curr.DIN) OR T.Active > Curr.Active) WHERE T.NAME LIKE '%X%' AND T.DIN LIKE '%%' ORDER BY T.Active, T.DIN, T.Name LIMIT 1;
Рабочий образец, представленный ниже
create table products (ID int, SEED int, NAME varchar(20), DIN varchar(10), ACTIVE int, DELETED int); insert products values (1, 0, 'Product #1', '004812', 1, 0), (2, 0, 'Product #2', '004942', 0, 0), (3, 0, 'Product #3', '004966', 1, 0), (4, 0, 'Product #4', '007437', 1, 1), (5, 2, 'Product #2', '004944', 0, 0), (6, 2, 'Product #2', '004944', 1, 0); SELECT * FROM products WHERE active = 1 AND deleted = 0 ORDER BY din DESC, ID desc; Output: "ID";"SEED";"NAME";"DIN";"ACTIVE";"DELETED" "3";"0";"Product #3";"004966";"1";"0" "6";"2";"Product #2";"004944";"1";"0" "1";"0";"Product #1";"004812";"1";"0"
Если текущий – это строка с ID = 6, следующая запись может быть получена с использованием
SELECT T.* FROM products T INNER JOIN products curr on curr.ID = 6 AND ((T.din = curr.din and T.ID > curr.ID) OR (T.din < curr.din)) WHERE T.active = 1 AND T.deleted = 0 ORDER BY T.din DESC, T.ID ASC LIMIT 1;
Без уровня кеша персистентности, такого как memcached , где вы можете хранить результаты и получать его без повторной выдачи запроса, простым решением может быть включение кеша запросов в mysql. Таким образом, если кеш не является недействительным из других запросов, при повторной выдаче запроса стоимость вытягивания результата будет снижена
Для каждого отображаемого результата установите следующую и предыдущую ссылки с соответствующими идентификаторами.
Порядок сортировки не может быть изменен из представления деталей.
Интересный вопрос. Я не думаю, что вы можете сделать это в одном запросе, но вы можете снять его, возможно, тремя.
Первый запрос вызывает строку ответа. Хитрость заключалась бы в том, чтобы удержать значение для столбца, который они отсортировали. Предположим, что это «SortedColumn», а значение «M». Предположим, что идентификатор строки у вас был 10.
Второй запрос будет таким же, как и первый, но будет «топ-1» и добавит в предложение where «… и sortedColumn> =« M »и id <> 10« Это приведет к следующей строке. Возможно, вы захотите вернуть 10 строк и удерживать их, чтобы это не повторялось снова и снова.
Чтобы получить предыдущее, переключите свое упорядочение на нисходящее, а добавленное предложение where – «… и sortColumn <= 'M' и id <> 10". Это дает вам предыдущую строку. Опять же, 10 строк могут быть более полезными для вашего пользователя.
Надеюсь это поможет.