Comments (8)
First task is to collect all use cases / situations.
from zuugle-api.
@martinheppner : collected the use cases in a google sheet, check your chat
from zuugle-api.
- Initialize the rank_xy with 1
- Set the TLD assotiated rank_xy with 10
- Set the menu_lang rank_xy with 100
SELECT
o."id",
o."url",
o."provider",
o."hashed_url",
o."description",
o."image_url",
o."ascent",
o."descent",
o."difficulty",
o."difficulty_orig",
o."duration",
o."distance",
o."title",
o."type",
o."children",
o."number_of_days",
o."traverse",
o."country",
o."state",
o."range_slug",
o."range",
o."season",
o."month_order",
o."country_at",
o."country_de",
o."country_it",
o."country_ch",
o."country_si",
o."country_fr",
o."publishing_date",
o."quality_rating",
o."user_rating_avg",
o."cities",
o."cities_object",
o."max_ele"
FROM (
SELECT
i1.,
ts_rank(i1.search_column, websearch_to_tsquery('english', '"schneeberg" schneeberg:') ) * {rank_en} as result_rank
FROM tour AS i1
WHERE i1.text_lang='en'
AND i1."country_at" = true and i1.cities @> '[{"city_slug": "wien"}]'::jsonb
AND i1.search_column @@ websearch_to_tsquery('english', '"schneeberg" schneeberg:*')
UNION
SELECT
i2.,
ts_rank(i2.search_column, websearch_to_tsquery('german', '"schneeberg" schneeberg:') ) * {rank_de} as result_rank
FROM tour AS i2
WHERE i2.text_lang='de'
AND i2."country_at" = true and i2.cities @> '[{"city_slug": "wien"}]'::jsonb
AND i2.search_column @@ websearch_to_tsquery('german', '"schneeberg" schneeberg:*')
UNION
SELECT
i3.,
ts_rank(i3.search_column, websearch_to_tsquery('french', '"schneeberg" schneeberg:') ) * {rank_fr} as result_rank
FROM tour AS i3
WHERE i3.text_lang='fr'
AND i3."country_at" = true and i3.cities @> '[{"city_slug": "wien"}]'::jsonb
AND i3.search_column @@ websearch_to_tsquery('french', '"schneeberg" schneeberg:*')
UNION
SELECT
i4.,
ts_rank(i4.search_column, websearch_to_tsquery('italian', '"schneeberg" schneeberg:') ) * {rank_it} as result_rank
FROM tour AS i4
WHERE i4.text_lang='it'
AND i4."country_at" = true and i4.cities @> '[{"city_slug": "wien"}]'::jsonb
AND i4.search_column @@ websearch_to_tsquery('italian', '"schneeberg" schneeberg:*')
UNION
SELECT
i5.,
ts_rank(i5.search_column, websearch_to_tsquery('simple', '"schneeberg" schneeberg:') ) * {rank_sl} as result_rank
FROM tour AS i5
WHERE i5.text_lang='sl'
AND i5."country_at" = true and i5.cities @> '[{"city_slug": "wien"}]'::jsonb
AND i5.search_column @@ websearch_to_tsquery('simple', '"schneeberg" schneeberg:*')
UNION
) AS o
order by o."month_order" asc, o.result_rank DESC, o.traverse DESC, FLOOR((o.cities_object->'wien'->>'best_connection_duration')::int/30)*30 ASC, o.ID % date_part('day', NOW() )::INTEGER ASC
limit 9 offset 9
from zuugle-api.
Up until now I was able to achieve the functionality to create the above query as it is in a general format, i.e. it is one function that creates all of the above query (looping over different arrays that were created for this purpose)
The challenge is to integrate this function inside the listWrapp that creates the complete query (with other parameters including filter params).
from zuugle-api.
The inner 5 SQLs are always rhere. The weighting value changes. The city and the search term changes. Everything else stays rhe same in the inner sql.
The outer sql is the one you are doing the filtering with (just like in the first version). The sorting is done in the outer sql as well.
I do not see the need of looping through an array, but maybe I am wrong.
from zuugle-api.
from your comment above : _The outer sql is the one you are doing the filtering with (just like in the first version). The sorting is done in the outer sql as well._
Do you mean that the outer sql , actually remains the same as before ? and so does the sorting that comes afterwards?
isnt this the outer sql ?
SELECT
o."id",
o."url",
o."provider",
........
........
FROM (
it maybe some small issue that i haven't caught yet, so can we/ should we use the "outer sql" already generated by the function listWrapp ? just as a reminder , by the time it reaches the search section were we should be inserting new search related code, the query looks like this:
select "id", "url", "provider", "hashed_url", "description", "image_url", "ascent", "descent", "difficulty", "difficulty_orig", "duration", "distance", "title", "type", "children", "number_of_days", "traverse", "country", "state", "range_slug", "range", "season", "month_order", "country_at", "country_de", "country_it", "country_ch", "country_si", "country_fr", "publishing_date", "quality_rating", "user_rating_avg", "cities", "cities_object", "max_ele" from "tour" where "country_at" = true and cities @> '[{"city_slug": "wien"}]'::jsonb
then this "" AND " : ""}search_column @@ websearch_to_tsquery('german', '"${_search}" ${_search}:*')` "
is added to it :
so ..
should we add the currently generated "outer" query as is ? modify it ?
or should we create a completely new version of it ?
this is what i would like to talk to you about tomorrow.
from zuugle-api.
We will still have to sent two queries. In Metacode like this:
query_count = knex.raw("SELECT count(*)" + sql_from + sql_where);
query_result = knex.raw(sql_select + sql_from + sql_where + sql_order);
from zuugle-api.
Code is in branch dev-drop-slovenia-issue#10
from zuugle-api.
Related Issues (20)
- Remove unused columns from table fahrplan HOT 1
- sync.js : create insert function for tracks table HOT 2
- Fix map markers calls on api HOT 4
- Sending markers in chunks from database/api HOT 2
- Handle inconsistent data HOT 1
- Low data quality: gpx_data and max_ele HOT 1
- pdf download not working HOT 10
- Result ordering by language is not working HOT 2
- Store always all tours in PostgreSQL for permanent urls HOT 1
- Expose travel time and connection_no_of_transfers on API
- Change to city2tour to get lat/lon of the stop
- Consolidate into one knex.raw query for search
- Detailpage is not working reliable
- Enhance search by upgrading city2tour HOT 1
- Checking which image to use and if available should be in the backend
- Replace columns cities and cities_object in table tour
- Remove column gpx_data from table tour and remove use
- URL Parameter "p" and "map" not working HOT 1
- Reset Filter shows weird values
- Limit result in connectionsExtendedWrapper
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from zuugle-api.