0

I'm working with a PM on a Drupal project, and we're having little success finding a developer to answer (what we believe to be) a simple question. I'm afraid I have no Drupal experience to speak of.

We've made well aware that Drupal itself doesn't use MySQL statements per se, but it is MySQL based, so - is it possible to write a SQL statement to find all users that have purchased a particular product/sku? The SQL is to be used in an external script, outside of the website itself.

The developer is vague, at best, so I can't be any more specific than the fact that it's Drupal 7 using a standard ecommerce package. I absolutely hate asking a broad question like this, but we're locked into this particular resource, and is insisting it will take 100+ hours to get this data to us, and I am unable to get to the DB to even try to construct the SQL.

Any advice is appreciated! (and if you want some contract work, I'd be MORE than happy to talk).

Clarification

Again, I know this is vague, and the SQL itself would be a bonus in the answer. The challenge is that the developer insists that Drupal doesn't use SQL statements, and therefore an external script isn't possible because it will take 100 hours to write a Drupal module to get that the result of the query. I'm imagining something like this to determine if/when a customer last ordered product #1234.:

SELECT MAX(o.orderdate) 
FROM users AS u
JOIN orders AS o on o.userid = u.userid
JOIN orderitems AS i on i.orderid = o.orderid
JOIN products AS p on o.productid = i.productid
WHERE u.email = 'somecustomer@example.com' AND o.productid=1234

With my limited Drupal experience, I know that this example isn't close to correct, but in principal, this is what we need to execute on a different server, with different software to simply get a date or a null. If she'd give us credentials to look at the database, I could probably figure it out, but her "position" in the matter is that it can only be done with this exhaustive module development effort that she seems to want to do.

  • You need to elaborate on: 'The SQL is to be used in an external script, outside of the website itself.' – J. Reynolds Sep 22 '15 at 17:01
  • Welcome to Drupal Answers :) If you're unable to get to the DB, what are you hoping for exactly? You need access to the DB to run a statement from anywhere (even from PHP on the server running Drupal). I can't work out if you're asking how to get access to the SQL server, or what query you need to run when you get there...would you mind confirming? – Clive Sep 22 '15 at 17:06
  • 1
    BTW the data you're ultimately looking for will need joins on: users, commerce_product, commerce_order and field_data_commerce_line_items I think (maybe more depending on what details about the product/user you need) – Clive Sep 22 '15 at 17:15
  • @Clive , the tables you mention...are they part of a standard Drupal installation, or what would we have to download to have them and try to emulate their installation? – GDP Sep 22 '15 at 18:16
  • 1
    There are so many other ways you could solve this though, even by just installing a couple of modules (Services + Commerce Services would give you a REST server to get data, for example). A custom module to expose the data in some format or other wouldn't take long either, but again you'd need to know the definite data structure up front. 100 hours is, I'm sorry to say, ludicrous (but i get the sense you know that...) – Clive Sep 22 '15 at 18:36
3

Drupal does use SQL statements to communicate with the database, however it does so through a built in abstraction layer. That said, you can write regular old SQL from any PHP script to query the information you need, as long as you have the database connection info. Personally I would build whatever functionality you're after into a custom Drupal module, and then use that to interface with whichever external service you're working with. We would need a bit more information on exactly what you're trying to achieve to provide more specific feedback however.

  • The perfect answer..."yes, it's possible". Realizing fully that there is much more needed for a more robust answer, this is all I need to know to "get on with it", lol. – GDP Sep 22 '15 at 17:45
1

Yes it's absolutely possible, but almost certainly not the best way to do things.

Drupal uses MySQL (or equivalent) to store data, so it has to use SQL statements to store data. This is done at the core level (meaning the vast majority of developers will never need to write SQL statements for their Drupal development). Instead, developers call various Drupal functions that will run the appropriate SQL statements. Basically you can think of it like this: as a developer, you tell Drupal you want to save a certain object type and it will translate your object into SQL statements that save it in the database the best way it knows how.

Drupal saves data in dozens of tables. A user might have their information saved in half a dozen tables, and your products could be saved in many more. To find the history purchases of users via SQL statements is probably more difficult than hiring a developer to write a custom module that would be able to calculate that for you and potentially even track and display purchase trends going forward in a much cleaner way. As far as the 100 hour estimate, I'm not sure.. I'm betting that some e-commerce modules already store this historical purchase information and they could probably be setup in an hour or so. Others might not have that functionality and will need some custom code that could take 100 hours, sure.

Instead of insisting on running SQL statements to run your reports, find out which modules your e-commerce store is using (perhaps ubercart?). Then do some research to find out of that module already offers the functionality you're looking for (or ask your developer to do that research). If not, a custom module might be required, otherwise other modules (like the rules module) could be sufficient.

I highly recommend you do not run external SQL scripts. If you do, I highly recommend you do not hire a Drupal developer to do it and instead hire a Database Administrator (which is probably much more expensive than a Drupal developer). Instead, you should hire a Drupal developer with the following experience:

  1. Has developed a Drupal module (more than just hello world)
  2. Has experience with the e-commerce module your Drupal site uses
  3. Understands what MVC is and how Drupal implements MVC
  4. Has at least 3 years of PHP experience

That developer will be able to tell you the best way forward for your case.

  • Thank you...the DBA would be an overkill for a single SQL statement, but everything you're saying is correct. What we need is a Drupal programmer who isn't so paranoid that we could look at the db itself. – GDP Sep 22 '15 at 17:34
  • I don't think you'll want a Drupal programmer to look at the db. I mean, it might help them at some point to take a look, but they can decide that on their own and you will probably have better luck finding someone from a different angle: you want a Drupal developer that is experienced with Drupal development (programming in php). They way you are searching for someone who isn't paranoid of database assumes that looking at the database is the correct way to go about the solution, which I'm confident is not the case. The person you are looking for will very likely never look at the database. – Bryden Sep 22 '15 at 18:42
  • i think you miss my need...it's only the DB we're interested in. To write a single SQL statement to be used elsewhere. My knowledge of Drupal tables is my downfall, thus the vague question. – GDP Sep 22 '15 at 20:31
  • All I'm trying to say is that you are breaking a lot of best practices by trying to frame your solution as "a single SQL statement to be used elsewhere". You can do it, as I've said and others have also said, but it's a bad idea. There's a better solution to get you the same results (a list of users who have purchased a certain product). Open up that information via API and do it properly, otherwise compromise the security of your database, your website and your server. Again, you can do it, but I think you'll want a Network Administrator/DBA's advice to help you out, for security reasons. – Bryden Sep 22 '15 at 21:00

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.