Search rows and order by column and position of keywords in the results

[Dacian Nicoara]

Hello everyone!

I bumped my head into an issue and I need your help to solve it. My context is as following:

I have a table that stores records with a basic structure that looks like this (there are some other columns but these are used in the current issue):

```
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
|ID |type|title | description |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 1 | 0 | Lorem ipsum keyword_1 dolor keyword_2 keyword_3 | Mauris faucibus, keyword_2 odio keyword_1 gravida keyword_3, lacus leo malesuada |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 2 | 0 | Lorem ipsum keyword_1 dolor keyword_2 | Mauris faucibus, odio keyword_1 gravida keyword_3, lacus leo malesuada |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 3 | 1 | keyword_1 Lorem ipsum dolor keyword_2 | Mauris keyword_1 faucibus, odio keyword_2 gravida keyword_3, lacus leo malesuada |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 4 | 0 | Lorem ipsum keyword_1 dolor keyword_2 keyword_3 | Mauris faucibus, keyword_2 odio keyword_1 gravida keyword_3, lacus leo malesuada |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 5 | 0 | Lorem ipsum keyword_1 dolor keyword_2 | Mauris faucibus, odio keyword_1 gravida keyword_3, lacus leo malesuada |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 6 | 1 | keyword_1 Lorem ipsum dolor keyword_2 | Mauris keyword_1 faucibus, odio keyword_2 gravida keyword_3, lacus leo malesuada |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
| 7 | 0 | keyword_1 keyword_2 keyword_3 Lorem ipsum dolor | keyword_1 keyword_2 Mauris faucibus, odio keyword_3 gravida, lacus leo malesuada |
|---|----|-------------------------------------------------|----------------------------------------------------------------------------------|
```
...and so on.

+ the *ID* column is auto_increment
+ the *type* column is int(1) with only two possible values: 0 or 1
+ the *title* column is varchar(255)
+ the *description* column is mediumtext

An user performs a search for "*keyword_1 keyword_2 keyword_3*" or "*keyword_1 keyword_2*" or "*keyword_1*" and I would like to get the results with a query that is respecting the following conditions:

1. it should return the rows with type=0 first
2. it should return the rows that contain all the keywords in title and / or description (either the *title* or the *description* (or both) should contain all the keywords entered by the user)
3. it should return first the rows in which the position of the keywords is the most to the left (the beggining of the string stored in the table) on each of the two columns (*title* and / or *description*)
4. foreach of the keywords, it should match only the whole word (I don't want results with *keyword_1andsomeotherchars*)
5. **Update** if `type` = 0 then search only in `title` else search in `title` or `description`

A pseudo-query would be something like
```
select * from table
where title and / or description contains all the keywords
and keywords are most to the beginig of title and or description
order by type asc and by position of keywords in title and / or description
```

Currently, my query looks like this:

```
SELECT *
FROM table
WHERE table.`type` IN ('0','1')
AND table.`approved` = 1
AND table.`trash` = 0
AND ((table.`type` = 0
AND (match(table.`title`) against ('+keyword_1' IN BOOLEAN MODE)
AND match(table.`title`) against ('+keyword_2' IN BOOLEAN MODE)
AND match(table.`title`) against ('+keyword_3' IN BOOLEAN MODE))
OR table.`title` REGEXP '\bkeyword_1 keyword_2 keyword_3\b')
OR (table.`type` = 1
AND ((match(table.`title`) against ('+keyword_1' IN BOOLEAN MODE)
AND match(table.`title`) against ('+keyword_2' IN BOOLEAN MODE)
AND match(table.`title`) against ('+keyword_3' IN BOOLEAN MODE))
OR (match(table.`description`) against ('+keyword_1' IN BOOLEAN MODE)
AND match(table.`description`) against ('+keyword_2' IN BOOLEAN MODE)
AND match(table.`description`) against ('+keyword_3' IN BOOLEAN MODE)))
OR (table.`title` REGEXP '\bkeyword_1 keyword_2 keyword_3\b'
OR table.`description` REGEXP '\bkeyword_1 keyword_2 keyword_3\b'))
OR (table.`type` = 1
AND ((table.`title` REGEXP '\bkeyword_1\b'
OR table.`title` REGEXP '\bkeyword_2\b'
OR table.`title` REGEXP '\bkeyword_3\b')
AND (table.`description` REGEXP '\bkeyword_1\b'
AND table.`description` REGEXP '\bkeyword_2\b'
AND table.`description` REGEXP '\bkeyword_3\b'))))
ORDER BY table.`type` ASC,
CASE
WHEN table.`type` = 0 THEN INSTR(table.`title`, 'keyword_1 keyword_2 keyword_3')
WHEN table.`type` = 1 THEN INSTR(concat(table.`title`, table.`description`), 'keyword_1 keyword_2 keyword_3')
END ASC
LIMIT 0,20
```

but I'm not getting what I wanted. Can anyone help me solve this?

Thank you, in advance, for your time and help!