# SQL question

**URL:** <https://www.ruby-forum.com/t/sql-question/152670>\
**Category:** Rails\
**Created:** [November 6, 2008, 9:44am UTC](https://www.ruby-forum.com/t/sql-question/152670 "2008-11-06T09:44:07Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![ther](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/977dab/32.png) [@ther](https://www.ruby-forum.com/u/ther)\
**Post date:** [November 6, 2008, 9:44am UTC](https://www.ruby-forum.com/t/sql-question/152670/1 "2008-11-06T09:44:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ther](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/977dab/32.png) [@ther](https://www.ruby-forum.com/u/ther)\
**Post date:** [November 6, 2008, 3:23pm UTC](https://www.ruby-forum.com/t/sql-question/152670/2 "2008-11-06T15:23:14Z")

</div>

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](http://developer.mimer.com/validator/parser200x/index.tml)

---

<div class="post-metadata">

**Author:** ![ther](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/977dab/32.png) [@ther](https://www.ruby-forum.com/u/ther)\
**Post date:** [November 6, 2008, 3:24pm UTC](https://www.ruby-forum.com/t/sql-question/152670/3 "2008-11-06T15:24:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ther](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/977dab/32.png) [@ther](https://www.ruby-forum.com/u/ther)\
**Post date:** [November 7, 2008, 12:42pm UTC](https://www.ruby-forum.com/t/sql-question/152670/4 "2008-11-07T12:42:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ther](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/977dab/32.png) [@ther](https://www.ruby-forum.com/u/ther)\
**Post date:** [November 7, 2008, 11:37am UTC](https://www.ruby-forum.com/t/sql-question/152670/5 "2008-11-07T11:37:17Z")

</div>

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](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