This works as expected in sqlite3 select id, title || body as tb from docs where tb like ''%en%'' But fails in postgres because tb is unknown. and works in ms-sql like: select id from docs where title+body like ''%en%'' I didn''t tried Oracle but I guess it has its own sintax too. So my questions are. 1. Is there universal SQL syntax to support this query? 2. (whispering) Could it be done in rails (AR) syntax? 3. Should I create a view and query the view? by TheR -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
This isn''t exactly the same, but it might be good enough for you: select id from docs where title like ''%en%'' or body like ''%en%'' There is an SQL validator here: http://developer.mimer.com/validator/parser200x/index.tml -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Damjan Rems wrote:> > This works as expected in sqlite3 > select id, title || body as tb from docs where tb like ''%en%'' > > But fails in postgres because tb is unknown. >Postgres is pretty finicky about what gets used in where conditions. If I recall correctly, SQL Server allows you to reference aliases in the where, but PG doesn''t (unless I''ve been doing something wrong for the past two years). So to get it work in Postgres, you''d have to repeat the SELECT fragment in the WHERE: select id, title || body as tb from docs where title || body like ''%en%''> So my questions are. > 1. Is there universal SQL syntax to support this query?The duplication is probably the best you''re going to get.> 2. (whispering) Could it be done in rails (AR) syntax?Well, there are at least two ways to do this via AR. First, you could use all finder syntax and have the database do all the work. In that case, you would be doing the same thing, but in AR: Doc.find(:all, :select => ''id, title || body as tb'', :conditions => "title || body like ''%en%''") Since AR is just building a SQL statement, you run into the same problem as when you create it yourself. The second way that I know of would be to pull everything back and remove the ones you don''t want: Doc.find(:all, :select => ''id, title || body as tb'').delete_if { |doc| !doc.tb.include? ''en'' } Or something like that. But that is the less performant option. First, you have to pull all the records back, then loop through them doing a test, and perform an array operation at each deletion. The first way would be better.> 3. Should I create a view and query the view? >You could try that. In my database life, I''ve not been terribly impressed with views and have not made much use of them at all. I don''t know if there are any implications from an AR perspective with views. That would be something to look into. Though, as long as you are just querying, I don''t see what problem there would be. Peace. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Pau Cor wrote:> This isn''t exactly the same, but it might be good enough for you: > > select id from docs where title like ''%en%'' or body like ''%en%'' > > There is an SQL validator here: > http://developer.mimer.com/validator/parser200x/index.tmlOf course. Sometimes you just have to break the problem into smaller peaces. Althow I will have to do some research about performance (>> 10000 records). It should even be faster if string is found in title. Thank you also Phillip. There is so much I have to learn about AR. In the mean time I have found that this: select id from docs where title || body like ''%en%'' works on PG3.3, sqlite3, Oracle 10. But of course not on MS-SQL2005. by TheR -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Damjan Rems wrote:> > In the mean time I have found that this: > > select id from docs where title || body like ''%en%'' > > works on PG3.3, sqlite3, Oracle 10. But of course not on MS-SQL2005. >Right. SQL Server''s string concatenation operator is +. I got tripped up on that a number of times when I started working with Postgres. If this really must be portable to most/all of the databases supported by Rails, you should avoid raw SQL completely. This will be the beginning of many frustrations for you. Unfortunately, that does mean that some things you could have done faster in raw SQL will now take a bit longer because you have to use Ruby constructs, but you will gain the portability. Peace. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---