Выбор по одной цене

mstdmstd

Новичок
Всем привет,
Условия выбора по меньшей(p_sale_price_from) и большим(p_sale_price_till) ценам :

Код:
( ( p.sale_price >= p_sale_price_from AND p.sale_price <= p_sale_price_till ) OR ( p_sale_price_from IS NULL OR p_sale_price_till IS null ) ) AND
работает правильно, если заданы обе цены или не не заданы не одна.
Если же задана только одна цена - то уловие не работает.
А как сделать чтобы работало, если задано тольеко одна цена ?

Спасибо!
 

antson

Новичок
Партнер клуба
самое простое решение [from,till] всегда определены
если что-то не указано, то
from = -1
to = максимальное возможное значение
 

antson

Новичок
Партнер клуба
правильно написать условие проверки. Но эксплаин запроса будет просто ужасен.
В простом варианте мы имеем range по индексу.
добавляя условия "или" скатимся до фуллскана по таблице

В общем если не знаком с оптимизацией запросов, советовать другое - только вредить.
 

fixxxer

К.О.
Партнер клуба
В postgresql можно сделать expression index, но сначала надо смотреть, оправдано ли это. Если обновляется таблица редко, а таких выборок очень много - смело делай.

UPD: так, стоп, секудочку :)

p_sale_price_from/to - это откуда берется?
 

mstdmstd

Новичок
В postgresql можно сделать expression index, но сначала надо смотреть, оправдано ли это. Если обновляется таблица редко, а таких выборок очень много - смело делай.

UPD: так, стоп, секудочку :)

p_sale_price_from/to - это откуда берется?
Параметры в функцию с этим запросом
 

mstdmstd

Новичок
правильно написать условие проверки. Но эксплаин запроса будет просто ужасен.
В простом варианте мы имеем range по индексу.
добавляя условия "или" скатимся до фуллскана по таблице

В общем если не знаком с оптимизацией запросов, советовать другое - только вредить.
С оптимизацией запросов знаком - хотя и не досконально.
В общем если явно определять [from,till] как наименьшее и наиюольшее возможное значение - то упроститься запрос и будет выигрыш в производительности?
 

antson

Новичок
Партнер клуба
@mstdmstd, да . может быть в случае если в запросе нет других условий.
оптимизатор выбирает какой индекс использовать из возможных или вообще их все послать из соображений минимизации затрат. (в частности операций чтений с диска)
при этом сильно влияют сами данные и оптимальный план со временем может меняться.

у тебя запрос собирает php функция .
тогда дописывай условия
от и до
только от
только до
ничего не добавлять
 

mstdmstd

Новичок
Если запрос создается средствами php да, там можно конструировать запрос самому.
Но часто делается процедура которую юзают с разных страниц,
например, есть функция для получения прайс листа с большим кол-вом параметров, которые на форме могут быть выбраны пользователем или нет:


Код:
CREATE OR REPLACE FUNCTION public.pd_get_product_prices(p_limit integer, p_offset integer, p_sort character varying DEFAULT 'rating'::character varying, p_sort_direction character varying DEFAULT 'asc'::character varying, p_title character varying DEFAULT NULL::character varying, p_status type_productstatus DEFAULT NULL::type_productstatus, p_in_stock boolean DEFAULT NULL::boolean, p_downloadable boolean DEFAULT NULL::boolean, p_virtual boolean DEFAULT NULL::boolean, p_sku character varying DEFAULT NULL::character varying, p_sale_price_from type_money DEFAULT NULL::numeric, p_sale_price_till type_money DEFAULT NULL::numeric, p_rating_from integer DEFAULT NULL::integer, p_rating_till integer DEFAULT NULL::integer, p_category_list integer[] DEFAULT NULL::integer[])
RETURNS TABLE(id bigint, title character varying, status type_productstatus, slug character varying, sku character varying, user_id integer, regular_price type_money, sale_price type_money, in_stock boolean, short_description character varying, downloadable boolean, virtual boolean, rating_count integer, rating_summary integer, rating integer, published_at timestamp without time zone, created_at timestamp without time zone, main_image character varying, product_categories character varying[], product_categories_slug character varying[], product_tags character varying[], product_tags_id smallint[], bookmarks_count bigint, product_attributes jsonb)
LANGUAGE sql
AS $function$

select * from ( SELECT p.id, p.title, p.status, p.slug, p.sku, p.user_id, p.regular_price, p.sale_price, p.in_stock, p.short_description, p.downloadable, p.virtual, p.rating_count, p.rating_summary,

CAST( floor( p.rating_summary / NULLIF(p.rating_count,0) ) AS INTEGER ) as rating,

p.published_at, p.created_at,
(select pi.image from pd_product_image as pi where pi.product_id = p.id and pi.is_main = true ) as main_image,
( select array_agg(c.name) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories,
( select array_agg(c.slug) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories_slug,
( select array_agg(t.name) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags,
( select array_agg(t.id) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags_id,
( select count(*) from pd_product_bookmark where pd_product_bookmark.product_id = p.id and pd_product_bookmark.user_id = p.user_id ) as bookmarks_count,
( select pa.attributes_data from pd_product_attribute as pa where pa.product_id = p.id) as product_attributes


FROM pd_product AS p LEFT JOIN pd_product_category AS pc ON pc.product_id = p.id

WHERE
( p.status= p_status OR p_status IS NULL ) AND
( p.in_stock = p_in_stock OR p_in_stock IS NULL ) AND
( p.downloadable = p_downloadable OR p_downloadable IS NULL ) AND
( p.virtual = p_virtual OR p_virtual IS NULL ) AND
( LOWER(p.sku) like LOWER(p_sku) OR p_sku IS NULL ) AND
( LOWER(p.title) like LOWER(p_title) OR p_title IS NULL ) AND
( ( p.sale_price >= p_sale_price_from AND p.sale_price <= p_sale_price_till ) OR ( p_sale_price_from IS NULL OR p_sale_price_till IS null ) ) AND
( pc.category_id = ANY (p_category_list) OR p_category_list IS NULL )
GROUP BY p.id
) as rows




WHERE( ( rows.rating >= p_rating_from AND rows.rating <= p_rating_till ) OR ( p_rating_from IS NULL OR p_rating_till IS null ) )




ORDER BY






CASE WHEN p_sort_direction = 'asc' THEN
CASE p_sort
-- sort by numeric fields
WHEN 'rating' THEN rating
WHEN 'sale_price' THEN sale_price
ELSE NULL
END
ELSE
NULL
END
ASC,




CASE WHEN p_sort_direction = 'desc' THEN
CASE p_sort -- sort by numeric fields
WHEN 'rating' THEN rating
WHEN 'sale_price' THEN sale_price
ELSE NULL
END
ELSE
NULL
END
DESC,






CASE WHEN p_sort_direction = 'asc' THEN
CASE p_sort -- sort by string fields
WHEN 'title' THEN title
WHEN 'status' THEN CAST(status AS character varying )
WHEN 'downloadable' THEN CAST(downloadable AS character varying )
WHEN 'virtual' THEN CAST(virtual AS character varying )
WHEN 'in_stock' THEN CAST(in_stock AS character varying )
ELSE NULL
END
ELSE
NULL
END
ASC,




CASE WHEN p_sort_direction = 'desc' THEN
CASE p_sort -- sort by string fields
WHEN 'title' THEN title
WHEN 'status' THEN CAST(status AS character varying )
WHEN 'downloadable' THEN CAST(downloadable AS character varying )
WHEN 'virtual' THEN CAST(virtual AS character varying )
WHEN 'in_stock' THEN CAST(in_stock AS character varying )
ELSE NULL
END
ELSE
NULL
END
DESC,






CASE WHEN p_sort_direction = 'asc' THEN
CASE p_sort
-- sort by timestamp fields
WHEN 'published_at' THEN published_at
WHEN 'created_at' THEN created_at
ELSE NULL
END
ELSE
NULL
END
ASC,




CASE WHEN p_sort_direction = 'desc' THEN
CASE p_sort -- sort by timestamp fields
WHEN 'published_at' THEN published_at
WHEN 'created_at' THEN created_at
ELSE NULL
END
ELSE
NULL
END
DESC





LIMIT p_limit OFFSET p_offset;


$function$
Есть 2 параметра пределов цены p_sale_price_from и p_sale_price_till - которые, наверно, также лучше сделать с мин и макс значениями для упрощения выборки,
заместо условия :
Код:
( ( p.sale_price >= p_sale_price_from AND p.sale_price <= p_sale_price_till ) OR ( p_sale_price_from IS NULL OR p_sale_price_till IS null ) ) AND
Но можно ли и как убирать условия OR для следующих конструкций:

Чекбокс downloadable может быть выбран или нет :
Код:
( p.downloadable = p_downloadable OR p_downloadable IS NULL ) AND
строковое значение может быть введено или нет :
Код:
( LOWER(p.sku) like LOWER(p_sku) OR p_sku IS NULL ) AND
Может быть выбрано несколько категорий или нет :
Код:
( pc.category_id = ANY (p_category_list) OR p_category_list IS NULL )
Это postgres-функция, а не конструктор на php.
Ну так ведь поэтому и уходят от mysql с различными ORM-ами к Postgres с выполнением функций на сервере...
Я понимаю, что есть плюсы и минусы в обоих подходах...
 

fixxxer

К.О.
Партнер клуба
@mstdmstd, в хранимке тоже можно собирать запрос динамически.

Берешь не language sql, а language plpgsql, и программируй себе примерно как на php.
 
Последнее редактирование:

fixxxer

К.О.
Партнер клуба
Ну так ведь поэтому и уходят от mysql с различными ORM-ами к Postgres с выполнением функций на сервере...
Нет, не поэтому, да и переходят как раз в другую сторону чаще.

Без крайне веских причин логику в хранимках вообще делать не надо. В веб-разработке - можно сказать, что никогда не надо. Маленькие функции-триггеры, скажем, для умышленной денормализации в целях производительности и поддержания целостности такой структуры - да. А логику - не стоит, это только все усложняет.
 

mstdmstd

Новичок
Спасибо.
То есть в Where заместо
Код:
( p.downloadable = p_downloadable OR p_downloadable IS NULL ) AND
Лучше написать
...
Код:
( CASE when p_downloadable IS NOT NULL THEN p.downloadable = p_downloadable else true END ) AND
...
1) и это будет лучше в производительности ?
2) в такой последовательности надо обязательно писать else true - иначе не работает ?
downloadable - это булево поле



3) С двойным условием :
Код:
( CASE WHEN p_sale_price_from IS NOT NULL AND p_sale_price_till IS NOT NULL THEN p.sale_price >= p_sale_price_from AND p.sale_price <= p_sale_price_till

WHEN p_sale_price_from IS NOT NULL AND p_sale_price_till IS NULL THEN p.sale_price >= p_sale_price_from
WHEN p_sale_price_from IS NULL AND p_sale_price_till IS NOT NULL THEN p.sale_price <= p_sale_price_till
END ) AND
...
4) И для двух дат делать как выше 3)?
 

antson

Новичок
Партнер клуба
я бы написал ;)
price BETWEEN coalesce(pmin,-1) AND coalesce(pmax,99999999999)
 

antson

Новичок
Партнер клуба
максимально возможное для типа константой. 9999 это условно
 

mstdmstd

Новичок
Спасибо, многое прояснилось.
И еще вопрос в фугкцию добавил бувевский параметп p_in_description указывающий на то что если он TRUE то поиск нужно сделать
по двум полям описания :

Код:
   short_description varchar(255) NOT NULL,
   description text NULL,
И условие выглядит так:
Код:
          (
          ( CASE when p_title IS NOT NULL THEN LOWER(p.title) like LOWER(p_title) else true END ) OR
          ( CASE when p_title IS NOT NULL AND p_in_description = TRUE THEN LOWER(p.description) like LOWER(p_title) else true END ) OR
          ( CASE when p_title IS NOT NULL AND p_in_description = TRUE THEN LOWER(p.short_description) like LOWER(p_title) else true END ) ) AND
Но как его оптимизировать ?
 

WMix

герр M:)ller
Партнер клуба
убрать lower
Код:
select 'FoO' like 'fOo';
+------------------+
| 'FoO' like 'fOo' |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)
убрать = TRUE
Код:
select true, true = true;
+------+-------------+
| TRUE | true = true |
+------+-------------+
|    1 |           1 |
+------+-------------+
1 row in set (0.00 sec)
Код:
p_title IS NULL OR
p.title LIKE p_title OR (
  p_in_description AND (
    p.description LIKE p_title OR p.short_description LIKE p_title
  )
)
но комлексность не на много уменьшилаь
 

antson

Новичок
Партнер клуба
@mstdmstd, замечание в сторону.
лайкание "поискового выражение" в тексте не дает ожидаемого потребителем эффекта.
находятся только точные соответствия.

Н-р: "бухта 5е 305м" - фиг найдется потому что типовое название "Кабель UTP 5e (бухта,305м)" Попробуй посмотреть в сторону сфинкса
 
Сверху