Propel custom Setter with SQL-specific stuff

Question

Hello stackoverflow gurus

I am using Propel 1.2 in a Symfony 1.0 project, with PostgreSQL db. I can use Criteria::CUSTOM in SELECT statements in order to use Postgres functions, like this (fulltext search):

`$c = new Criteria();

$c->add(MyTablePeer::FULLTEXT_COLUMN, MyTablePeer::FULLTEXT_COLUMN." @@ to_tsquery('english', 'text to search')", Criteria::CUSTOM);`

That's ok for selecting records, but what about INSERTing? I'm using raw SQL queries at the moment, but I would like to do it in a more elegant way. I saw this snippet on the Symfony website but it's a lot of code and it does not fit my needs. Performance is very important so maybe there isn't any solution better than writing raw SQL.

How can I write a custom setter like $object->setFulltextColumn($parameters)?

Thank you in advance

Answer 1

For insert/update on the fulltext column, why not use a trigger? Then the fulltext column will automatically be updated when you change the text for a row.

CREATE TRIGGER my_table_fulltext_trigger 
BEFORE INSERT OR UPDATE ON my_table 
FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(
  'fulltext',
  'pg_catalog.english', 
  'column_with_text_1', 
  'column_with_text_2', 
  'column_with_text_N'
);

The parameters to the tsvector_update_trigger procedure are: name of tsvector fulltext column, language configuration, and then the names of all the columns to index.