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

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: <input type="string" id="search_form" name="search" />
<img id="spinner" src="/images/indicator.gif" style="display: none;" /

<div id="results"></div>
<%= 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 %>
  <ul>
    <% for stream in @streams %>
      <li>
        <%= h(stream.name) %>
      </li>
    <% end %>
  </ul>
<% 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

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

try with this code

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"}
  e[4;36;1mStream Load (0.000000)e[0m e[0;1mSELECT * FROM `streams` e
[0m
Rendering template within layouts/streams
Rendering streams/index
  e[4;35;1mStream Columns (0.000000)e[0m e[0mSHOW FIELDS FROM
`streams`e[0m
  e[4;36;1mStream Load (0.000000)e[0m e[0;1mSELECT * FROM `streams`
WHERE (`streams`.`id` = 1) e[0m
  e[4;35;1mTag Load (0.000000)e[0m e[0mSELECT tags.* FROM tags INNER
JOIN taggings ON tags.id = taggings.tag_id WHERE
((taggings.taggable_type = 'Stream') AND (taggings.taggable_id = 1)) e
[0m
  e[4;36;1mStream Load (0.000000)e[0m e[0;1mSELECT * FROM `streams`
WHERE (`streams`.`id` = 2) e[0m
  e[4;35;1mTag Load (0.000000)e[0m e[0mSELECT tags.* FROM tags INNER
JOIN taggings ON tags.id = taggings.tag_id WHERE
((taggings.taggable_type = 'Stream') AND (taggings.taggable_id = 2)) e
[0m
Completed in 0.03100 (32 reqs/sec) | Rendering: 0.01500 (48%) | DB:
0.00000 (0%) | 200 OK [http://localhost/streams]

thanks
srikanth

Try to use "or" in the places of "," in conditions.

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

@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?

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

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

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

@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

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

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"

Hi Priya,

below are the details of tables and corresponding taggings between
streams and tags tables in taggings table.

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:

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 ) ;" %>

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

<div id="results"></div>

<%= 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'" %>

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

hi ,

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

i tried like this:

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 ,

i am not clear about how to intergrate the logic you have given with
my application.

in my logic i have a observe_field it will take the parameter s from
arry assignd as input to this:
e.g:
search:<%= select_tag 'category',options_for_select([['mpeg2

25fps'],
                      ['mpeg2 30fps'],
                      ['mpeg2'],
                      ['1920x1080'],
                      ['h264'],
                      ['aac']], to_s), :onchange =>
"content.category(this,notnull ) ;" %>

from the above code the observe_field is taking the params from category.like
<%= observe_field 'category',.....%>
here my doubt is if i have one more dropdown box box with name
codecname how to add the name of that array with the observe_field?
can you tell me is it possible to by this way?

thanks
Srikanth

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