there is a table -

table_a

| id | name | price |

and such a request -

(select id, name, price from table_a where to_tsvector('russian', name) @@ plainto_tsquery('russian', 'винт') order by price asc limit 1) union all (select id, name, price from table_a where to_tsvector('russian', name) @@ plainto_tsquery('russian', 'гайка') order by price asc limit 1)

Actually, it searches for fts names and gives it with the lowest price. Each item in the request.

http://sqlfiddle.com/#!15/eaa2b/24

Is there any way to get rid of union all ? And how will this all be if there are many associations?

Or is it easier to just do it on request and then combine the result for example php ?

  • A screw nut can be placed in one tsquery. The postgres documentation seems understandable. to_tsquery ('nut | screw') - Sergey
  • Well, if there is a whole search sentence? and then it is not clear how to separate the cheapest nut and the cheapest screw separately. - Zippbl4
  • Then look for separate nuts, separate screws. If these are completely different entities, what's the point of shoving them in one request? - Sergey
  • There is an astronomical degree of madness thought: make a JOIN to the tsquery set, recorded via VALUES according to the predicate with @@ , and ensure uniqueness with the help of DISTINCT ON to these tsquery . And if you set the order by price, then one entry will be selected for each tsquery with the minimum price. I dare not write this to SQL ._. And I strongly suspect that this will not win before individual requests. - D-side
  • I will dwell on the variant with individual requests. THX. - Zippbl4

0