By NewSites on

I have a rather complicated query running on an ASP site that I need to port to Drupal. It uses several calls to First(), an Access-specific function similar to TOP and LIMIT in SQL Server and MySQL. However, my query calls First() on eight columns, as well as Max() on another. In MySQL (which I'm using), LIMIT seems to only apply to the entire result set, not to individual columns (MySQL documentation).

I'm not sure if Drupal's db_query_range() is intended for this sort of purpose, but if it is, I don't see a way to apply the range selection to specific columns.

Here is the SQL as it currently runs in ASP. Is there any way to do this in Drupal?

SELECT First(Table1.Name), First(Data1A), First(Data1B), First(Data1C),  First(Data1D), First(Data1E), First(Data1F),  First(Table2.ID1), Max(Table2.Data2A), Count(*), Count(Data2B), Count(Data2C), Count(Data2D)
FROM (Table1 RIGHT JOIN Table2 ON Table1.ID1 = Table2.ID1)  LEFT JOIN Table3 ON Table2.ID2 = Table3.ID2
WHERE (Data1E IS NULL AND (Data1D IS NULL OR Data1D <> 0))  OR Data1E = "+" OR Data1D = -1  
GROUP BY Table1.ID1
ORDER BY First(Table1.Name)

Field names without a table prefix are unique to one table.

I know it would be good to say what the query is doing. Unfortunately, I wrote it several years ago and didn't document it well. I can tell you that the results of this query are used to control what data are used to generate a set of tables on the website. I need to get a clearer picture of that, but in the meantime, I need to know if I can port that code to a Drupal db function or if I'm going to need to figure out a different way of doing it.

Thanks for your help.

Comments

NewSites’s picture

NewSites commented

I figured this out, so I'll share it here in case it's helpful to someone else doing a conversion from ASP. The answer is trivially simple.

What I found is not a general answer on the translation of First(), but is the answer applicable to this particular situation.

Notice three things. First, all the fields in the select statement are inside so-called "aggregate" functions, in this case either First(), Max(), or Count(). Second, the record set is a join of three tables. And third, everything is grouped on the ID (primary key) of one of the tables.

There's one other thing you need to know in order to understand what's going on. That is that I developed the SQL code with the query design system in Access, which allows queries to be built easily in a graphical environment and automatically generates their SQL. The resulting SQL needs some clean-up, such as removing excessive brackets, but is always functionally correct.

However, one peculiarity of the design system is that when records are grouped, every field must appear only through an aggregate function. I've always wondered if this is a requirement of SQL or just of the Access design system. Now I have the answer to that.

This situation explains why First() is called so many times. When I built this query, the joins created many records that were duplicates on all the important fields. All I wanted was total counts of the data (and one maximum value) in the joined tables. So I put the fields from the main table in First() because that gave me data from one record of each set of duplicates (the grouping). (This explanation might only makes sense to people who have experienced this peculiar behavior of the Access query design system.)

In MySQL, I first tried using LIMIT, but that reduced the result set to one record, which was definitely not what I need. I need one record for each value of "Table1.ID1".

So the next thing I tired was just to remove the First() from all those fields. And that did it! The results are identical to what I got with those First()s included in Access. Which answered my previous question about whether those First()s are needed by SQL. They aren't. They are a figment of Access's design system.

I ran that first as a test in phpMyAdmin, then I implemented it in db_query() with three placeholders to 0, "+", and -1. And as I said, the results are identical to what is generated on the current site running in ASP with eight calls to First().

So there you have it. In this particular, limited situation, the translation of the Access First() function for use in Drupal is simply to remove it!