Rails - Jan 2006 - How do you do a custom sql call in rails?

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;

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 Chandler 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;

-- 
Posted via http://www.ruby-forum.com/.

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

-----Original Message-----
From: rails-bounces@lists.rubyonrails.org
[mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Adam Groves
Sent: Wednesday, January 11, 2006 12:52 PM
To: rails@lists.rubyonrails.org
Subject: [Rails] Re: How do you do a custom sql call in rails?

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 Chandler 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;

-- 
Posted via http://www.ruby-forum.com/.
_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails

or

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


On 11-Jan-06, at 1:52 PM, Adam Groves wrote:
> 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 Chandler 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;
>
>
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails