sphinx config for publication

gistfile1.txt

source publication_core_0
{
  type = pgsql
  sql_query_pre = SET TIME ZONE 'UTC'
  sql_query = SELECT "publications"."id" * 5 + 3 AS "id" , CAST(COALESCE("editions"."title_prefix", '') as varchar) || ' ' || CAST(COALESCE("editions"."title", '') as varchar) || ' ' || CAST(COALESCE("editions"."subtitle", '') as varchar) AS "title", array_to_string(array_accum(COALESCE(CAST(COALESCE("profiles"."given_names", '') as varchar) || ' ' || CAST(COALESCE("profiles"."surname", '') as varchar), '0')), ' ') AS "author", CAST(COALESCE("editions"."short_description", '') as varchar) || ' ' || CAST(COALESCE("editions"."long_description", '') as varchar) AS "description", "editions"."abstract" AS "abstract", "publications"."id" AS "sphinx_internal_id", 2640206651 AS "class_crc", 0 AS "sphinx_deleted", COALESCE("editions"."title_prefix", '') || ' ' || COALESCE("editions"."title", '') || ' ' || COALESCE("editions"."subtitle", '') AS "title_sort", array_to_string(array_accum(COALESCE(COALESCE("profiles"."given_names", '') || ' ' || COALESCE("profiles"."surname", ''), '0')), ' ') AS "author_sort", cast(extract(epoch from "publications"."date_published") as int) AS "date_published", "publications"."imprint_id" AS "imprint", array_to_string(array_accum(COALESCE("media_types"."id", '0')), ',') AS "mt", array_to_string(array_accum(COALESCE("offerings"."price", '0')), ',') AS "price", "work_types"."id" AS "pt" FROM "publications"    LEFT OUTER JOIN "editions" ON "editions".id = "publications".edition_id   LEFT OUTER JOIN "works" ON "works".id = "editions".work_id   LEFT OUTER JOIN "authorships" ON authorships.work_id = works.id   LEFT OUTER JOIN "users" ON "users".id = "authorships".authorizable_id AND "authorships"."authorizable_type" = 'CgIdentity::User'   LEFT OUTER JOIN "profiles" ON profiles.user_id = users.id   LEFT OUTER JOIN "offerings" ON offerings.publication_id = publications.id AND  ("offerings"."type" = 'CgPublisher::Product' )   LEFT OUTER JOIN "media_types" ON "media_types".id = "offerings".media_type_id   LEFT OUTER JOIN "work_types" ON "work_types".id = "works".work_type_id  WHERE "publications"."id" >= $start AND "publications"."id" <= $end GROUP BY "publications"."id", "editions"."title_prefix", "editions"."title", "editions"."subtitle", "editions"."short_description", "editions"."long_description", "editions"."abstract", "publications"."id", "editions"."title_prefix", "editions"."title", "editions"."subtitle", "publications"."date_published", "publications"."imprint_id", "work_types"."id" 
  sql_query_range = SELECT COALESCE(MIN("id"), 1::bigint), COALESCE(MAX("id"), 1::bigint) FROM "publications" 
  sql_attr_uint = sphinx_internal_id
  sql_attr_uint = class_crc
  sql_attr_uint = sphinx_deleted
  sql_attr_uint = imprint
  sql_attr_uint = pt
  sql_attr_timestamp = date_published
  sql_attr_str2ordinal = title_sort
  sql_attr_str2ordinal = author_sort
  sql_attr_multi = uint mt from field
  sql_attr_multi = uint price from field
  sql_query_info = SELECT * FROM "publications" WHERE "id" = (($id - 3) / 5)
}