Multiple Inner Joins and Geospatial Indexing

[Code Daemon]

I have a query that involves 4 inner joins, the only reason I want to
use it with TS is so that I can incorporate a geospatial search.

Basically, every time a user posts with a tag, a delayed job will find
everyone who is subscribed to that tag within a certain radius and
then send out an email.

Here is what will generate my recipient list in ActiveRecord minus the
geospatial part. (It will also run in a delayed job task)

# SELECT DISTINCT users.id, users.email
# => FROM `users`
# => INNER JOIN `user_tag_subscriptions` ON
`user_tag_subscriptions`.`user_id` = `users`.`id`
# => INNER JOIN `tags` ON `tags`.`id` =
`user_tag_subscriptions`.`tag_id`
# => INNER JOIN `post_tags` ON `post_tags`.`tag_id` = `tags`.`id`
# => WHERE `post_tags`.`post_id` = 123"

@recipients = User.joins(:user_tag_subscriptions => [:tag
=> :post_tags])
.where('post_tags.post_id' => 123)
.select("DISTINCT users.id, users.email")

LAT/LON data is nested in the UserTagSubscription object. It has a
user_id, tag_id, location_id. The Location object has the actual LAT/
LON coordinates.

Is this possible using TS?

[Pat Allan]

I know this is getting drawn into two threads, but I'll answer here...

First thing to note is that Sphinx doesn't handle any more than one lat/lng pair for a document... so, it sounds like the index is best placed on the UserTagSubscription object (has_one location, so still one set). Given the tag is attached to that model as well, it should be pretty simple:

define_index do
indexes tag.name, :as => :tag

has tag_id, user_id
has location.lat, :as => :lat
has location.lng, :as => :lng
end

UserTagSubscription.search(
:with => {:tag_id => tag.id, '@geodist' => 0..100000},
:geo => [lat, lng]
).collect &:user_id

If you're using Sphinx 2.0.x and the latest Thinking Sphinx, then you may even be able to get the user email addresses without needing to instantiate user objects:

has user.email, :as => :email

UserTagSubscription.search(
:with => {:tag_id => tag.id, '@geodist' => 0..100000},
:geo => [lat, lng]
).collect { |uts| uts.sphinx_attributes['email'] }

That's untested, though... it's theoretically possible, though.

--
Pat

> --
> 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.
>