How do you do a custom sql call in rails?

Rails
1

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;

2

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;

3

or

Model.find_by_sql(“put sql text here between double quotes”)

4

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