# Del.icio.us-like output

**URL:** <https://www.ruby-forum.com/t/del-icio-us-like-output/96387>\
**Category:** Rails\
**Created:** [January 23, 2006, 11:32pm UTC](https://www.ruby-forum.com/t/del-icio-us-like-output/96387 "2006-01-23T23:32:27Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [January 23, 2006, 11:32pm UTC](https://www.ruby-forum.com/t/del-icio-us-like-output/96387/1 "2006-01-23T23:32:27Z")

</div>

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](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](http://del.icio.us/djsodom/rails) … and 4 other  
people[http://del.icio.us/url/acd90e7ad2ec718ead225bc48d7c68dc](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](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](http://del.icio.us/djsodom?delete=acd90e7ad2ec718ead225bc48d7c68dc&jump=no)
2. ProgrammableWeb: Web 2.0 API  
Reference[http://programmableweb.com/apis](http://programmableweb.com/apis) Good  
listing of all the APIs  
to reference [http://del.icio.us/djsodom/reference](http://del.icio.us/djsodom/reference)  
programming[http://del.icio.us/djsodom/programming](http://del.icio.us/djsodom/programming)… and  
85 other  
people[http://del.icio.us/url/76b171f78b8e4eb0851ae24a1994f105](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](http://del.icio.us/djsodom?url=http%3A%2F%2Fprogrammableweb.com%2Fapis&jump=no)/  
delete[http://del.icio.us/djsodom?delete=76b171f78b8e4eb0851ae24a1994f105&jump=no](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](http://www.karmiccoding.com/articles/2005/11/29/querying-serialized-data-in-activerecord)to  
Tag1 [http://del.icio.us/djsodom/rails](http://del.icio.us/djsodom/rails) Tag2 … and 4 other  
people[http://del.icio.us/url/acd90e7ad2ec718ead225bc48d7c68dc](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](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](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

```

%\>

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”) %\>  
to \<%= link\_to(bookmark.tag)

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](http://www.smarkets.net)

---

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [January 24, 2006, 2:36am UTC](https://www.ruby-forum.com/t/del-icio-us-like-output/96387/2 "2006-01-24T02:36:55Z")

</div>

Hi Steve,

try to do it the railish way 😉

first, get away from writing SQL by hand. Have a look at  
act\_as\_taggable. With this plugin you can easily tag items in a table.  
Then connect links and user via belongs\_to and so in your model.

I have your delicous-like look with my links, and get this with

```
<h4><a href="<%= link.url %>"><%=h link.name %></a></h4>

<%= link.text %>

```

from \<%= link.user.login %\> in \<% for tag in link.tag\_names -%\> \<%= link\_to tag, :action =\> 'tag', :id =\> tag %\> \<% end -%\> (this is code in a partial)

In my controller I have no SQL:  
@link\_pages, @links = paginate :link, :per\_page =\> 10, :order\_by =\>  
‘created\_on DESC’

HTH,  
Beate

---

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [January 24, 2006, 12:31pm UTC](https://www.ruby-forum.com/t/del-icio-us-like-output/96387/3 "2006-01-24T12:31:05Z")

</div>

Hi Beate,

Thanks for your suggestions. I must admit sometimes I think it is easier  
to  
write the SQL by hand. You answered my question.

Steve  
[http://www.smarkets.net](http://www.smarkets.net)