boogerlad/mj - Change OJD2AWBJJRUXYVF5BBYWSRRIE5A7LKJDL6YQCQZ3APEBIB6GATFAC

https://wiki.postgresql.org/wiki/Set_equality so that if all variants are "recommended", then none are share query between get_products and similar_products similar_products takes in variant now(mostly for capsules and edibles, but can be even more dynamic now :)) remove terpenes and description from menu_item_loader

Created by boogerlad on April 13, 2020

OJD2AWBJJRUXYVF5BBYWSRRIE5A7LKJDL6YQCQZ3APEBIB6GATFAC

Dependencies

In channels

main

Change contents

Replacement in initialize.sql at line 580 [6.17]

∅:D[4.1171] → [5.1723:1765]

B:BD[5.1723] → [5.1723:1765]

create or replace function get_products(

[4.1171]

[7.0]

create or replace function contains_contained_by (anyarray, anyarray) returns boolean as $$
	select $1 <@ $2 and $1 @> $2 
$$ language sql parallel safe immutable; 
create operator <@> (
	procedure = contains_contained_by, 
	leftarg = anyarray, 
	rightarg = anyarray
);
create or replace function common(

Insertion in initialize.sql at line 614 [6.17]
[5.1935]
[5.1935]
```
	strain_id integer,
```
Replacement in initialize.sql at line 616 [6.17]
B:BD[5.1950] → [7.256:273]
```
	terpenes json,
```
[5.1950]
[5.1971]
```
	terpenes citext[],
```
Insertion in initialize.sql at line 632 [6.17]
[5.2165]
[5.2165]
```
		strain_id,
```
Replacement in initialize.sql at line 634 [6.17]
B:BD[5.2189] → [7.665:693]
```
		array_to_json(terpenes),
```
[5.2189]
[5.2202]
```
		terpenes,
```
Replacement in initialize.sql at line 654 [6.17]
B:BD[5.2730] → [5.2730:2747]
```
				product_id,
```
[5.2730]
[7.712]
```
				poop.product_id,
```

Replacement in initialize.sql at line 656 [6.17]

B:BD[7.722] → [3.0:112]

					when type in ('capsule', 'tea', 'gummy', 'mint', 'chocolate', 'baked') then json_agg(distinct variant_id)

[7.722]

[7.829]

					when type in ('capsule', 'tea', 'gummy', 'mint', 'chocolate', 'baked') and not array_agg(distinct variant_id) <@> (select array_agg(variant_id) from variant where product_id = poop.product_id) then json_agg(distinct variant_id)

Replacement in initialize.sql at line 674 [6.17]

B:BD[5.2970] → [7.1322:1365]

				inner join product using (product_id)

[5.2970]

				inner join product as poop using (product_id)

Replacement in initialize.sql at line 683 [6.17]

B:BD[5.3040] → [7.1427:1444]

B:BD[7.1444] → [8.847:858]

				product_id,
				type,

[5.3040]

[7.1444]

				poop.product_id,
				type,--think about what order bys will make separating by concentration by important. obvious ones are sort by thc / cbd. 2.5mg argyle on top, 10mg argyle on bottom. in all other cases, 2.5mg and 10mg argyle should be one product and not duplicated. price maybe. producer / brand / name definitely not. what wheres will show both? if both are shown, does order matter? before implementing stackoverflow.com/q/28560389 stackoverflow.com/q/10614505 "gaps and islands". more efficient to group by once compared to group by => (row_number or lag + sum) + group by

Replacement in initialize.sql at line 849 [6.17]

∅:D[4.8338] → [9.503:600]

B:BD[9.503] → [9.503:600]

B:BD[9.600] → [10.751:777]

create or replace function similar_products(
	_product_id integer,
	_store_id integer = null,
	in_stock boolean = null

[4.8338]

[9.627]

create or replace function get_products(
	_store_ids integer[] = null,
	_species species[] = null,
	_effects effect[] = null,
	_strain_ids integer[] = null,
	_types product_type[] = null,
	frequency text = 'daily',
	in_stock boolean = null,--true in stock only. false oos only. null everything
	search text = null,
	min_thc2tc decimal = null,
	max_thc2tc decimal = null,
	--_user_id integer = null, cte vs plpgsql variable?. will replace below parameters once user stuff is done
	akt1 akt1 = null,
	comt comt = null,
	cyp2c9 cyp2c9 = null,
	cyp2c19_2 cyp2c19_2 = null,
	cyp2c19_3 cyp2c19_3 = null,
	cyp2c19_17 cyp2c19_17 = null

Replacement in initialize.sql at line 868 [6.17]
B:BD[9.645] → [9.645:659]
```
	id integer,
```
[9.645]
[9.659]
```
	product_id integer,
	variant_ids json,
```
Replacement in initialize.sql at line 876 [6.17]
B:BD[9.758] → [9.758:779]
```
	terpenes citext[],
```
[9.758]
[9.779]
```
	terpenes json,
```
Replacement in initialize.sql at line 881 [6.17]
B:BD[9.852] → [9.852:871]
```
	quantity decimal
```
[9.852]
[9.871]
```
	min_portions smallint,
	max_portions smallint
```

Insertion in initialize.sql at line 884 [6.17]

[9.880]

	select
		product_id,
		variant_ids,
		type,
		producer,
		brand,
		name,
		species,
		strain,
		array_to_json(terpenes),
		image,
		cbd,
		thc,
		min_price,
		min_portions,
		max_portions
	from common(
		_store_ids => _store_ids,
		_species => _species,
		_effects => _effects,
		_strain_ids => _strain_ids,
		_types => _types,
		frequency => frequency,
		in_stock => in_stock,
		search => search,
		min_thc2tc => min_thc2tc,
		max_thc2tc => max_thc2tc,
		akt1 => akt1,
		comt => comt,
		cyp2c9 => cyp2c9,
		cyp2c19_2 => cyp2c19_2,
		cyp2c19_3 => cyp2c19_3,
		cyp2c19_17 => cyp2c19_17
	)
$$ language sql stable;
create or replace function similar_products(
	_variant_id integer,
	_store_ids integer[] = null,
	_species species[] = null,
	_effects effect[] = null,
	_strain_ids integer[] = null,
	_types product_type[] = null,
	frequency text = 'daily',
	in_stock boolean = null,--true in stock only. false oos only. null everything
	search text = null,
	min_thc2tc decimal = null,
	max_thc2tc decimal = null,
	--_user_id integer = null, cte vs plpgsql variable?. will replace below parameters once user stuff is done
	akt1 akt1 = null,
	comt comt = null,
	cyp2c9 cyp2c9 = null,
	cyp2c19_2 cyp2c19_2 = null,
	cyp2c19_3 cyp2c19_3 = null,
	cyp2c19_17 cyp2c19_17 = null
) returns table(
	product_id integer,
	variant_ids json,
	type product_type,
	producer text,
	brand text,
	name text,
	species species,
	strain text,
	terpenes json,
	image text,
	cbd numrange,
	thc numrange,
	min_price decimal(5, 2),
	min_portions smallint,
	max_portions smallint
) as $$--this is different. input should be variant(10mg, 2.5mg). its product_id should be excluded from result set incase another variant is "similar". but also, group by product id. don't show the same different gummy twice for example because two different concentration per pack. this is also a problem with get_products. if both different concentrations are recommended to the user, should be just one. only if filters showing only one of the concentrations at a time is ok. concrete example. if only 2.5mg is safe, product card link => 2.5mg highlight. same with 10. but if both are safe, don't show up as two cards. does group by change the order? how can I group by only if beside each other??

Insertion in initialize.sql at line 957 [6.17]
[9.904]
[9.904]
```
			product_id,
```

Replacement in initialize.sql at line 961 [6.17]

B:BD[9.942] → [9.942:1013]

			thc2tc(range_merge(thc), range_merge(cbd)) as thc2tc,
			terpenes

[9.942]

[9.1013]

			thc2tc(range_merge(thc), range_merge(cbd)),
			terpenes,
			avg_range(range_merge(thc)),
			first(portions)

Replacement in initialize.sql at line 969 [6.17]
B:BD[9.1119] → [9.1119:1153]
```
		where product_id = _product_id
```
[9.1119]
[9.1153]
```
		where variant_id = _variant_id
```

Deletion in initialize.sql at line 972 [6.17]

B:BD[9.1180] → [9.1180:1392]

--future state: 	select * from get_products(_store_id => _store_id)
--where product_id != _product_id--what are performance implications of nested where? any chance this can be inlined with get_products where?

Insertion in initialize.sql at line 974 [6.17]
[9.1416]
[9.1416]
```
		variant_ids,
```
Replacement in initialize.sql at line 978 [6.17]
B:BD[9.1448] → [9.1448:1473]
```
		product.display_name,
```
[9.1448]
[9.1473]
```
		name,
```

Replacement in initialize.sql at line 980 [6.17]

B:BD[9.1485] → [9.1485:1538]

		strain.display_name,
		terpenes,
		destination,

[9.1485]

[9.1538]

		strain,
		array_to_json(terpenes),
		image,

Replacement in initialize.sql at line 985 [6.17]

B:BD[9.1554] → [9.1554:2361]

B:BD[9.2361] → [10.778:852]

∅:D[10.852] → [9.2437:2689]

B:BD[9.2437] → [9.2437:2689]

		min,
		first
	from
		product
		left outer join strain using (strain_id)
		left outer join (
			select distinct on(product_id)--https://dba.stackexchange.com/a/159899
				product_id,
				destination
			from
				product_image
			--order by rank
		) as product_image using (product_id)
		inner join (--aggregate first, then join https://stackoverflow.com/a/27626358 I think this counts as "retrieve all or most"? obviously more filters maybe not, but let's optimize later
			select
				product_id,
				range_merge(cbd) as cbd,
				range_merge(thc) as thc,
				min(price),
				first(quantity)--this is used to convert mg / g to mg / piece for edibles
			from
				menu_item
				inner join variant using (variant_id)
			where
				store_id = _store_id or _store_id is null and
				case
					when in_stock then stock > 0
					when not in_stock then stock = 0
					else true
				end
			group by
				product_id
		) as product_agg using (product_id)
	where
		show = true and
		product_id != _product_id--this is the only thing stopping function reuse, and referencing columns not in select in the order by

[9.1554]

[9.2689]

		min_price,
		min_portions,
		max_portions
	from common(
		_store_ids => _store_ids,
		_species => _species,
		_effects => _effects,
		_strain_ids => _strain_ids,
		_types => _types,
		frequency => frequency,
		in_stock => in_stock,
		search => search,
		min_thc2tc => min_thc2tc,
		max_thc2tc => max_thc2tc,
		akt1 => akt1,
		comt => comt,
		cyp2c9 => cyp2c9,
		cyp2c19_2 => cyp2c19_2,
		cyp2c19_3 => cyp2c19_3,
		cyp2c19_17 => cyp2c19_17
	)
	where product_id != (select product_id from p)

Replacement in initialize.sql at line 1011 [6.17]

B:BD[9.2864] → [9.2864:3021]

		levenshtein(terpenes[1], (select terpenes[1] from p)) <= 2 desc nulls last,--temporary until similarity algorithm between two rank ordered lists is found

[9.2864]

[9.3021]

		levenshtein(terpenes[1], (select terpenes[1] from p)) <= 2 desc nulls last,--replace with http://sigaev.ru/git/gitweb.cgi?p=smlar.git;a=blob;hb=HEAD;f=README https://medium.com/@Alibaba_Cloud/keyword-analysis-with-postgresql-algorithms-for-text-analysis-with-the-smlar-plug-in-64ad32475026 using terpene weights 3:2:1 or 2.5:2:1 or bccannabisstores.com

Replacement in initialize.sql at line 1015 [6.17]

B:BD[9.3252] → [4.8339:8609]

∅:D[4.8609] → [9.3556:3617]

B:BD[9.3556] → [9.3556:3617]

		round(@ avg_range(thc) - (select avg_range(thc) from p), -1) asc nulls last--this is wrong thc_mg, but temporary(take into account user pref for flower, recommend at variant level) but maybe doesn't matter since same type? need to fix if type becomes optional though
		--nice la
		--since thc_mg is last, don't need to round.

[9.3252]

[9.3617]

		@ avg_range(thc) - (select avg_range from p) asc nulls last,--won't work if type becomes optional though
		@ avg_range(thc) / ((min_portions + max_portions) / 2) - (select avg_range / first from p) asc nulls last

Replacement in initialize.sql at line 1030 [6.17]
B:BD[11.214] → [2.0:21]
```
	terpenes citext[],
```
[11.214]
[5.3481]
```
	terpenes json,
```
Replacement in initialize.sql at line 1042 [6.17]
∅:D[2.226] → [5.3599:3612]
B:BD[5.3599] → [5.3599:3612]
```
		terpenes,
```
[2.226]
[5.3804]
```
		array_to_json(terpenes),
```
Deletion in initialize.sql at line 1127 [6.17]
B:BD[12.1622] → [12.1622:1663]
```
	description text,
	terpenes citext[],
```
Deletion in initialize.sql at line 1137 [6.17]
B:BD[12.1805] → [12.1805:1834]
```
		description,
		terpenes,
```

Replacement in server.js at line 694 [13.590]

B:BD[9.3850] → [9.3850:3971]

					let s = Number(parameters.store);
					if(Number.isInteger(s)) {
						qs += `_store_id => $${params.push(s)},`;

[9.3850]

[9.3971]

					if(Array.isArray(parameters.stores) && (parameters.stores = parameters.stores.map(Number)).every(Number.isInteger)) {
						qs += `_store_ids => $${params.push(parameters.stores)},`;

Replacement in server.js at line 700 [13.590]

B:BD[9.4107] → [9.4107:4235]

					let p = Number(parameters.product_id);
					if(Number.isInteger(p)) {
						qs += `_product_id => $${params.push(p)},`;

[9.4107]

[9.4235]

					let v = Number(parameters.variant_id);
					if(Number.isInteger(v)) {
						qs += `_variant_id => $${params.push(v)},`;

Dependencies

In channels

Change contents

Replacement in initialize.sql at line 580 [6.17]

Insertion in initialize.sql at line 614 [6.17]

Replacement in initialize.sql at line 616 [6.17]

Insertion in initialize.sql at line 632 [6.17]

Replacement in initialize.sql at line 634 [6.17]

Replacement in initialize.sql at line 654 [6.17]

Replacement in initialize.sql at line 656 [6.17]

Replacement in initialize.sql at line 674 [6.17]

Replacement in initialize.sql at line 683 [6.17]

Replacement in initialize.sql at line 849 [6.17]

Replacement in initialize.sql at line 868 [6.17]

Replacement in initialize.sql at line 876 [6.17]

Replacement in initialize.sql at line 881 [6.17]

Insertion in initialize.sql at line 884 [6.17]

Insertion in initialize.sql at line 957 [6.17]

Replacement in initialize.sql at line 961 [6.17]

Replacement in initialize.sql at line 969 [6.17]

Deletion in initialize.sql at line 972 [6.17]

Insertion in initialize.sql at line 974 [6.17]

Replacement in initialize.sql at line 978 [6.17]

Replacement in initialize.sql at line 980 [6.17]

Replacement in initialize.sql at line 985 [6.17]

Replacement in initialize.sql at line 1011 [6.17]

Replacement in initialize.sql at line 1015 [6.17]

Replacement in initialize.sql at line 1030 [6.17]

Replacement in initialize.sql at line 1042 [6.17]

Deletion in initialize.sql at line 1127 [6.17]

Deletion in initialize.sql at line 1137 [6.17]

Replacement in server.js at line 694 [13.590]

Replacement in server.js at line 700 [13.590]