Rails equivalent of Delphi SetRange()

[markh]

I am currently converting a large Delphi webbroker application to RoR.
The Delphi application makes extensive use of client datasets and
setrange which can limit the range of records returned to a client
dataset in an SQL call without the overhead of making another SQL call.
The setrange call can be issued in a loop to repeatedly change the range
of records viewable in the dataset.

My app frequently issues the setrange command to a dataset in a loop in
order to test for previous game time bookings in a sports application.
Is there any way I can do this in Rails or do I need to repeatedly make
SQL queries to return the data subset?

[markh]

There are a few ways to do this.

One would be using a named_scope in Rails 2 or just plain scope in
Rails 3

class Bookings < ActiveRecord::Base
named_scope :discount, :conditions => { :discount > .5 }

I like to model class methods for pulling specific information from
the database

def self.inactive
find( :all, :conditions => [“inactive = ?”, 1], :order
=> :last_calibrated_on )
end
And then in the controller:

@inactive = Item,inactive

In Rails 3 there is a new query interface (
http://m.onkey.org/2010/1/22/active-record-query-interface )

The neat thing here is that it seems to map well to what you’re doing.
You start off creating a relation that you can then manipulate, but
the query doesn’t fire until you want it too.

Getting those datasets or updating the limits could be done through
javascript on the view page, periodically_call_remote or an observer
on a field.

[markh]

Thanks for your reply. A couple of further comments / question. I am a
noob in Rails.

I like to model class methods for pulling specific information from
the database

def self.inactive
find( :all, :conditions => [“inactive = ?”, 1], :order
=> :last_calibrated_on )
end
And then in the controller:

@inactive = Item.inactive

This looks like it will still fire off a query which is what I want to
avoid.

In Rails 3 there is a new query interface (
http://m.onkey.org/2010/1/22/active-record-query-interface )

Model scoping looks like it might do the job. My problem is that the
loop may reset the client dataset range (its not really the client as in
the browser, the ISAPI sits on the server and the client dataset is a
query result) many thousands of times while testing for a court booking
or a game draw which is not a close repeat of a previous instance.
Making a new query would be very detrimental to performance.

In the above link you provided there is a paragraph on model scoping and
the following example seems to do the job:-

cars = Car.scoped
rich_ppls_cars = cars.order(‘cars.price DESC’).limit(10)

It appears that the call to cars fires the query but the call to
rich_ppls_cars only works on the resultant dataset and does not fire a
new query. Would that be correct? For example, I could then say
poor_ppls_cars = cars.order(‘cars.price ASC’).limit(10) and I would get
different data without a new query?

[markh]

Mark H. wrote:

I am currently converting a large Delphi webbroker application to RoR.
The Delphi application makes extensive use of client datasets and
setrange which can limit the range of records returned to a client
dataset in an SQL call without the overhead of making another SQL call.
The setrange call can be issued in a loop to repeatedly change the range
of records viewable in the dataset

This is probably not a great way of doing things. If you have 1000
records that you’re paging through 10 at a time, there is no reason to
fetch all 1000 if you only want to read 10. Certainly you should avoid
extra queries, but you should also avoid unnecessarily large results.
In the example I gave, I think separate queries would be the better
solution. If you can describe your use case in more detail, it might
help.

My app frequently issues the setrange command to a dataset in a loop in
order to test for previous game time bookings in a sports application.
Is there any way I can do this in Rails or do I need to repeatedly make
SQL queries to return the data subset?

[markh]

Mark H. wrote:

Marnen Laibow-Koser wrote:
If you have 1000

records that you’re paging through 10 at a time, there is no reason to
fetch all 1000 if you only want to read 10.

There aren’t anywhere near that many. Its just that I change the view of
the records returned many times while testing for a game time that
hasn’t been booked by the two teams for the longest period of time while
re booking fixtures. The use case is that game fixtures may be redrawn
and then require new court bookings part way through the season.

I’m still not completely sure I understand exactly what you’re doing,
but it looks to me like you’re running onto trouble because you’re
trying to have the app do queries that should be done on the DB side.
Can you have the database do the calculations and return only the
records you actually want?

[markh]

Marnen Laibow-Koser wrote:
If you have 1000

records that you’re paging through 10 at a time, there is no reason to
fetch all 1000 if you only want to read 10.

There aren’t anywhere near that many. Its just that I change the view of
the records returned many times while testing for a game time that
hasn’t been booked by the two teams for the longest period of time while
re booking fixtures. The use case is that game fixtures may be redrawn
and then require new court bookings part way through the season. Here is
some code excerpts.

while not cdsGamesToBook.Eof do
begin

teamno := cdsGamesToBook.FieldByName('teamA').Value;

cdsTeamsToBook.SetRange([teamno],[teamno]);

if cdsTeamsToBook.RecordCount = 0 then
  begin

  cdsTeamsToBook.CancelRange;

  cdsTeamsToBook.Insert;

  cdsTeamsToBook.FieldByName('teamno').Value := teamno;

  cdsTeamsToBook.Post;

  end;

teamno := cdsGamesToBook.FieldByName('teamB').Value;

cdsTeamsToBook.SetRange([teamno],[teamno]);

In this case the code is just iterating through a list of teams so the
max interations might be around 16 here for 16 teams. The use of
setrange saves querying the DB 16 times. This loop would in turn be in a
separate loop which would issue setrange on the previous games played in
order to find the longest time either team has not previously been
booked to play at a particular time. The result will be that several
small datasets will have setrange applied maybe a dozen times each but
the tests may be many. The actual number of queries performed on the DB
will be very few.

[markh]

Marnen Laibow-Koser wrote:

I’m still not completely sure I understand exactly what you’re doing,
but it looks to me like you’re running onto trouble because you’re

If I want to re book courts after a game redraw part of the way through
a competition, its necessary to check the previous game time bookings
and find a time which has not been played before or find the time which
has not bee played for the longest time.

So, I will get a list of possible times to book and the id numbers of
both teams. If I check through many past games I will normally find that
all times have previously been booked in past games. Say 8 weeks have
previously been played for 6 teams and there are 4 game times available.
Odds are that every time has previously been booked at some time. So the
next iteration, I will reduce the number of past games visible and test
each time for both teams.

Eventually, by reducing the number of visible past games on each
iteration, I will find a time that has not been booked for either team
for the greatest number of weeks and this will be the best time to book
the future game being tested. Because I have 2 teams and many future
games to book I will repeat this process on a dataset of past games many
times.

It doesn’t make sense to get that list of past games many times.
SetRange allows me to reduce the number of past games visible on each
pass of a dataset and then return it to the full set for the next future
game to be tested or the next team to be tested, or the next time to be
tested. This way I can query the past games only once, and then change
the range of visible games on it many times while looking for optimal
times to book each future game to be booked.

I can’t find this ability in Rails.

[markh]

Mark H. wrote:

Marnen Laibow-Koser wrote:

I’m still not completely sure I understand  exactly what you’re doing,Â
but it looks to me like you’re running onto trouble because you’reÂ
Â
If I want to re book courts after a game redraw part of the way throughÂ
a competition, its necessary to check the previous game time bookingsÂ
and find a time which has not been played before or find the time whichÂ
has not bee played for the longest time.

That calculation should be done in the DB if possible. Â Then you don’t
need SetRange to make your app efficient.Â

Again: your basic design, to the extent that I understand it, seems
quite bad. Â You’re reinventing the DB in the app.Â

If you disagree, please explain your data structure in more detail.Â

[…]

SetRange allows me to reduce the number of past games visible on eachÂ
pass of a dataset and then return it to the full set for the next futureÂ
game to be tested or the next team to be tested, or the next time to beÂ
tested. This way I can query the past games only once, and then changeÂ
the range of visible games on it many times while looking for optimalÂ
times to book each future game to be booked.
Â
I can’t find this ability in Rails.

Huh? Â A Rails “recordset” is simply an array. Â You can take a piece of
it just as you would from any other Ruby array. Â There’s no explicit
SetRange because you can just use array slicing.Â

Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

[markh]

Marnen Laibow-Koser wrote:

Huh? Â A Rails “recordset” is simply an array. Â You can take a piece of
it just as you would from any other Ruby array. Â There’s no explicit
SetRange because you can just use array slicing.Â

Thanks! I guess this is just what I am looking for. Setrange gives the
ability to slice the array based on the data contained within it rather
than just by the index number. I am hoping i can find a way to do this
in Rails. As I mentioned, I am new at Ruby on Rails but have made
several simple applications already. I am now ready to tackle a far more
complex app.

[markh]

And then in the controller:

@inactive = Item.inactive

This looks like it will still fire off a query which is what I want to
avoid.

The query would only fire (once) prior to rendering the view. For
example, if you set the variable in the index action of the controller
the query would only be run after calling the index action.

cars = Car.scoped
rich_ppls_cars = cars.order(‘cars.price DESC’).limit(10)

It appears that the call to cars fires the query but the call to
rich_ppls_cars only works on the resultant dataset and does not fire a
new query. Would that be correct? For example, I could then say
poor_ppls_cars = cars.order(‘cars.price ASC’).limit(10) and I would get
different data without a new query?

In Rails 3 the cars example above, you are creating a relation. The
query is fired when you call rich_people_cars.first or .all

Looking at some of the other posts here I agree there could be some
misunderstanding of what you’re trying to do.

If it’s 16 teams * 16 games that’s only 256 results. You can query all
the records and then logically view the remainder in instance
variables.

Also, if you’re looking at the bookings for holes then perhaps you set
the query to return records based on the date and venue. There are
only so many dates and venues available after all.

Interesting problem though and a good way to learn Rails.

[markh]

Bb Serviss wrote:

Many thanks to everyone who replied, this is a great forum. Delphi has
been kind to me with the (visual component library) client data set
which allows me to make a single query, then scope the remaining records
in any way I see fit and loop through them, breaking out of the loop
anytime I see fit. I can also use summary functions on the cds to return
sum and count, etc, all without any further queries. Anyway, enough of
Delphi, the requirement to use Windows servers has finally killed Delphi
for me.

The basic tasks I need to learn in Ruby are

1. create an array of hashes (records). I guess this is the result of a
   query. (cars = Car.all) but I also need to create them by hand
   sometimes.

2. Loop through the array, reading each record (I figure I can test each
   record rather than limit the records in the apparent dataset ala
   SetRange and perform some processing.

3. Breaking out of the loop anytime a condition has been met (e.g. the
   game has been booked).

I am sure there are plenty of material available to learn from. What I
really get from forums like this are hints as the the best way to go
about stuff. So far I am really enjoying RoR and have been playing with
it for over a year now and have made a couple of applications similar to
the Depot sample in the excellent book Agile Web Programming.

[markh]

It might help you a bunch to think of it as Ruby and not Rails. Most
of what your doing is writing Ruby and in many situations you will
need to know how. Look through the Array methods here and you should
get an idea of what can be done;

http://ruby-doc.org/core/classes/Array.html

Rails also injects some of it’s own methods into Array;

http://api.rubyonrails.org/
under: ActiveSupport::CoreExtensions::Array

You should find everything you need by doing some googling. Also I
would try to come at it from different angles to find the best
solution. Sometimes when you rewrite an app you try to copy it
verbatim when what is needed is rethinking of the problem.

[markh]

Mark H. wrote:

Bb Serviss wrote:
Â
Many thanks to everyone who replied, this is a great forum. Delphi hasÂ
been kind to me with the (visual component library) client data setÂ
which allows me to make a single query, then scope the remaining recordsÂ
in any way I see fit and loop through them, breaking out of the loopÂ
anytime I see fit.

Delphi has been unkind to you, then. Â That’s a bad way to interact with
a database. Â In general, it is much better practice to have the DB
narrow down the data as much as possible and loop through as few records
as possible on the app side.Â

I can also use summary functions on the cds to returnÂ
sum and count, etc, all without any further queries.

Your database can already do this. Â Please learn about SQL aggregate
functions, which are the correct way to achieve this. Â (Rails abstracts
these in the Calculations module.)

Anyway, enough ofÂ
Delphi, the requirement to use Windows servers has finally killed DelphiÂ
for me.

Nobody’s made an open-source Kylix clone?

Â
The basic tasks I need to learn in Ruby are
Â

1. create an array of hashes (records). I guess this is the result of aÂ
   query. (cars = Car.all) but I also need to create them by handÂ
   sometimes.
   Â
2. Loop through the array, reading each record (I figure I can test eachÂ
   record rather than limit the records in the apparent dataset alaÂ
   SetRange and perform some processing.
   Â
3. Breaking out of the loop anytime a condition has been met (e.g. theÂ
   game has been booked).
   Â

No. Â You’re going about this entirely the wrong way. Â You seem to be
trying to copy your Delphi application line by line. Â If that is even
possible, it will lead to unidiomatic Rails.Â

What I would recommend instead is this: learn the Rails framework’s
basics (from the Rails Guides or elsewhere), then reimplement the
functionality of the Delphi app in Rails, not its architecture.

Remember to do all development test-first (I recommend RSpec and
Cucumber). Â In fact, if you can write Cucumber stories for your Delphi
app, you’ll know when you’ve copied the functionality properly.Â

I am sure there are plenty of material available to learn from. What IÂ
really get from forums like this are hints as the the best way to goÂ
about stuff.

Hint: don’t try to write Delphi in Rails. Â If you really want to do
that, I’m sure you can find a Pascal Web framework…

 So far I am really enjoying RoR and have been playing withÂ
it for over a year now and have made a couple of applications similar toÂ
the Depot sample in the excellent book Agile Web Programming.

Then you should already have a basic idea of how a Rails app works, and
you should try to use that structure instead of what you’re used to from
Delphi.Â

Bonus tip: your lack of familiarity with the Array class suggests that
you need to get more familiar with basic Ruby, apart from Rails.Â

Good luck! Â Please don’t hesitate to ask if you have further questions.Â

Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

[markh]

Marnen Laibow-Koser wrote:

Mark H. wrote:

Bb Serviss wrote:
Â

Delphi has been unkind to you, then. Â That’s a bad way to interact with
a database. Â In general, it is much better practice to have the DB
narrow down the data as much as possible and loop through as few records
as possible on the app side.

Given that the returned records were relatively few (less than 200) and
that I needed to progressively narrow the view of those records down
then repeatedly restore the original view and go through the process
again with different teams and times, I had thought it was more
efficient to do this on the app side.
Â

Nobody’s made an open-source Kylix clone?

Even if they had it would not enjoy the same large user base and support
that RoR has.

No. Â You’re going about this entirely the wrong way. Â You seem to be
trying to copy your Delphi application line by line. Â If that is even
possible, it will lead to unidiomatic Rails.Â

That was my original intention but I will take your advice on this. It
was always apparent that there was going to be a great deal of
refactoring in any case.

What I would recommend instead is this: learn the Rails framework’s
basics (from the Rails Guides or elsewhere), then reimplement the
functionality of the Delphi app in Rails, not its architecture.

Bonus tip: your lack of familiarity with the Array class suggests that
you need to get more familiar with basic Ruby, apart from Rails.Â

That’s for sure and I have been doing a lot of reading of late.

Good luck! Â Please don’t hesitate to ask if you have further questions.Â

Thanks! I appreciate the feedback I get from this forum as a noob.

[markh]

Marnen Laibow-Koser wrote:

Hmm. You could do a line-by-line port, then refactor to a more Railsy
architecture – but it will be a herculean refactoring. Rewriting to
the new architecture from scratch will probably be easier.

I will do it from scratch. The algorithms for game draws and court
bookings are quite complex.

In the case of the game draw, there may be previous games played and the
draw is a redraw for part of the season due to the addition or
removal/transfer of teams. I need to ensure that the teams (up to 16
teams) have not played each other for the longest time. Its a matter of
testing each future week in the draw template to find the best week to
start.

In the case of court game time bookings, I need to ensure that neither
team has been booked at the time and court combo being tested for the
longest time. The teams will complain that another team has had x early
games while they have had x late games. Further, the system must support
clash teams where a team playing volleyball will not want to booked at
the same time as a team playing basketball if some players play in both
teams. Teams may also have times when they can’t turn up to play.

My customers are commercial social sports centers with potentially fluid
competitions as teams may come and go during a season or there may be
team regrades after several weeks if a team is either much better or
worse than the other teams in its league. As you can see the temptation
to port to what works already is very compelling but it seems the best
way forward is to refactor from scratch. Pity RoR wasn’t around in 2001
when I originally wrote it. The website is sportdata.com.au

[markh]

Mark H. wrote:

Marnen Laibow-Koser wrote:

Mark H. wrote:

Bb Serviss wrote:
Â

Delphi has been unkind to you, then. Â That’s a bad way to interact with
a database. Â In general, it is much better practice to have the DB
narrow down the data as much as possible and loop through as few records
as possible on the app side.

Given that the returned records were relatively few (less than 200) and
that I needed to progressively narrow the view of those records down
then repeatedly restore the original view and go through the process
again with different teams and times, I had thought it was more
efficient to do this on the app side.

Well, if you’re really retrieving a small recordset and doing lots of
different operations on it, then yes, it might be more efficient do to
this in the application. Maybe.

[…]

No. Â You’re going about this entirely the wrong way. Â You seem to be
trying to copy your Delphi application line by line. Â If that is even
possible, it will lead to unidiomatic Rails.Â

That was my original intention but I will take your advice on this. It
was always apparent that there was going to be a great deal of
refactoring in any case.

Hmm. You could do a line-by-line port, then refactor to a more Railsy
architecture – but it will be a herculean refactoring. Rewriting to
the new architecture from scratch will probably be easier.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

[markh]

Bb Serviss wrote:

I use Delphi (Lazarus) at work and I constantly miss ruby features

I still use Delphi 7 because later versions don’t like some of the com
stuff in the app exe. Its a nightmare just to refactor that. I have
numerous third party dependencies like patches to Internet Express and
some of these won’t work in later versions either due to unit location
changes and the like. Not only, but the Internet Express javascript has
i.e. extensions which won’t work in mozilla browsers. That said, I like
the Internet Express grid as it has multi record browsing, editing and
updates in a single trip to the web server. Probably not so much of a
problem anymore with faster internet but its a nice feature.

I can only echo Marnen’s remarks about Rails, Delphi does some weird
things and I’ve found that doing it the “Rails” way is usually simpler
and much more efficient.

Thanks, I have resolved to go that way.

As far as the bookings, you should do a bottom up review by starting
off with what would be the perfect view for you. Something by venue
that not only shows the two teams but their amount of late/early games which would aid in scheduling. Make something that makes sense to you.

That was the original plan long ago but with unknown numbers of courts
and game times, what is a late game? I found that its better to simply
distribute the courts and game times evenly, starting with the game time
and then the courts.

Clashes though - shouldn’t that be the player’s responsibility?

No, the sports centre allocates the game times and courts.

Also, don’t forget unit tests and a revision control system (like git)
as both of these will help you in many ways.

I have git organized, tests are high on my list. The stock test all bomb
because of the PK / FK relationships. I need to construct the fixtures
accordingly. Right now I am still sorting out the easy part which is the
CRUD on the organizations / sports / leagues / teams | games / players
and members, holidays, courts, etc.

Next I will attempt a game draw. Expect further questions

Thanks for the input.

Mark

[markh]

I use Delphi (Lazarus) at work and I constantly miss ruby features

I can only echo Marnen’s remarks about Rails, Delphi does some weird
things and I’ve found that doing it the “Rails” way is usually simpler
and much more efficient.

As far as the bookings, you should do a bottom up review by starting
off with what would be the perfect view for you. Something by venue
that not only shows the two teams but their win/loss percentage and
amount of late/early games which would aid in scheduling. Make
something that makes sense to you.

Clashes though - shouldn’t that be the player’s responsibility?

Also, don’t forget unit tests and a revision control system (like git)
as both of these will help you in many ways.