1

I'm not sure if I should ask this type of question in this site, but I already tried to ask in the Drupal forum here with no reply so far.

I'm trying to find out the best way to implement a solution in an existing Drupal 7 site that must meet the following requirements:

  1. a option to import addresses to the database from a csv/xls file, with:
    • fields: id, city, locality, street, street-number, postal code
    • around 100000 records/addresses
  2. a form that include a address section/field(s) with a search functionality to search the addresses
    • where the user would fill up some of the address fields (ex: city, street; or just street)
      • and it would show a list with the matched addresses
      • that would be sorted by relevance
        • for example: is more relevant if it's found in both "street" and "city" fields, than only in the "street" field (ideally it would be nice to replicate the google maps search)
  3. a option to export the submitted form values to a csv/xls (if this is too of topic, just ignore this step (3)) (The site is on a Drupal 7, with mysql 5.5, and php 5.5)

Previously the client had tried to implement the import requirement, but it was to slow (a few days just to load the csv file), using the feeds plugin and a taxonomy structure to save part of the address fields.

I'm relatively new to Drupal backend development, mostly frontend, theming (templates with html, js, css/sass, and just a bit of php) in Drupal and doing some simple forms in backend, but I have some MySQL and PHP background.

So I was thinking in building a module to:

  • create database tables of:
    • addresses (fields: id, city, locality, ...)
    • addresses_searchable, where it would have addresses strings fields, by converting the strings:
      • to lowercase
      • replacing non-latin text to US-ASCII (ex: "á", "à" to "a")
      • in order to help speeding up the search
        • when using "like" in Drupal database search api
    • form_submitions (fields: id (of the submitted form), name, ... (other form fields) , adress_id)
  • create a form . using ajax to load the search addresses list by pressing a button
  • set up a backend page (using permissions) for the import and export pages
    • import:
      • include a button to load addresses file (csv/xls), and other to run latest loaded file, and show a progress bar, and a log with possible errors
    • export:
      • using a button to download all form submissions up to now, or up to / after a selected date

From what I researched so far:

  • for reading files:
    • xls: could use the Drupal module phpexcel
    • csv: by using PHP native functions
  • to search the addresses:
    • by checking the Drupal api,
    • there is a Search API module that maybe would be the way to go
      • I haven't used it before, but maybe it would work if i create a entity for the address with the matching fields. Would be faster that a dedicated table?
      • in case it's better this way maybe, i could still use the address table as a temporary table, just to speed up the importing of the csv/xls addresses file

I'm not sure if this is the best approach to the problem, and what are the best practices to implement this in Drupal 7. Or even if it is something that should other solution/framework like for example:

  • use something like Laravel (v5.4) (which i also would have to learn), and load the forms in Drupal 7 through a iframe that would show the form from the other server and framework.

I've searched Google but I’m still a bit confused. Can someone point me in the right direction?

2

My first approach would be to build a basic importer using the BatchAPI and create a custom entity to hold the address data (NOT a content type), combined with Address Field for proper storage.

Then, I would use Search API and Search API Solr to index and store those entities, afterwhich you can build custom forms with Form API or use Views to expose and search the index - which will be much faster than Drupal db queries. Whenever possible, keep MySQL from doing work, as this is what will bog down your site.

This keeps things close to "Drupal native" while solving many of the performance issues you raised with Solr.

You can use Form API to build forms with #ajax callbacks to query and return data from Search API queries as well.

The import will never be lightning fast unless you're not using the Drupal API to store them as entities. When you don't store them as entities, you lose a lot of the flexibility of say, plugging into Views or Search API without a decent bit of work. You couldn't also add fields with field API, at that point it's just flat data, so say goodbye to easy addition of taxonomy fields or using Address Field.

With Solr constantly indexing new and updated entities, having the import be lightning fast isn't really a priority (IMO) as long as it is reasonable. If you have millions of addresses, Drupal will only be able to parse rows so fast, and a lot of this is due to the event system (save an entity, fields attach, hooks fire, alters fire, etc) on every call to entity_save. The price you pay in speed here is paid off later when you want to manipulate or integrate that data with the contrib space.

The gist I linked to was an experiment I was doing with the Data module and CSV files. This loads the entire CSV file and dumps its contents into MySQL using load infile. This will plant data into a database very quickly, but, as I said, you will have a lot more work trying to get Drupal to recognize, communicate with, and extend that data.

Another approach, totally non-Drupal, would be to import the data to a PostgreSQL (9.4+) database or a no-SQL equivalent, possibly even something like Firebase, and execute searches against that. Then again, this is almost the same as doing Search API + Search API Solr, except you'd have to build the wrappers to create and send queries to those servers. So, it is still valid to offload all of this work in a separate system just to crunch data. It really depends on how much programming you feel like doing.

Just to be clear, there are a handful of options here, it just depends on how much you can stomach if it doesn't use Drupal in some way. Personally, I would not want to give up the flexibility the Drupal API provides in terms of understanding your data model, and therefore, I would create a custom entity and start there. It will be slower, but in the end provide more points of integration for you in the module ecosystem.

  • Thank you for enlightening me on the possible paths. Not sure what can (if the client current server allows) and must be done yet with solr (I still need learn about it), were if not possible, maybe the "Search API Database Search" module would be an alternative, from the search api page (just not sure if is as good as solr for this project). Entities with the Address Field module + extra missing address fields to the same entity seems a good solution (just worried about the client address file import frequency in case it takes to long to import (were your script would be an alternative)). – Seeker Apr 17 '17 at 15:48
  • Search API DB the data still resides in your database, so you can still wind up with performance issues. – Kevin Apr 17 '17 at 15:49
0

A possible approach is to just import your data in a MySQL database (one or more tables, up to your ownn imagination). And then use the Forena module (disclosure: I'm a co-maintainer) to build whatever reports (and/or charts) related to those data.

To really make it fency, you could combine this also with DataTables, e.g. to implement it's typical search facilities that come with it.

On top of that, you can export data via Forena to any of the typical formats, such as CSV, XLS, etc.

PS: another variation is to not even import your data, and just have Forena access your existing data from within Drupal. All that's needed for this to work, is that your data base format is supported by Forena (such as PDO compliant, Oracle, MS SQL, SQLite, MySQL, etc).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.