full text search with active records?

[eluser]jorre[/eluser]
I'm trying to write the following query in CI using active record syntax:

select * from tblproducts where MATCH(product_name) against ('pods coffee')

but in the documentation I can't find any code for creating this MATCH

Does anyone know how to best solve this? I could of course write a full custom query and run it in CI...

[eluser]bradym[/eluser]
I don't believe there is currently a way to do a MATCH AGAINST query with the active record class.

I only ever use the CI Active Record class for the simplest of queries. Once I get past the really simple stuff, I find it's more work to figure out how to get the query I want generated correctly than to write it myself.

Brady

[eluser]Seppo[/eluser]
There is no built in method, but you can use

Code:

//$this->db->where('MATCH(product_name) AGAINT (' . $this->db->escape($search) . ')');

Update: as a matter of fact, you can't... since some version this has broken =(
You are stick to $this->db->query()

[eluser]Pascal Kriete[/eluser]
The solution to Seppo's problem can be found here. Just add a FALSE argument.

[eluser]Seppo[/eluser]
That was my first thought... However I think that's wrong

If I do

Code:

$this->db->where('MATCH (field) AGAINST ("value")', FALSE);
$this->db->get('table');

The generated query is

SELECT * FROM (`table`) WHERE MATCH (field) AGAINST ("value") 0

(note the 0 at the end).

Have you tried it?

[eluser]Pascal Kriete[/eluser]
I haven't. I didn't think I'd have to verify Derek's comment .

EDIT: You're right, it doesn't escape the query (correct behavior), but it also adds a trailing zero (wtf behavior).

EDIT2: where() takes 3 arguments, so it will be:

Code:

$this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);
$this->db->get('table');

This needs to be in the docs.

[eluser]Seppo[/eluser]
Still no good... I'm getting

SELECT * FROM (`table`) WHERE `MATCH` (field) AGAINST ("value")

using that sintaxis (using current SVN version, not sure on 1.6.1).

Setting the escape argument to false does not prevent the "column" name to get escaped...

-Edit- Try posting new replies instead of editing, this way I didn't know about your updates

[eluser]Derek Allard[/eluser]
inparo, see... trusting me was your first mistake!

OK all, I think I've got this licked. Apologies for the run around. Inparo is correct that it needs to be a third parameter (the second set to null). I've documented this under db->where()

So that means that

Code:

$this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);

Should give

Code:

SELECT * FROM (`table`) WHERE MATCH (field) AGAINST ("value")

Please beat it up, test it, put it through the wringer.

[eluser]Seppo[/eluser]
Well... I think Derek fix this for the next CI version... the correct way to do it will be

Code:

$this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);

-Edit- 5 seconds later! =P

[eluser]jdgiotta[/eluser]
This fix was implemented in the latest release, correct?
Following the example does not produce the desired effect; the MATCH keyword is still escaped.

Code:

$this->db->where("((t.`text` IN ('".$sql_words."'))");
$this->db->or_where("(c.code IN ('".$sql_words."'))");
$this->db->or_where("MATCH (ct.title, ct.description) AGAINST ('".str_replace("','", ' ', $sql_words)."' IN BOOLEAN MODE))", NULL, FALSE);

Gets me

WHERE ((t.`text` IN ('yeah','news'))
OR (c.code IN ('yeah','news'))
OR `MATCH` (ct.title, ct.description) AGAINST ('yeah news' IN BOOLEAN MODE))