Is it possible to have my optional search terms have results with them come first?
E.G.:
Search: flamingo MAYBE pen
I would like all results with both flamingo AND pen to come first, then all results with just flamingo.
I know I could do this with 2 searches and stacking, but I am trying to avoid that big of changes to my system.
Hello
The MAYBE operator is exactly for that or I perhaps don’t understand the issue. With flamingo MAYBE pen you will get “results with both flamingo AND pen to come first, then all results with just flamingo”. Example:
mysql> drop table if exists t; create table t(f text); insert into t(f) values('flamingo smth pen'),('flamingo smth'),('smth else flamingo'); select *, weight() from t where match('flamingo MAYBE pen');
--------------
drop table if exists t
--------------
Query OK, 0 rows affected (0.01 sec)
--------------
create table t(f text)
--------------
Query OK, 0 rows affected (0.01 sec)
--------------
insert into t(f) values('flamingo smth pen'),('flamingo smth'),('smth else flamingo')
--------------
Query OK, 3 rows affected (0.00 sec)
--------------
select *, weight() from t where match('flamingo MAYBE pen')
--------------
+---------------------+--------------------+----------+
| id | f | weight() |
+---------------------+--------------------+----------+
| 1514488646869188862 | flamingo smth pen | 1500 |
| 1514488646869188863 | flamingo smth | 1409 |
| 1514488646869188864 | smth else flamingo | 1409 |
+---------------------+--------------------+----------+
3 rows in set (0.00 sec)