CODEWITHSUNDEEP
postgresqlsubquerysearch-enginesphinx
Fish
Fish

Reputation: 137

How to use subquery In Sphinx's multi-valued attributes (MVA) for query type

My database is PostgreSQL, and I want to use Sphinx Search Engines to index my data;

How can i use sql_attr_multi to fetch the relationship data?

The tables in postgresql, schemas is:

crm=# \d orders
                                   Table "orders"
     Column      |            Type             |               Modifiers
-----------------+-----------------------------+----------------------------------------
 id              | bigint                      | not null
 trade_id        | bigint                      | not null
 item_id         | bigint                      | not null
 price           | numeric(10,2)               | not null
 total_amount    | numeric(10,2)               | not null
 subject         | character varying(255)      | not null default ''::character varying
 status          | smallint                    | not null default 0
 created_at      | timestamp without time zone | not null default now()
 updated_at      | timestamp without time zone | not null default now()
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "orders_trade_id_idx" btree (trade_id)




crm=# \d trades
                                     Table "trades"
        Column         |            Type             |               Modifiers
-----------------------+-----------------------------+---------------------------------------
 id                    | bigint                      | not null
 operator_id           | bigint                      | not null
 customer_id           | bigint                      | not null
 category_ids          | bigint[]                    | not null
 total_amount          | numeric(10,2)               | not null
 discount_amount       | numeric(10,2)               | not null
 created_at            | timestamp without time zone | not null default now()
 updated_at            | timestamp without time zone | not null default now()
Indexes:
    "trades_pkey" PRIMARY KEY, btree (id)

The Sphinx's config is:

source trades_src
{
  type        = pgsql
  sql_host    = 10.10.10.10
  sql_user    = ******
  sql_pass    = ******
  sql_db      = crm
  sql_port    = 5432

  sql_query   = \
    SELECT id, operator_id, customer_id, category_ids, total_amount, discount_amount, \
         date_part('epoch',created_at) AS created_at, \
         date_part('epoch',updated_at) AS updated_at \
      FROM public.trades;

  #attributes
  sql_attr_bigint = operator_id
  sql_attr_bigint = customer_id

  sql_attr_float  = total_amount
  sql_attr_float  = discount_amount


  sql_attr_multi = bigint category_ids from field category_ids

  #sql_attr_multi = bigint order_ids from query; SELECT id FROM orders
  #how can i add where condition is the query for orders? eg. WHERE trade_id = ?


  sql_attr_timestamp  = created_at
  sql_attr_timestamp  = updated_at
}

I used MVA (multi-valued attributes) on category_ids field, and it is the ARRAY type in Postgresql.

But I donot know How to define MVA on order_ids. It will be through subquery?

Upvotes: 0

Views: 242

Answers (1)

barryhunter
barryhunter

Reputation: 21091

Copied from sphinx forum....

sql_attr_multi = bigint order_ids from query; SELECT trade_id,id FROM orders ORDER BY trade_id

The first column of the query is the sphinx 'document_id' (ie the id in main sql_query)

The second column is the value to insert into the MVA array for that document.

(The ORDER BY might not strictly be needed, but sphinx is much quicker at processing the data if ordered by document_id IIRC)

Upvotes: 1

Related Questions