Complex query

[smeed652]

I'm hoping Pat and/or the community can help with a complex query we'd
like to do with ThinkingSphinx/Sphinx. To express what we are looking
for we did the query through SQL.

-- Need a Thinking/sphinx search where I can express an 'or' condition
with different values of the same attribute.
-- The question marks ('?') are values passed into the query.

select distinct videos.id
from videos
join video_catalogs on videos.video_catalog_id = video_catalog.id

where videos.title like "%search term%"
and video_catalogs.id not in [?, ?, ...]
and ( (video_catalogs.visibility = 0)
or (video_catalogs.visibility = 1 and
video_catalogs.company_id = ?)
or (video_catalogs.visibility = 2 and
video_catalogs.company_id in [?, ?, ...]))

-- visibility is an enumerated integer.
-- video_catalogs.company_id is a single unsigned integer.

Is this possible with Thinking/Sphinx? Thanks for everyones time.

Sid

[Timo Virkkala]

Hi,

Are those question marks values that would already know at indexing
time, or are they different for each query? If you already know them
at indexing time, it would be quite easy to define some attributes in
your index that would allow you to make your search.

If you really need to do all that at search time, I hope Pat knows
something I don't :)

-T-

> --
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.
> To post to this group, send email to thinkin...@googlegroups.com.
> To unsubscribe from this group, send email to thinking-sphi...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
>
>

[Pat Allan]

Thanks for the suggestion Timo - and you're right, if the values are known at indexing, then create attributes for them using SQL snippets and you should be good.

I'm guessing that won't be enough though - but there are ways to get OR logic into attribute filters - here's some docs, let me know if you need more information though.
http://freelancing-god.github.com/ts/en/common_issues.html#or_attributes

Cheers

--
Pat