0

I need combine this 2 query in one, and sort by Id,

select GEODIST(40, 50, latitude,longitude, {in=degrees, out=meters}) AS distance, id FROM offersRT WHERE distance<=1000 LIMIT 0,30;

and

select 0 as distance, id FROM offersRT WHERE MATCH('@location_path_id 39644') LIMIT 0,30;

I use Manticore 3.6.0.

I can't use OR statement like

WHERE distance<=1000 or MATCH('@location_path_id 39644')

i also try like this:

SELECT id FROM (
      select 0 as distance, id FROM offersRT WHERE MATCH('@location_path_id 39644') LIMIT 0,30;
      select GEODIST(40, 50, latitude,longitude, {in=degrees, out=meters}) AS distance, id FROM offersRT WHERE distance<=1000 LIMIT 0,30;
);

and not working too.

Is there any other option to do this?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Black Jack
  • 11
  • 5

1 Answers1

0

Well it MATCH('@location_path_id 39644') doesnt really need to use the 'keywords' index. Its not a really a keyword match, and something that can easily be done with an attribute filter. Ie can just make location_path_id an attribute.

Manticore at least should then support OR directly in the WHERE (it just doesn't with MATCH)

SELECT GEODIST(40, 50, latitude,longitude, {in=degrees, out=meters}) AS distance, id 
FROM offersRT WHERE distance<=1000 OR location_path_id = 39644 LIMIT 0,30;

But if that doesn't work, can definitely implement the 'OR' in the SELECT section

SELECT GEODIST(40, 50, latitude,longitude, {in=degrees, out=meters})<=100 + location_path_id = 39644 AS filter, id 
FROM offersRT WHERE filter > 0 LIMIT 0,30;

The second form should work even in Sphinx.

barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • MATCH('@location_path_id 39644') must be a keywords index because they constants multiple id like "39644 123 983 23234" etc. Search by attribute require albo full index scan. I don't find a solutions so I refactored all code to use just one query with match location_path_id. – Black Jack Oct 23 '21 at 19:14
  • Multiple values like that could go in the special Multi Value Attribute (MVA) type. it specifically for holding multiple ids. – barryhunter Oct 24 '21 at 16:47