CODEWITHSUNDEEP
searchindexingsphinx
Wonka
Wonka

Reputation: 8674

Sphinx - Filter One Index, Search another Full Text

For a real estate portal, agents record how many open house guests showed up for any given day. I'm trying to use sphinx api to filter the houses with the most visits in a sphinx index called "open_house_guest_info", and then search against those houses ids in another sphinx index which has the house intro "houses", with a search term.

"open_house_guest_info" index:
date, house_id, guest_count

"houses" index:
house_id, house_intro

I know how to do this with MySQL, but I'm trying to do this with Sphinx. How can I search a different index's text based on the attributes filtered from another index tied with "house_id"?

Upvotes: 0

Views: 179

Answers (1)

barryhunter
barryhunter

Reputation: 21091

Sphinx cant 'JOIN' indexed. You have to put all the data in ONE index.

the sql_query that builds the index, can include JOINs, and GROUP BYs etc. Executed by mysql to create a single index. 

sql_query = SELECT g.id,house_id,house_intro,REPLACE(date,'-','') as date,COUNT(g.id) AS guests FROM houses h INNER JOIN open_house_guest_info g USING (house_id) GROUP BY date,house_id

sql_attr_uint = house_id
sql_attr_uint = date
sql_attr_uint = guests

Then can filter/group/order by date, house_id, or guests :) [in the sphinx query]

Upvotes: 1

Related Questions