Search function implimentaion with the tag name as the search box entry


#1

hi

i am implimenting the search function using tags.

i have tables called streams and tags.

each entry of the table have a single or multiple tagnames(i am using
acts_as_taggable_on_steroids for tagging).

streams table and correspong tagname to the table entries given below

name resolution codecname framerate tags
dust.mpeg2 1920x1080 mpeg2 30fps mpeg2 1920x1080
mummy.264 1920x1080 h264 30fps h264 1920x1080
dust.aac na aac na aac
amelie.mpeg2 720x480 mpeg2 25fps 720x480

I have implimented a search function it will take search string as
input from the search box created and it will list out the
corresponding stream names .

example:
search box entry is: dust.aac
output is :dust.aac

But my requirement is if i use the tag name as the search box entry i
should get the details of streams from the stream table with tag
matching to that streams.
e.g:
search box entry is:aac
expected output is:dust.aac

i am getting the error like :
Mysql::Error: Unknown column ‘tag’ in ‘where clause’: SELECT * FROM
streams WHERE ((LOWER(tag) LIKE ‘%h%’))

below is the my application to impliment the search function based on
tag name entry:

1)in index file i have added the code to create the search box:

Search:
<img id=“spinner” src="/images/indicator.gif" style=“display: none;” /

<%= observe_field 'search_form', :frequency => 0.5, :update => 'results', :url => { :controller => 'streams', :action=> 'get_results' }, :with => "'search_text=' + escape(value)", :loading => "document.getElementById ('spinner').style.display='inline'", :loaded => "document.getElementById('spinner').style.display='none'" %> this code is able to take the string what we enterd.

2)to do the search function i have added a method in controller like
this:
def get_results
if request.xhr?
if params[‘search_text’].strip.length > 0
terms = params[‘search_text’].split.collect do |word|
“%#{word.downcase}%”
end
if blank?
flash[:notice] = ‘Stream was successfully updated.’
else
@streams = Stream.find_tagged_with(
:all,
:conditions => [
( ["(LOWER(name) LIKE ?)"] *
terms.size ).join(" AND "),
* terms.flatten
]
)
end
end
render :partial => “search”
else
redirect_to :action => “index”
end
throwing errors like:
Mysql::Error: Column ‘name’ in where clause is ambiguous: SELECT
DISTINCT streams.* FROM streams INNER JOIN taggings
streams_taggings ON streams_taggings.taggable_id = streams.id AND
streams_taggings.taggable_type = ‘Stream’ INNER JOIN tags streams_tags
ON streams_tags.id = streams_taggings.tag_id WHERE ((LOWER(name) LIKE
‘%dust%’) AND (streams_tags.name LIKE ‘all’))
3)the result is collecting in _search.html.erb looks like

% if @streams %>

    <% for stream in @streams %>
  • <%= h(stream.name) %>
  • <% end %>
<% end %>

i have to impliment the same functionality with tag name as the entry
for search how can i do that.
give some ideas to make it work.

thanks
sriaknth


#2

@stream = Stream.find(:all, :conditions => [‘title LIKE ?’,
‘%’+params[:search_text]+’%’])

try with this code


#3

hi priya,

thanks for your reply,

but what you are telling is similar to:
@streams = Stream.find( :all,:conditions => [( ["(LOWER(name)
LIKE ?)"] * terms.size ).join(" AND "),* terms.flatten] )
with this i am able to search the table for any one column.

my query is :
1)i have to search with any column name
eg:name or codecname or resolution or frame rate
to do this i updated my query like:
@streams = Stream.find( :all,:conditions => [( ["(LOWER(name) LIKE ?),
(LOWER(resolution) LIKE ?),(LOWER(codecname) LIKE ?),(LOWER(framerate)
LIKE ?)",search_text,.search_text,search_text,search_text] *
terms.size ).join(" AND "),* terms.flatten] )

but i could not get the output throwing mysql erros.

2)i need to search with the tagname instead of stream table entries to
do this updated my query like;
@streams = Stream.find_tagged_with(:all, :conditions => [( ["(LOWER
(name) LIKE ?)"] * terms.size ).join(" AND "), * terms.flatten ] )

if i enter a tagname mpeg2 in search box it shold give reult as:
dust.mpeg2

but i did not see any result on webpage and no error.So i have checked
in development.log
searching is happening but it could not able to print on page.

log file details:

Processing StreamsController#index (for 127.0.0.1 at 2008-12-10
15:19:35) [GET]
Session ID:
BAh7BzoMY3NyZl9pZCIlYTg0NGU1YTNiZGMxNzEzYzEwYTdhMWRiOWFmNmFh
%0AMDgiCmZsYXNoSUM6J0FjdGlvbkNvbnRyb2xsZXI6OkZsYXNoOjpGbGFzaEhh
%0Ac2h7AAY6CkB1c2VkewA%3D–d23cab72ea46e47489ac826945b29bbc3431a039
Parameters: {“action”=>“index”, “controller”=>“streams”}
[4;36;1mStream Load (0.000000) [0m [0;1mSELECT * FROM streams
[0m
Rendering template within layouts/streams
Rendering streams/index
[4;35;1mStream Columns (0.000000) [0m [0mSHOW FIELDS FROM
streams [0m
[4;36;1mStream Load (0.000000) [0m [0;1mSELECT * FROM streams
WHERE (streams.id = 1) [0m
[4;35;1mTag Load (0.000000) [0m [0mSELECT tags.* FROM tags INNER
JOIN taggings ON tags.id = taggings.tag_id WHERE
((taggings.taggable_type = ‘Stream’) AND (taggings.taggable_id = 1))
[0m
[4;36;1mStream Load (0.000000) [0m [0;1mSELECT * FROM streams
WHERE (streams.id = 2) [0m
[4;35;1mTag Load (0.000000) [0m [0mSELECT tags.* FROM tags INNER
JOIN taggings ON tags.id = taggings.tag_id WHERE
((taggings.taggable_type = ‘Stream’) AND (taggings.taggable_id = 2))
[0m
Completed in 0.03100 (32 reqs/sec) | Rendering: 0.01500 (48%) | DB:
0.00000 (0%) | 200 OK [http://localhost/streams]

thanks
srikanth
On Dec 10, 1:43 pm, Priya D. removed_email_address@domain.invalid


#4

Try to use “or” in the places of “,” in conditions.


#5

@stream = Stream.find(:all, :conditions => [‘name LIKE ? or resolution
LIKE ? or codecname LIKE ? or framerate LIKE ? or tags LIKE ?’,
‘%’+params[:search_text]+’%’,’%’+params[:search_text]+’%’,’%’+params[:search_text]+’%’,
‘%’+params[:search_text]+’%’,’%’+params[:search_text]+’%’])

I checked with this. Its working. I don’t know for what you are using
LOWER here?


#6

hi priya ,

you are right,

but here only one column entries of table it is able to search and
listing out.
if i enter any column entry i should get the stream name from table.

ex:
name resolution codecname framerate tags
dust.mpeg2 1920x1080 mpeg2 30fps mpeg2 1920x1080
mummy.264 1920x1080 h264 30fps h264 1920x1080
dust.aac na aac na aac
amelie.mpeg2 720x480 mpeg2 25fps 720x480

from above table when i enter the any string from name column i am
able to see the correponding names listing out .
in the same way if enter string from other columns i should see the
name of stream correspond to the string enter.
eg:
if enter “mpeg2” in search box
output:
amelie.mpeg2
dust.mpeg2

how can i do this.

can you tell me any suggestions to do that?

thanks
srikanth
On Wed, Dec 10, 2008 at 3:57 PM, Priya B.


#7

@stream = Stream.find(:all, :joins => “streams inner join tags as t on
streams.id=t.id”, :conditions => [‘name LIKE ? or resolution LIKE ? or
codecname LIKE ? or framerate LIKE ? or tags LIKE ? or t.name LIKE ?’,
‘%’+params[:search_text]+’%’,’%’+params[:search_text]+’%’,’%’+params[:search_text]+’%’,
‘%’+params[:search_text]+’%’,’%’+params[:search_text]+’%’,’%’+params[:search_text]+’%’],
:select => “steams.name”)

just try with this. I think it’ll work.


#8

hi priya,

thanks alot its working for me .
and one more query .

i have two tables like:
1)streams table with columns
id name resolution codecname framerate

2)tags table with columns like;
id name

previously i am searching in streams table using any column entry of
that table.
And using taggable plugin im joing two tables.

now i want to search the table entries of streams table using the tag
name.
eg:
stream table entris
tags table entris
id name resolution codecname framerate
id name
1 dust.mpeg2 1920x1080 mpeg2 30fps
1 mpeg2 1920x1080
2 mummy.264 1920x1080 h264 30fps
2 h264 1920x1080
3 dust.aac na aac na
3 aac
4 amelie.mpeg2 720x480 mpeg2 25fps
4 720x480

now i want to searcg the table streams using tagname;

if i enter tagname 720x480
expected output:amelie.mpeg2

to do this wat are all the things i need to change?
can you give idea on this?

thanks
srikanth

On Wed, Dec 10, 2008 at 4:44 PM, Priya B.


#9

@stream = Stream.find(:all, :joins => “streams as s inner join tags as t
on
s.id=t.id”, :conditions => [‘s.name LIKE ? or s.resolution LIKE ? or
s.codecname LIKE ? or s.framerate LIKE ? or s.tags LIKE ? or t.name LIKE
?’,
‘%’+params[:search_text]+’%’,’%’+params[:search_text]+’%’,’%’+params[:search_text]+’%’,
‘%’+params[:search_text]+’%’,’%’+params[:search_text]+’%’,’%’+params[:search_text]+’%’],
:select => “name”)

SELECT streams.name FROM streams .
In this no need to give streams.name, so repalce streams.name by just
“name”. Just try above query


#10

hi priya,
thank you for your response,

your help is precious to me as i am very new to sql and rails.

1)i used “name” instead streams.name but still the same error i am
seeing.like:

Column ‘name’ in field list is ambiguous: SELECT name FROM streams
streams inner join tags as t on
stream.id=t.id WHERE (name LIKE ‘%mpeg2%’ or resolution LIKE
‘%mpeg2%’ or
codecname LIKE ‘%mpeg2%’ or framerate LIKE ‘%mpeg2%’ or tags LIKE
‘%mpeg2%’ or t.name LIKE ‘%mpeg2%’)

and my query is do we need to mention all parameters in query when we
are trying to access the streams table data using tagname?
and do we need to change anywhere in the controller to get the all the
taglist in to tags.

:e.g if we are using single table in controller we use:
@Streams = Stream.find(:all) in index class
now sterams contain all the streams details.

do we need to do the same for tag table also before collecting the
data from streams table?
give me some idea how to do the search using tagname?

thanks alot
sriaknth

On Thu, Dec 11, 2008 at 9:53 AM, Priya B.


#11

hi priya,

i ahve tried the same way,but i am getting errors like:

ActiveRecord::StatementInvalid in StreamsController#get_results
Mysql::Error: Column ‘name’ in where clause is ambiguous: SELECT
streams.name FROM streams streams inner join tags as t on
streams.id=t.id WHERE (name LIKE ‘%mpeg2%’ or resolution LIKE
‘%mpeg2%’ or
codecname LIKE ‘%mpeg2%’ or framerate LIKE ‘%mpeg2%’ or tags LIKE
‘%mpeg2%’ or t.name LIKE ‘%mpeg2%’)

can you tell me ,where is the problem?

regards
Srikanth

On Wed, Dec 10, 2008 at 6:02 PM, Priya B.


#12

If you want to search with streams field, then you have to pass all
those parameters else its enough to pass tag field name alone.

Also we can join the 2 tables so no need to give like
@streams=Stream.fine(:all) for tag.

Do you have column heading as “name” in tag table? Check it and give the
correct attribute name in the place of “name”


#13

Hi Priya,

below are the details of tables and corresponding taggings between
streams and tags tables in taggings table.
mysql> select * from streams;
±—±-----------±----------------------------------±-----------±--------±----------±----------±-----------±-----------+
| id | name | location | resolution |
bitrate | framerate | codecname | created_on | updated_on |
±—±-----------±----------------------------------±-----------±--------±----------±----------±-----------±-----------+
| 1 | dust.mpeg2 | E:\streams\RED_S_J_JE_In_NT.divx | 1920x1080 |
6mbps | 30fps | mpeg2 | 2008-12-08 | 2008-12-08 |
| 2 | mummy.264 | E:\streams\RED_S_J_JE_In_NT.divx | 720x480 |
4mbps | 30fps | h264 | 2008-12-08 | 2008-12-08 |
| 3 | hiphop.aac | na | na |
na | na | aac | 2008-12-10 | 2008-12-10 |
| 5 | jazz.aac | na | na |
na | na | aac | 2008-12-10 | 2008-12-10 |
±—±-----------±----------------------------------±-----------±--------±----------±----------±-----------±-----------+
4 rows in set (0.03 sec)

mysql> select * from tags;
±—±-------------+
| id | name |
±—±-------------+
| 1 | mpeg2 |
| 2 | h264 720x480 |
| 3 | aac |
±—±-------------+
3 rows in set (0.00 sec)

mysql> select * from taggings;
±—±-------±------------±--------------+
| id | tag_id | taggable_id | taggable_type |
±—±-------±------------±--------------+
| 1 | 1 | 1 | Stream |
| 2 | 2 | 2 | Stream |
| 3 | 3 | 3 | Stream |
| 4 | 3 | 5 | Stream |
±—±-------±------------±--------------+
4 rows in set (0.00 sec)

can i use the query to search with tag field like:

@streams = Stream.find(:all, :joins => “streams inner join tags as t on
stream.id=t.id”, :conditions => [‘name LIKE ?’,
‘%’+params[:search_text]+’%’],:select => “name”)

from the above query what i understand is:
it joing streams and tags table but using the “name” column of tags
table it is finding and storing the result of stream names from
streams table in @streams array.

if i a m wrong let me know ?
do i need to mention anywhere association between tables in the
corresponding models?

is there any procedure to make it work ?

thanks
srikanth

On Thu, Dec 11, 2008 at 10:46 AM, Priya B.


#14

Hi Priya ,

thanks for your suggestions.

Finally i am able to do the search with tagname.
presently i can search the database by typing the string in the search
box.
But now i want to provide a dropdown box of search parameters instead of
typing.
here my search application is usin ajax .
can you give any suggestions to create dropdown box and once i select
the option from dropdown box how to link with it for search function?

take a look at my search code:

in index.html.erb

Search:


<%= observe_field ‘search_form’,
:frequency => 0.5,
:update => ‘results’,
:url => { :controller => ‘streams’, :action=> ‘get_results’ },
:with => “‘search_text=’ + escape(value)”,
:loading =>
“document.getElementById(‘spinner’).style.display=‘inline’”,
:loaded => “document.getElementById(‘spinner’).style.display=‘none’”
%>

the search mthod defined in controller is

def get_results
if request.xhr?
if params[‘search_text’].strip.length > 0
terms = params[‘search_text’].split.collect do |word|
“%#{word.downcase}%”
end
if blank?
flash[:notice] = ‘Stream was successfully updated.’
else
@streams = Stream.find_tagged_with(params[:search_text])
end
end
render :partial => “search”
else
redirect_to :action => “index”
end
end

thnaks for any suggetions and help

regards
Srikanth.


#15

hi priya,

thnaks again
i am able to get the dropdown box for search text box.And able to get
the searched streams list.

i have tried like this.
-----------------------------------in
index.html.erb-----------------------------------------------
search :<%= select_tag ‘category’,options_for_select([[‘mpeg2 25fps’],
[‘mpeg2 30fps’],
[‘mpeg2’],
[‘1920x1080’],
[‘h264’],
[‘aac’]], to_s), :onchange =>
“content.category(this,notnull ) ;” %>

<%= observe_field ‘category’,
:frequency => 0.5,
:update => ‘results’,
:url => { :controller => ‘streams’, :action=> ‘get_results’ },
:with => “‘search_text=’ + escape(value)”,
:loading =>
“document.getElementById(‘spinner’).style.display=‘inline’”,
:loaded => “document.getElementById(‘spinner’).style.display=‘none’”
%>

But this procedure is very bad beacuase we have to give the number
combinations for dropdown box if i have number of searchable options.

So now what i want to do is like this:
e.g
i have two dropdown boxes one for codecformat one for resolution
using drop down box i can select codecforformat as mpeg2 and
resolution as 1920x1080 now by using the search button i want to
search the streams for the corresponding selection.

i hope you understand my problem.

Can we do it?
Can you give me any suggestion or idea to do this?

thanks for spending time for reading my querys and replying

regards
Srikanth

On Fri, Dec 12, 2008 at 11:32 AM, Priya B.


#16

Ya you can do…
But one thing, its not efficient to give the select options in code…
You have to get from DB… So that you can use collection_select…
Use 2 collection_select for both the drop down boxes…
Then use normal search query for search…


#17

Hi,
In the place of textbox in template, you can display the text using
collection_select.


#18

hi ,

in tried in same way but how to give the two collecton_select arrays
in my code:

i tried like this:

codecformat:<%= select_tag
‘codecformat’,options_for_select([[‘mpeg2’],
[‘h264’],
[‘aac’]], to_s), :onchange =>
“content.category(this,notnull ) ;” %>

combination:<%= select_tag ‘category’,options_for_select([[‘mpeg2
25fps’],
[‘mpeg2 30fps’],
[‘mpeg2’],
[‘1920x1080’],
[‘h264’],
[‘aac’]], to_s), :onchange =>
“content.category(this,notnull ) ;” %>

now i have two colletion_select showned above

in observe_field <%= observe_field ‘category’%>
how to give this two names:

thanks
srikanth

On Fri, Dec 12, 2008 at 1:24 PM, Priya B.


#19

Codecformat:
<%=
collection_select(:destinationtable_name,:destinationcolumn_name,Sourcetablename.find(:all
),sourcecolumn_name,:Sourcetablename) %>
Eg:
<%= collection_select(:author,:name,Book.find(:all ),author_name,:book)
%>

sreekanth.G wrote:

hi ,

in tried in same way but how to give the two collecton_select arrays
in my code:

i tried like this:

codecformat:<%= select_tag
‘codecformat’,options_for_select([[‘mpeg2’],
[‘h264’],
[‘aac’]], to_s), :onchange =>
“content.category(this,notnull ) ;” %>

combination:<%= select_tag ‘category’,options_for_select([[‘mpeg2
25fps’],
[‘mpeg2 30fps’],
[‘mpeg2’],
[‘1920x1080’],
[‘h264’],
[‘aac’]], to_s), :onchange =>
“content.category(this,notnull ) ;” %>

now i have two colletion_select showned above

in observe_field <%= observe_field ‘category’%>
how to give this two names:

thanks
srikanth

On Fri, Dec 12, 2008 at 1:24 PM, Priya B.


#20

hi ,

i got stuck there itself,
can you tell me whch document/book talks about the implementation of
search with dropdown box params?

thanks
srikanth