Consider following models:
class Product(models.Model):
code = modeld.CharField()
class ProductTrans(models.Model):
product = models.ForeignKey('Product')
language = models.ChoiceField(choices=settings.LANGUAGES)
title = models.ChaField()
description = models.ChaField()
With this snippet is possible search through all translations of product at the same time (using string concatenation in trigger):
Product.objects.extra(
where = ['product_product.fulltext @@ to_tsquery(%s)'],
params = [ 'someproduct' ]
)
For PostgreSQL >=8.4 only.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | CREATE LANGUAGE plpgsql;
ALTER TABLE product_product ADD COLUMN fulltext tsvector;
CREATE INDEX product_product_fulltext_idx ON product_product USING gin(fulltext);
CREATE OR REPLACE FUNCTION product_product_fulltext_update() RETURNS trigger AS $$
DECLARE
txt TEXT;
trans RECORD;
BEGIN
txt := NEW.code;
FOR trans IN SELECT language, title, description FROM product_producttrans WHERE product_id = NEW.id LOOP
txt := txt || ' ' ||
coalesce(trans.language, '') || ' ' ||
coalesce(trans.title, '') || ' ' ||
coalesce(trans.description, '');
END LOOP;
NEW.fulltext := to_tsvector('pg_catalog.english', txt);
RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER product_product_fulltext_update BEFORE INSERT OR UPDATE
ON product_product FOR EACH ROW EXECUTE PROCEDURE product_product_fulltext_update();
CREATE OR REPLACE FUNCTION product_producttrans_fulltext_update() RETURNS trigger AS $$
DECLARE
trans RECORD;
BEGIN
IF (TG_OP = 'DELETE') THEN
trans := OLD;
ELSE
trans := NEW;
END IF;
UPDATE product_product SET id = id WHERE id = trans.product_id;
RETURN trans;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER product_producttrans_fulltext_update AFTER INSERT OR UPDATE OR DELETE
ON product_producttrans FOR EACH ROW EXECUTE PROCEDURE product_producttrans_fulltext_update();
|
More like this
- create_template_postgis-ubuntu_lucid by clawlor 14 years, 2 months ago
- Drop all tables in MySQL database by mpasternacki 14 years, 10 months ago
- grep and delete sqlite tables by kifkif 15 years ago
- Substitute hyphens with spaces to enale URLs to reference to multi-word tags by miernik 15 years, 1 month ago
- oneliner to delete SQLite tables by kifkif 15 years, 1 month ago
Comments
Please login first before commenting.