SQL question

Rails
1

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

2

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

3

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

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

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

4

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

5

Pau C. 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.tml

Of 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