Simple tag searching with Sphinx

Question

For example, I have 3 tables:

documents (
    id serial PRIMARY KEY,
    title character varying(256),
    content text,
    created timestamp with time zone
);

tags (
    id serial PRIMARY KEY,
    tag_content character varying(128)
);

tag_assoc (
    id serial PRIMARY KEY,
    document_id integer,
    tag_id integer
);

I would like to be able to search documents for title, content, and for tags.

My sql_query so far is very simple like:

sql_query SELECT id, title, content FROM documents

How would I set up the Sphinx sql_query so that the tags associated with each document are joined to them?

Answer 1

You could use inside sql_query a subselect with group_concat to retrieve them , but a better approach would be to use the sql_joined_field. In your case, would look like:

sql_joined_field = tags from query; tag.assoc.document_id, \
                   tag_content from tags join tag_assoc on \
                   tags.id=tar_assoc.tag_id order by tag.assoc.document_id asc