# How do you do a custom sql call in rails?

**URL:** <https://www.ruby-forum.com/t/how-do-you-do-a-custom-sql-call-in-rails/52199>\
**Category:** Rails\
**Created:** [January 11, 2006, 9:14pm UTC](https://www.ruby-forum.com/t/how-do-you-do-a-custom-sql-call-in-rails/52199 "2006-01-11T21:14:32Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Zack\_C](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/eb9ed0/32.png) [@Zack\_C](https://www.ruby-forum.com/u/Zack_C)\
**Post date:** [January 11, 2006, 9:14pm UTC](https://www.ruby-forum.com/t/how-do-you-do-a-custom-sql-call-in-rails/52199/1 "2006-01-11T21:14:32Z")

</div>

Hi.

I am developing an industry specific crm that allows busineses to track  
customers in a particular way. Each customer will be assigned a  
customer  
number. The first customer will have a customer number of 1, the second  
customer\_number = 2, etc… Note that this customer number is not  
global  
but particular to the account that created the customer (see schema snip  
below).

I need to have a way of generating the next available number for that  
particular account. I have used a custom sequence table to do this type  
of  
thing in the past. The sql I’ve used is the following:

# begin update customer\_number\_sequences set current\_number = current\_number + 1 where account\_id = 12345 select current\_number from customer\_number\_sequences where account\_id

12345  
commit

So my question is how if it all can I integrate this into my  
@customer.save  
call???  
Should I add a before\_create method to the Customer model and do a  
custom  
sql call?  
If so what is the sytax on this call? self.connection.select(…)?  
Should I just use the find\_by\_sql() method?

Any insight would be greatly appreciated.

Thanks,  
Zack

— Schema snip —

create table accounts (  
id int unsigned not null auto\_increment,  
…  
primary key (id)  
) engine=innodb;

create table customer\_number\_sequences (  
account\_id int unsigned not null,  
current\_number int unsigned default 0,  
foreign key (account\_id) references accounts(id) on update cascade on  
delete cascade  
) engine=innodb;

create table customers (  
id int unsigned not null auto\_increment,  
account\_id int unsigned not null,  
customer\_number int unsigned not null,  
…  
foreign key (account\_id) references accounts(id) on update cascade on  
delete cascade,  
primary key (id)  
) engine=innodb;

---

<div class="post-metadata">

**Author:** ![Zack\_C](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/eb9ed0/32.png) [@Zack\_C](https://www.ruby-forum.com/u/Zack_C)\
**Post date:** [January 11, 2006, 9:52pm UTC](https://www.ruby-forum.com/t/how-do-you-do-a-custom-sql-call-in-rails/52199/2 "2006-01-11T21:52:21Z")

</div>

Hi Zack,

Find customer with the highest account number

@last\_cust = Customer.find(:first, :order=\> “acc\_no DESC”)  
@new\_acc\_no = @last\_cust.acc\_no + 1

and then pass that to your new customer object.

For custom sql try

@sql = “INSERT INTO mytable (col1,col1) VALUES (val1,val2)”  
ActiveRecord::Base.connection.execute(@sql)

Regards

Adam

Zack C. wrote:

> Hi.
> 
> I am developing an industry specific crm that allows busineses to track  
> customers in a particular way. Each customer will be assigned a  
> customer  
> number. The first customer will have a customer number of 1, the second  
> customer\_number = 2, etc… Note that this customer number is not  
> global  
> but particular to the account that created the customer (see schema snip  
> below).
> 
> I need to have a way of generating the next available number for that  
> particular account. I have used a custom sequence table to do this type  
> of  
> thing in the past. The sql I’ve used is the following:
> 
> # begin update customer\_number\_sequences set current\_number = current\_number + 1 where account\_id = 12345 select current\_number from customer\_number\_sequences where account\_id
> 
> 12345  
> commit
> 
> So my question is how if it all can I integrate this into my  
> @customer.save  
> call???  
> Should I add a before\_create method to the Customer model and do a  
> custom  
> sql call?  
> If so what is the sytax on this call? self.connection.select(…)?  
> Should I just use the find\_by\_sql() method?
> 
> Any insight would be greatly appreciated.
> 
> Thanks,  
> Zack
> 
> — Schema snip —
> 
> create table accounts (  
> id int unsigned not null auto\_increment,  
> …  
> primary key (id)  
> ) engine=innodb;
> 
> create table customer\_number\_sequences (  
> account\_id int unsigned not null,  
> current\_number int unsigned default 0,  
> foreign key (account\_id) references accounts(id) on update cascade on  
> delete cascade  
> ) engine=innodb;
> 
> create table customers (  
> id int unsigned not null auto\_increment,  
> account\_id int unsigned not null,  
> customer\_number int unsigned not null,  
> …  
> foreign key (account\_id) references accounts(id) on update cascade on  
> delete cascade,  
> primary key (id)  
> ) engine=innodb;

---

<div class="post-metadata">

**Author:** ![Zack\_C](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/eb9ed0/32.png) [@Zack\_C](https://www.ruby-forum.com/u/Zack_C)\
**Post date:** [January 12, 2006, 12:23am UTC](https://www.ruby-forum.com/t/how-do-you-do-a-custom-sql-call-in-rails/52199/3 "2006-01-12T00:23:22Z")

</div>

or

Model.find\_by\_sql(“put sql text here between double quotes”)

---

<div class="post-metadata">

**Author:** ![Zack\_C](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/eb9ed0/32.png) [@Zack\_C](https://www.ruby-forum.com/u/Zack_C)\
**Post date:** [January 12, 2006, 12:23am UTC](https://www.ruby-forum.com/t/how-do-you-do-a-custom-sql-call-in-rails/52199/4 "2006-01-12T00:23:21Z")

</div>

Adam,

Great stuff. I searched and searched for ActiveRecord::Base.connection  
method information. Is there some place I missed? (tried the api docs,  
mailing list web bridge search, two ruby/rails book, the wiki, google,  
…)

The custom sql option is transaction safe which I like although the  
first  
option you gave is of course easier. I may just use a unique index on  
the  
columns (customer\_number and account\_id) and the Customer.find call and  
let  
the db complain if there is threading issue that assigns the same  
customer  
number (unlikely, but still possible).

I really appreciate your help.

Thanks again,  
Zack