Forum: Ruby on Rails Custom SQL Question

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
B75a2a570283dda68209b41b5d1cf316?d=identicon&s=25 Thorsten Brückner (shayu70)
on 2006-03-02 17:59
Hello,

 can search with following SQL Statement:

def execute_search

    @result = Search.find_by_sql ["SELECT * FROM customers WHERE
firstname LIKE ?  LIMIT 10",params[:firstname]]

    render :template => "search/resultlist", :layout => false

end

My question is, how do I use 2 or more Parameters - and with the "%"? (I
Like to use SQL-Statements because later i will have Queries over a lot
of tables)


Thank you


Thorsten
Bf66e10c8fc4abefebde0425e7f6f15a?d=identicon&s=25 Norman Timmler (Guest)
on 2006-03-03 12:22
(Received via mailing list)
Am Donnerstag, den 02.03.2006, 17:59 +0100 schrieb Thorsten Brückner:
>
> My question is, how do I use 2 or more Parameters - and with the "%"? (I
> Like to use SQL-Statements because later i will have Queries over a lot
> of tables)

Here is an example of what you might looking for:

@result = Search.find_by_sql(
  [
    "SELECT * FROM customers WHERE firstname LIKE ? and lastname LIKE ?
LIMIT 10",
    "%#{params[:firstname]}%",
    "%#{params[:lastname]}%"
  ]

The first question mark is replaced with the second element in the
array, the second question mark with the third, etc.

"%#{params[:firstname]}%"

You wrap your data into percent signs, before they are injected into the
SQL string.

--
Norman Timmler

http://blog.inlet-media.de
32d1b70dcff9066a90976c0fb18acabe?d=identicon&s=25 Neil Dugan (Guest)
on 2006-03-05 23:47
(Received via mailing list)
Thorsten Brückner wrote:
>
> end
>
> My question is, how do I use 2 or more Parameters - and with the "%"? (I
> Like to use SQL-Statements because later i will have Queries over a lot
> of tables)
>

try something like

@result = Search.find_by_sql ["SELECT * FROM customers WHERE
firstname LIKE ? AND lastname LIKE ?  LIMIT 10",
params[:firstname],params[:lastname]]



>
> Thank you
>
>
> Thorsten
>
>

Regards Neil
B75a2a570283dda68209b41b5d1cf316?d=identicon&s=25 Thorsten Brückner (Guest)
on 2006-03-07 12:09
Thank you,

it works fine!

Thorsten


Norman Timmler wrote:
> Am Donnerstag, den 02.03.2006, 17:59 +0100 schrieb Thorsten Brückner:
>>
>> My question is, how do I use 2 or more Parameters - and with the "%"? (I
>> Like to use SQL-Statements because later i will have Queries over a lot
>> of tables)
>
> Here is an example of what you might looking for:
>
> @result = Search.find_by_sql(
>   [
>     "SELECT * FROM customers WHERE firstname LIKE ? and lastname LIKE ?
> LIMIT 10",
>     "%#{params[:firstname]}%",
>     "%#{params[:lastname]}%"
>   ]
>
> The first question mark is replaced with the second element in the
> array, the second question mark with the third, etc.
>
> "%#{params[:firstname]}%"
>
> You wrap your data into percent signs, before they are injected into the
> SQL string.
>
> --
> Norman Timmler
>
> http://blog.inlet-media.de
This topic is locked and can not be replied to.