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