boogerlad/mj - Change GQ2TJSGOGNH4PKDNN5ZWITLOE6YNAWO6TCBCQV4SN7ZRWEB6IZ5AC

get products from within a list of stores(for geolocation query) thc:tc range filter sort price, concentration, producer, brand, name (only 1!!! lazy 2 drag and drop and think of good ux) through backend query generation recommend specific variant since different capsule concentrations are now variants instead of distinct products, and edibles grouping by concentration per pack terpenes json array instead of postgres array compute per pack in db instead of client side for edibles. also use for thresholds in recommendation new thresholds from roz specific for each product type fix text search. I got the parameter order backwards. oops :)

Created by boogerlad on April 7, 2020

GQ2TJSGOGNH4PKDNN5ZWITLOE6YNAWO6TCBCQV4SN7ZRWEB6IZ5AC

Dependencies

In channels

main

Change contents

Replacement in initialize.sql at line 579 [4.17]

B:BD[3.1765] → [2.1172:1304]

	_store_id integer = null,--soon to be array? for geolocation sort
	_species species[] = null,--how about default to empty array?

[3.1765]

[2.1304]

	_store_ids integer[] = null,
	_species species[] = null,

Deletion in initialize.sql at line 586 [4.17]

∅:D[2.1504] → [5.168:242]

B:BD[5.168] → [5.168:242]

B:BD[5.242] → [2.1505:1732]

∅:D[2.1732] → [5.330:381]

B:BD[5.330] → [5.330:381]

	sort_price boolean = null,--true asc, false desc, null don't sort by it
	sort_thc boolean = null,--in frontend, warn user that the sort does not make sense if types from more than one method of consumption(inhalation, sublingual, ingestion) are included in filter
	sort_cbd boolean = null,--ditto
	sort_rank boolean = null,--contingent on ranking

Insertion in initialize.sql at line 587 [4.17]
[2.1755]
[2.1755]
```
	min_thc2tc decimal = null,
	max_thc2tc decimal = null,
```
Insertion in initialize.sql at line 592 [4.17]
[2.1904]
[2.1904]
```
	cyp2c9 cyp2c9 = null,
```
Replacement in initialize.sql at line 595 [4.17]
B:BD[2.1964] → [2.1964:2019]
```
	cyp2c19_17 cyp2c19_17 = null,
	cyp2c9 cyp2c9 = null
```
[2.1964]
[3.1819]
```
	cyp2c19_17 cyp2c19_17 = null
```
Replacement in initialize.sql at line 597 [4.17]
B:BD[3.1837] → [3.1837:1851]
```
	id integer,
```
[3.1837]
[3.1851]
```
	product_id integer,
	variant_id integer,--null means recommend any variant
```
Replacement in initialize.sql at line 605 [4.17]
B:BD[3.1950] → [3.1950:1971]
```
	terpenes citext[],
```
[3.1950]
[3.1971]
```
	terpenes json,
```

Replacement in initialize.sql at line 610 [4.17]

B:BD[3.2044] → [3.2044:2063]

	quantity decimal

[3.2044]

[3.2063]

	quantity decimal,
	thc_per_pack decimal,--this is a hack for edibles. since grouping by concentration per pack, it is constant regardless of per piece concentration. concentration * quantity * portion yields wrong result because range * first() is wrong! range * both would yield same number, but no way for frontend to know unless more data is sent
	cbd_per_pack decimal--ditto

Insertion in initialize.sql at line 616 [4.17]
[3.2096]
[3.2096]
```
		v,
```
Replacement in initialize.sql at line 623 [4.17]
B:BD[3.2189] → [3.2189:2202]
```
		terpenes,
```
[3.2189]
[3.2202]
```
		array_to_json(terpenes),
```
Replacement in initialize.sql at line 628 [4.17]
B:BD[3.2242] → [3.2242:2251]
```
		first
```
[3.2242]
[3.2251]
```
		q,
		t,
		c
```

Insertion in initialize.sql at line 645 [4.17]

[3.2747]

				case
					when first(type) in ('capsule', 'tea', 'gummy', 'mint', 'chocolate', 'baked') then first(variant_id)
					else null
				end as v,

Replacement in initialize.sql at line 652 [4.17]

B:BD[3.2824] → [3.2824:2903]

				first(quantity)--this is used to convert mg / g to mg / piece for edibles

[3.2824]

[3.2903]

				case
					when first(type) in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then first(quantity)
					else null
				end as q,
				case
					when first(type) in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then first(portions * quantity * avg_range(thc))
					else null
				end as t,
				case
					when first(type) in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then first(portions * quantity * avg_range(cbd))
					else null
				end as c

Insertion in initialize.sql at line 667 [4.17]
[3.2970]
[3.2970]
```
				inner join product using (product_id)
```

Replacement in initialize.sql at line 669 [4.17]

B:BD[3.2980] → [6.320:373]

				(store_id = _store_id or _store_id is null) and

[3.2980]

[6.373]

				(store_id = any(_store_ids) or _store_ids is null) and

Replacement in initialize.sql at line 676 [4.17]

B:BD[3.3040] → [3.3040:3056]

				product_id

[3.3040]

[3.3056]

				product_id,
				case
					when type = 'capsule' then quantity
					when type in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then portions * quantity * (avg_range(thc) + avg_range(cbd))--group by concentration per pack. change once we recommend by concentration per piece
					else 0
				end

Replacement in initialize.sql at line 687 [4.17]

B:BD[2.2127] → [2.2127:2185]

		(species = any(_species) or _species is null) and case

[2.2127]

[2.2185]

		(species = any(_species) or _species is null) and
		(min_thc2tc is null or max_thc2tc is null or thc2tc(thc, cbd) between min_thc2tc and max_thc2tc) and case

Replacement in initialize.sql at line 702 [4.17]

B:BD[2.2637] → [2.2637:2942]

				type in ('flower', 'preroll', 'cartridge', 'disposable') or
				thc2tc(thc, cbd) > .3 or
				avg_range(thc) < case--76. need to fix
					when type = 'oil' then 20
					when type = 'spray' then 2
					when type in ('capsule', 'tea', 'gummy', 'mint', 'chocolate', 'baked') then 5 / first
				end

[2.2637]

[2.2942]

				(type in ('flower', 'preroll', 'cartridge', 'disposable', 'spray') or
				case
					when type = 'oil' then avg_range(cbd) < 76
					when type = 'capsule' then avg_range(cbd) < 20
					when type in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then c < 20
				end)

Replacement in initialize.sql at line 715 [4.17]

B:BD[2.3166] → [2.3166:3471]

				type in ('flower', 'preroll', 'cartridge', 'disposable') or
				thc2tc(thc, cbd) > .3 or
				avg_range(thc) < case--20. need to fix
					when type = 'oil' then 20
					when type = 'spray' then 2
					when type in ('capsule', 'tea', 'gummy', 'mint', 'chocolate', 'baked') then 5 / first
				end

[2.3166]

[2.3471]

				(type in ('flower', 'preroll', 'cartridge', 'disposable', 'spray') or
				case
					when type = 'oil' then avg_range(cbd) < 20
					when type = 'capsule' then avg_range(cbd) < 10
					when type in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then c < 10
				end)

Replacement in initialize.sql at line 724 [4.17]

B:BD[2.3630] → [2.3630:3711]

			else avg_range(thc) < case
				when type in ('flower', 'preroll') then case

[2.3630]

[2.3711]

			else case
				when type in ('flower', 'preroll') then avg_range(thc) < case

Replacement in initialize.sql at line 727 [4.17]

B:BD[2.3752] → [2.3752:3828]

						when cyp2c9 = '*1/*3' then 240
						when cyp2c9 = '*3/*3' then 200

[2.3752]

[2.3828]

						when cyp2c9 = '*1/*3' then 180
						when cyp2c9 = '*3/*3' then 150

Replacement in initialize.sql at line 731 [4.17]

B:BD[2.3880] → [2.3880:3972]

						when cyp2c9 = '*1/*3' then 215
						when cyp2c9 = '*3/*3' then 165
						else 365

[2.3880]

[2.3972]

						when cyp2c9 = '*1/*3' then 130
						when cyp2c9 = '*3/*3' then 100
						else 220

Replacement in initialize.sql at line 746 [4.17]

B:BD[2.4260] → [2.4260:4316]

				when type in ('cartridge', 'disposable') then case

[2.4260]

[2.4316]

				when type in ('cartridge', 'disposable') then avg_range(thc) < case
					when frequency = 'daily' then case
						when cyp2c9 = '*1/*3' then 490.9
						when cyp2c9 = '*3/*3' then 409.1
					end
					when frequency = 'weekly' then case
						when cyp2c9 = '*1/*3' then 441.8
						when cyp2c9 = '*3/*3' then 339.1
						else 750
					end
					when frequency = 'monthly' then case
						when cyp2c9 = '*1/*3' then 353.1
						when cyp2c9 = '*3/*3' then 184.6
						else 600
					end
					else case--'rarely'
						when cyp2c9 = '*1/*3' then 200
						when cyp2c9 = '*3/*3' then 100
						else 400
					end
				end
				when type = 'capsule' then avg_range(thc) < case

Replacement in initialize.sql at line 769 [4.17]

B:BD[2.4357] → [2.4357:4447]

						when cyp2c9 = '*1/*3' then 240--fixme
						when cyp2c9 = '*3/*3' then 200--fixme

[2.4357]

[2.4447]

						when cyp2c9 = '*1/*3' then 7.5
						when cyp2c9 = '*3/*3' then 5

Replacement in initialize.sql at line 773 [4.17]

B:BD[2.4499] → [2.4499:4605]

						when cyp2c9 = '*1/*3' then 215--fixme
						when cyp2c9 = '*3/*3' then 165--fixme
						else 365

[2.4499]

[2.4605]

						when cyp2c9 = '*1/*3' then 5
						when cyp2c9 = '*3/*3' then 3
						else 7.5

Replacement in initialize.sql at line 778 [4.17]

B:BD[2.4658] → [2.4658:4762]

						when cyp2c9 = '*1/*3' then 70--fixme
						when cyp2c9 = '*3/*3' then 40--fixme
						else 130

[2.4658]

[2.4762]

						when cyp2c9 = '*1/*3' then 3
						when cyp2c9 = '*3/*3' then 2
						else 5

Replacement in initialize.sql at line 783 [4.17]

B:BD[2.4798] → [2.4798:4902]

						when cyp2c9 = '*1/*3' then 50--fixme
						when cyp2c9 = '*3/*3' then 25--fixme
						else 100

[2.4798]

[2.4902]

						when cyp2c9 = '*1/*3' then 2.4
						when cyp2c9 = '*3/*3' then 1.2
						else 3

Replacement in initialize.sql at line 788 [4.17]

B:BD[2.4921] → [2.4921:5099]

				when type in ('capsule', 'tea', 'gummy', 'mint', 'chocolate', 'baked') then case
					when frequency = 'daily' then case--need to divide by first, but capsule breaks it...

[2.4921]

[2.5099]

				when type in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then t < case
					when frequency = 'daily' then case

Replacement in initialize.sql at line 817 [4.17]

B:BD[2.6045] → [2.6045:6439]

			when x = 'focused' then species = 'hybrid' and thc2tc(thc, cbd) between .25 and .7 or species = 'sativa' and avg_range(thc) <= case
				when type in ('flower', 'preroll') then 100
				when type in ('disposable', 'cartridge') then 550
				when type = 'oil' then 20
				when type = 'spray' then 2
				when type in ('capsule', 'tea', 'gummy', 'mint', 'chocolate', 'baked') then 5 / first

[2.6045]

[2.6439]

			when x = 'focused' then species = 'hybrid' and thc2tc(thc, cbd) between .25 and .7 or species = 'sativa' and case
				when type in ('flower', 'preroll') then avg_range(thc) <= 100
				when type in ('disposable', 'cartridge') then avg_range(thc) <= 400
				when type = 'oil' then avg_range(thc) <= 20
				when type = 'spray' then true
				when type = 'capsule' then avg_range(thc) <= 5
				when type in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then t <= 5

Replacement in initialize.sql at line 825 [4.17]

B:BD[2.6447] → [2.6447:6668]

		end) from unnest(_effects) as t(x)), true)-- and
		--(search is null or word_similarity(concat_ws(' ', producer, brand, product.display_name), lower(search)) > 0.5)--0.5 is too high... maybe limit instead if present?

[2.6447]

[5.561]

		end) from unnest(_effects) as t(x)), true) and
		(search is null or search <% concat_ws(' ', producer, brand, product.display_name))

Replacement in initialize.sql at line 828 [4.17]

B:BD[5.572] → [2.6669:6813]

∅:D[2.6813] → [5.662:704]

B:BD[5.662] → [5.662:704]

B:BD[5.704] → [2.6814:6861]

		case when search is not null then word_similarity(concat_ws(' ', producer, brand, product.display_name), lower(search)) end desc nulls last,
		case when sort_price then min end asc,
		case when not sort_price then min end desc,

[5.572]

[2.6861]

		--ranking whenever that lands
		case when search is not null then word_similarity(search, concat_ws(' ', producer, brand, product.display_name)) end desc,

Replacement in server.js at line 571 [8.590]

B:BD[7.22] → [2.9650:9810]

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

[7.22]

[7.143]

					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 613 [8.590]

B:BD[5.2071] → [5.2071:2196]

					if(typeof parameters.sort_price === 'boolean') {
						qs += `sort_price => $${params.push(parameters.sort_price)},`;

[5.2071]

[2.11326]

					if(Array.isArray(parameters.thc2tc) && parameters.thc2tc.every(isFinite) && parameters.thc2tc.length === 2) {
						qs += `min_thc2tc => $${params.push(parameters.thc2tc[0])},max_thc2tc => $${params.push(parameters.thc2tc[1])},`;

Replacement in server.js at line 617 [8.590]

B:BD[2.11529] → [2.11529:11719]

						wack = ` where ${parameters.producers.map(p => `producer = $${params.push(p.producer)}${Array.isArray(p.brands) ? ` and brand = any($${params.push(p.brands)})`: ''}`).join(' or ')}`;

[2.11529]

[7.395]

						wack += ` where ${parameters.producers.map(p => `producer = $${params.push(p.producer)}${Array.isArray(p.brands) ? ` and brand = any($${params.push(p.brands)})`: ''}`).join(' or ')}`;
					}
					if(typeof parameters.price === 'boolean') {
						wack += ` order by min_price ${parameters.price ? 'a' : 'de'}sc`;
					} else if(typeof parameters.THC === 'boolean') {
						wack += ` order by case when type in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then thc_per_pack else avg_range(thc) end ${parameters.THC ? 'a' : 'de'}sc, case when type in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then avg_range(thc) * quantity else null end ${parameters.THC ? 'a' : 'de'}sc`;
					} else if(typeof parameters.CBD === 'boolean') {
						wack += ` order by case when type in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then cbd_per_pack else avg_range(cbd) end ${parameters.CBD ? 'a' : 'de'}sc, case when type in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then avg_range(cbd) * quantity else null end ${parameters.CBD ? 'a' : 'de'}sc`;
					} else if(typeof parameters.producer === 'boolean') {//lower bad for performance. index?
						wack += ` order by lower(producer) ${parameters.producer ? 'a' : 'de'}sc`;
					} else if(typeof parameters.brand === 'boolean') {
						wack += ` order by lower(brand) ${parameters.brand ? 'a' : 'de'}sc`;
					} else if(typeof parameters.name === 'boolean') {
						wack += ` order by lower(name) ${parameters.name ? 'a' : 'de'}sc`;