In our app, we have artists who have_many songs. Some of those songs
might be available for download. I'd like to add an attribute for the
song count, and an attribute for the download count, so something like
this:
class Artist < ActiveRecord::Base
has_many :songs
define_index do
indexes :name, :as => :indexed_name
has songs.size, :as => :total_songs
has songs.count(:all, :conditions => "downloadable = 1"), :as =>
:total_downloads, :type => :integer
where "artists.deleted = 0 AND artists.hidden = 0"
end
end
If I do this, I get the following SQL query:
SELECT SQL_NO_CACHE `artists`.`id` * CAST(6 AS SIGNED) + 0 AS `id` ,
`artists`.`name` AS `indexed_name`, `artists`.`id` AS
`sphinx_internal_id`, 0 AS `sphinx_deleted`, 116757425 AS `class_crc`,
IFNULL('Artist', '') AS `sphinx_internal_class` FROM `artists` LEFT
OUTER JOIN `songs` ON `songs`.`artist_id` = `artists`.`id` WHERE
(`artists`.`id` >= $start AND `artists`.`id` <= $end AND
artists.deleted = 0 AND artists.hidden = 0) GROUP BY `artists`.`id`
ORDER BY NULL
which only joins songs once, and has no count(*) for even the
total_songs attribute, and appears to miss the total_downloads
attribute entirely.
I was able to get this to work with dependent subqueries, e.g.
has "SELECT COUNT(*) FROM songs WHERE songs.artist_id = artists.id",
:as => :total_songs, :type => :integer
However, that slowed down the indexing considerably.
I've now gotten it to work by monkey-patching TS to allow me to add an
sql_query_pre that creates temporary tables for the song counts per
artist and the download counts per artist, and then has_one each of
the temporary tables, but it's pretty messy. Is there a better way of
accomplishing this?
Thanks,
Andrew Watts
[email protected]
--
You received this message because you are subscribed to the Google Groups
"Thinking Sphinx" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/thinking-sphinx?hl=en.
