Postgres tsvector with relational tables

Question

I have two simple tables. First is brands and each brand may have a top brand. For example Elseve has a top brand which is Loreal Paris. But Avon has no top brand. And I have a simple products table.

Here is sqlfiddle

Here is brands table.

 id |     name     | parent_id | depth
----+--------------+-----------+-------
  3 | Loreal Paris |     null  |     0
  1 | Avon         |     null  |     1
  2 | Elseve       |      3    |     1
(3 rows)

And here is products table

 id | brand_id |   name
----+----------+-----------
  1 |        1 | Product 1
  2 |        2 | Product 2
(2 rows)

When I try to get tsvectors, Product 1 document returns null result. But I need to get at least Avon in the document.

 product_id | product_name |                     document
------------+--------------+--------------------------------------------------
          1 | Product 1    |
          2 | Product 2    | '2':2 'elsev':3 'loreal':4 'paris':5 'product':1
(2 rows)

How to solve this problem ?

Thanks to Voa Tsun. I updated query a little bit. I don't need grouping anymore.

select
  products.id as product_id,
  products.name as product_name,
  to_tsvector(products.name) ||
  to_tsvector(brands.name) ||
  to_tsvector(top_brands.name)
    as document
from products
  JOIN brands on brands.id = products.brand_id
  LEFT JOIN brands as top_brands on coalesce(brands.parent_id,brands.id) = top_brands.id;

You'll need to use coalesce() somewhere, because to_tsvector() returns NULL on NULL input. See f.ex. how the documentation use multiple columns. — pozs
– pozs, Commented May 31, 2017 at 13:04
Hi @pozs . I already used it. Like this to_tsvector(coalesce(string_agg(top_brands.name, ' '))). I know it is not logical. because there is no top_brand record for Avon. So it is useless. if I add top_brand for all brands, and empty top_brand name record for brands which has no top_brand, it will work. — Oğuz Can Sertel
– Oğuz Can Sertel, Commented May 31, 2017 at 13:05
@OğuzCanSertel you need to use everywhere, where NULL is a possibility. Note that your column may be NOT NULL, but if you use LEFT JOIN, the result set still can have NULLs. -- NVM I see what you misunderstood (or is this just a typo?): coalesce() with only 1 parameter does not do anything effective. You'll need to call with f.ex. coalesce(..., '') — pozs
– pozs, Commented May 31, 2017 at 13:07

Vao Tsun · Accepted Answer · 2017-05-31 13:12:57Z

1

The basic idea is to join id not against null in "parent_id", but "at least against id", as I got from your post. like here:

   select
      products.id as product_id,
      products.name as product_name,
      to_tsvector(coalesce(products.name,brands.name)) ||
      to_tsvector(brands.name) ||
      to_tsvector(coalesce(string_agg(top_brands.name, ' ')))
        as document
    from products
      JOIN brands on brands.id = products.brand_id
      LEFT JOIN brands as top_brands on coalesce(brands.parent_id,brands.id) = 
     top_brands.id
    GROUP BY products.id,brands.id;

    product_id  product_name    document
1   1   Product 1   '1':2'avon':3,4'product':1
2   2   Product 2   '2':2'elsev':3'loreal':4'pari':5'product':1

edited May 31, 2017 at 13:12

answered May 31, 2017 at 13:08

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres tsvector with relational tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related