TIP: Use Markdown or, <pre> for multi line code blocks / <code> for inline code.
These forums are read-only and for archival purposes only!
Please join our new forums at discourse.kohanaframework.org
MySQL Full-text Searching MATCH AGAINST
  • Hello, In my application i need to select records using MySQL Full-text Searching MATCH AGAINST syntax.

    SELECT * FROM table WHERE MATCH(field1, field2) AGAINST ('keyword') 

    Is it possible to add such condition with query builder in Kohana 3.0 ?

  • Here's an answer:


    Sadly full-text searching invariably means non-standard SQL and loss of code portability.

  • Thanks for your answer. In example you show me

    ->where('MATCH("title", "keywords")', 'AGAINST', DB::expr("(:str IN BOOLEAN MODE)"))
    ->param(':str', $string)

    was problem when using with ORM, because query builder wraps first param in where() with quotes

    'MATCH("title", "keywords")'

    so i put it in DB::expr() and it's work

    ->where(DB::expr('MATCH(`title`, `keywords`)'), 'AGAINST', DB::expr("('keyword' IN BOOLEAN MODE)"))
  • Isn't it easier to use plain SQL for difficult queries (like this and, of course, more difficult)? Imo, query builder is to reduce time of writing query. If you need to search in Google, maybe even ask in forum or IRC for suggestion, isn't that a bit lame? And what do you gain? New syntax and little, little lost of performance.

    With easier queries.... why not? =P

  • Of course you are right it's better and clearly to use plain sql-query and it's give more speed and perfomance, but in my situation there is no need in such improvements. I think people develop libraries like ORM for speed of development and readability for other. May be it's a bit lame :)

  • The key advantage for me is being able to conditionally add where clauses based on request without having to use placeholders in a raw SQL string or similar hackery. It can get a bit tiresome when you have to jump through such hoops, though.

  • @hvvmad: are you aware that you have SQL injection there?

  • @hvvmad I do not see why you would need DB::expr() on the first parameter: Database::column_quote() does some magic.

    @Kemo Where? I can't spot it.

  • @Darsstar at my current project i use Kohana 3.0, for 3.1 it's a real magic ;) If you run similar code with your own ORM Model in 3.0

    $result = ORM::factory('Article')->where('MATCH(`name`, `brief`)', "AGAINST", DB::expr("('keyword' IN BOOLEAN MODE)"))->find_all();

    it's produce such sql query

    SELECT `articles`.* FROM `articles` WHERE `MATCH(`name`, `brief`)` AGAINST ('keyword' IN BOOLEAN MODE) ORDER BY `articles`.`id` ASC

    as you can see, there is a problem with MATCH() because it wraped with (`) and breaks sql statment. Because of this i use DB::Expr(), may be there is more elegant solution.

  • @Kemo Isn't that Query Builder automatically escapes all characters so there can't be any SQL injection?

  • @hvvmad https://github.com/kohana/database/blob/3.1/master/classes/kohana/database.php#L520 but it only works if you use double quotes in the parameter not backticks, it will be changed to backticks after compiling.

  • @Darsstar @daGrevis Database_Expression objects aren't escaped at all, so be very careful when using them. You should always use Database::escape ($this->_db->escape() in ORM) when using it. In this case it's the 'keyword' string but if it was a variable it'd easily become an sqli (I'm not sure how much can be injected when inside of an AGAINST but there'll be quite a few errors)

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion