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

rubyonrails-talk
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: <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

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"}   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

4

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

5

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

6

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

7

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

8

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

9

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

10

@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

11

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

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.

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:

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

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

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 tried in same way but how to give the two collecton_select arrays in my code:

i tried like this:

18

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:

19

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

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