Forum: Ruby on Rails How do you do a custom sql call in rails?

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.
Zack C. (Guest)
on 2006-01-11 22:14
(Received via mailing list)
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;
Adam G. (Guest)
on 2006-01-11 22:52
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;
Zack C. (Guest)
on 2006-01-12 01:23
(Received via mailing list)
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
Bruce B. (Guest)
on 2006-01-12 01:23
(Received via mailing list)
or

Model.find_by_sql("put sql text here between double quotes")
This topic is locked and can not be replied to.