Need guidance for a simple search Rails application

Hi,

I am learning Ruby. In the meantime, I have to make a simple app for a small company that I know how to make in PHP and MySQL.

I have not started learning Rails yet (first I want to finish the Ruby book I have started). But I was thinking if with a little guidance may be I can make this app in Rails. If I can do it, that will be great.

The requirement is like this:

  • I am given an Excel file that has 11 columns. Each row has information about one item. Each row is unique. Right now the company uses this excel file to search for required information, and they want me to make a web app for this.

  • This Excel data and future data will be inserted into the database in bulk, using CSV imports. I think there is no need to break the table, and our app can just have a single table to search from.

  • The search will be done using 2 columns from the table.

  • One person will have the privilege to edit searched records.

  • Only 4 people will be using the app for now. There will be no option to sign-up for a new account.

  • The home page will have the search form (two fields, user can fill both or one).

I will appreciate (a lot) any pointers, guidance and help with this.

I can make this in PHP, but I’d love to make it in Rails.

– arslan

I didn’t exactly get your question. Are you looking for a way to search for the records ? If the number of rows is huge and you need full text search, you may need to look at Solr or Sphinx or AWS CloudSearch for fast queries. If time is not a big concern, you may even be able to do away with simple ActiveRecord queries (do look at squeel gem too : https://github.com/ernie/squeel ).

Assuming you have little Ruby experience and no Rails experience I think your best plan would be to familiarize yourself with RubyOnRails tutorials at: http://guides.rubyonrails.org/getting_started.html (a full tree provided by the RoR development group covering major Rails components), and: http://ruby.railstutorial.org/ruby-on-rails-tutorial-book (a very good book by Michael Hartl with the apt sub-title “Learn Web Development with Rails”). Both are free online.

Your application looks like a good one for a first effort with two models: User and Item. Members of the User class can have roles associated with them, i.e.: Manager, Employee, and Administrator. The Item class will be a direct map of the spreadsheet, members will have fields that mirror the spreadsheet columns. The Item search can be nicely built into the item controller’s index method and the item edit restriction can be enforced by keying off the user’s role in the controller methods and views.

I would suggest that, at least initially, you use a snapshot of the company’s spreadsheet to populate a database that you will use for all development, test, and functional acceptance. You can then segregate out a separate task to either provide functions to import and export the excel based data. I would suggest that the final design might want to have the data held in a database with a publish capability with an excel format (and/or pdf, and/or …) that has the published date/time as part of the page header.

Be sure to learn how to test your app, validate your data, and have fun.

+1 to those suggestions. To start developing an app before working
right through a good rails tutorial will just result in wasting a lot
of time. The railstutorial.org example will give you some useful
stuff such as user authentication that you can use in your app
directly.

Colin

Hi guys,

Thank you Emil, Rick and Colin.

Emil:

I didn’t exactly get your question. Are you looking for a way to search for the records ? If the number of rows is huge and you need full text search, you may need to look at Solr or Sphinx or AWS CloudSearch for fast queries. If time is not a big concern, you may even be able to do away with simple ActiveRecord queries (do look at squeel gem too : https://github.com/ernie/squeel ).

Yes, part of it is looking for a way to search for the records. And yes, number of rows is huge. There are around 80,000 rows split in multiple Excel sheets.

I am thinking I may have to split this one database table into more tables that can help with the search.

Rick:

Assuming you have little Ruby experience and no Rails experience I think your best plan would be to familiarize yourself with RubyOnRails tutorials at: http://guides.rubyonrails.org/getting_started.html (a full tree provided by the RoR development group covering major Rails components), and: http://ruby.railstutorial.org/ruby-on-rails-tutorial-book (a very good book by Michael Hartl with the apt sub-title “Learn Web Development with Rails”). Both are free online.

I finished the first chapter of Rails Tutorial, and I have this https://github.com/arslanfarooq/first_app and http://fast-beyond-5656.herokuapp.com/

This was really fun and I believe when I finish this whole tutorial I’ll learn a lot.

But I have a 2 week deadline (actually I set the duration myself when they asked me how long will it take). So today I decided to first make this in PHP. Once this app is finished and tested in PHP, then I’ll come back to making it in Rails.

Your application looks like a good one for a first effort with two models: User and Item. Members of the User class can have roles associated with them, i.e.: Manager, Employee, and Administrator. The Item class will be a direct map of the spreadsheet, members will have fields that mirror the spreadsheet columns. The Item search can be nicely built into the item controller’s index method and the item edit restriction can be enforced by keying off the user’s role in the controller methods and views.

What you are saying is kind of making sense, and I feel this may serve like a rough plan for what I want to achieve. Thank you!

I would suggest that, at least initially, you use a snapshot of the company’s spreadsheet to populate a database that you will use for all development, test, and functional acceptance.

I actually did this today in PHP, I imported the Excel data in the database. And the search form is working… kind of. It is pulling the results, but I need to grab a relational database book and look for ways to optimize my search query.

I have decided to make two search forms. One search form to search using details of a product, and another form to search using details of a person.

My SQL knowledge is very basic, and to give a clear idea of my skill level, I am a WordPress developer. So when I said I know how to make this in PHP and MySQL; I should have said I have an idea how to make this in PHP and MySQL.

Right now I need to formulate a smart query when search form is submitted. My current query is too simple and does not give the desired results in many cases. Gives desired results in one particular way :slight_smile: Which sucks.

You can then segregate out a separate task to either provide functions to import and export the excel based data. I would suggest that the final design might want to have the data held in a database with a publish capability with an excel format (and/or pdf, and/or …) that has the published date/time as part of the page header.

Client did say to implement the print search results feature. So I’ll use the pdf option.

Be sure to learn how to test your app, validate your data, and have fun.

Thank you Rick!

Colin:

+1 to those suggestions. To start developing an app before working
right through a good rails tutorial will just result in wasting a lot
of time.

Yes I completely agree.

The railstutorial.org example will give you some useful
stuff such as user authentication that you can use in your app

I will definitely finish that tutorial once this app is done in PHP.

And for that, I just need to get the search to work nicely. I already implemented Twitter Bootstrap for PHP version so interface is already good and responsive.

Thank you guys for your help and input. God bless you all.

I don’t want to ask for the SQL here. I’ll use appropriate forums etc.

– arslan

No, 80,000 rows is a *tiny* database. You should absolutely not need to split the table--stick with a clean design that mirrors a sensible understanding of the data, don't mess it up by trying to accommodate imaginary performance concerns.

No, 80,000 rows is a tiny database. You should absolutely not need to split the table–stick with a clean design that mirrors a sensible understanding of the data, don’t mess it up by trying to accommodate imaginary performance concerns.

Ah. Got it.

I’ll leave the table alone then.

Thank you Scott!

– arslan