I''m trying to build a page similar to a delicious page that is a
listing of
your bookmarks in this format:
1. Querying serialized data in
ActiveRecord<http://www.karmiccoding.com/articles/2005/11/29/querying-serialized-data-in-activerecord>
Hack
to search serialized fields
to rails <http://del.icio.us/djsodom/rails> ... and 4 other
people<http://del.icio.us/url/acd90e7ad2ec718ead225bc48d7c68dc>... on
2006-01-17 ...
edit<http://del.icio.us/djsodom?url=http%3A%2F%2Fwww.karmiccoding.com%2Farticles%2F2005%2F11%2F29%2Fquerying-serialized-data-in-activerecord&jump=no>/
delete<http://del.icio.us/djsodom?delete=acd90e7ad2ec718ead225bc48d7c68dc&jump=no>
2. ProgrammableWeb: Web 2.0 API
Reference<http://programmableweb.com/apis> Good
listing of all the APIs
to reference <http://del.icio.us/djsodom/reference>
programming<http://del.icio.us/djsodom/programming>... and
85 other
people<http://del.icio.us/url/76b171f78b8e4eb0851ae24a1994f105>...
on 2006-01-06 ...
edit<http://del.icio.us/djsodom?url=http%3A%2F%2Fprogrammableweb.com%2Fapis&jump=no>/
delete<http://del.icio.us/djsodom?delete=76b171f78b8e4eb0851ae24a1994f105&jump=no>
There are several things going on there that make it
hard/impossible/inefficient to do it one query. I''m soliciting tips on
how
to do this most efficiently.
For me, the data is in 4 tables.
Bookmarks (id, bookmark)
Tags (id, tag)
Tags_Bookmarks (tag_id, bookmark_id, user_id)
Users (id, username)
I have a query that can get me most of the info I need, but not necessarily
in the format I need it in.
sql = "SELECT jt.tag_id, b.id bid, t.tag, b.bookmark, jt.created_on
FROM tags_bookmarks jt
LEFT JOIN tags t ON jt.tag_id = t.id
LEFT JOIN bookmarks b ON jt.bookmark_id = b.id
WHERE jt.user_id = #{uid}"
GROUP BY tag_id
ORDER BY #{sort}"
That returns data in this format:
tag_id, bid, tag, bookmark, created_on
2, 23, Tag1, This is a bookmark, 12/25/06
3, 23, Tag2, This is a bookmark, 12/26/06
7, 25, Tag5, Different bookmark, 12/26/05
Problem 1: If I print out those results using a collection, "This is a
bookmark" would be shown twice - once tagged with Tag1 and again tagged
with
Tag2. I want to list to look like:
1. This is a bookmark
<http://www.karmiccoding.com/articles/2005/11/29/querying-serialized-data-in-activerecord>to
Tag1 <http://del.icio.us/djsodom/rails> Tag2 ... and 4 other
people<http://del.icio.us/url/acd90e7ad2ec718ead225bc48d7c68dc>... on
12/25/06 ...
edit<http://del.icio.us/djsodom?url=http%3A%2F%2Fwww.karmiccoding.com%2Farticles%2F2005%2F11%2F29%2Fquerying-serialized-data-in-activerecord&jump=no>/
delete<http://del.icio.us/djsodom?delete=acd90e7ad2ec718ead225bc48d7c68dc&jump=no>
...where Tag1 and Tag2 are shown within one bookmark. What can I do to get
those listed under 1 bookmark?
Problem 2: What''s the best way to get the "...and 4 other
people" info? The
one way I can think of is in my view, where I use a partial collection to
print out each question, I call a helper function to count other users also
bookmarking this url. For instance:
userBookmarks.rhtml
<div id="your_bookmarks">
<%= render :partial => "bookmark", :collection =>
@bookmarks
%>
</div>
and then inside a partial _bookmark.rhtml:
<% otherUserCount
call_to_helper_that_counts_other_users_also_bookmarking_this_url(
bookmark.bookmark_id)
<h4 class="bookmarkList"><%=
link_to(bookmark.bookmark,
:controller => "content", :action => "bookmark")
%></h4>
<span class="smallLight">to <%=
link_to(bookmark.tag)<snip>
</div>
If I list 20 questions, then that would mean 20 calls using the
call_to_helper... function. Is there a more efficient way?
Thanks very much for any suggestions.
Steve
http://www.smarkets.net
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060123/7a8d7024/attachment-0001.html