I'm using Views 2 and want to know how to construct a query which, as far as I can tell, requires a subquery.
Given these tables;
Table foo:
+----+-------+
| id | title |
+----+-------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+-------+
Table bar:
+----+-----+-----+
| id | foo | baz |
+----+-----+-----+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 3 |
| 5 | 2 | 2 |
| 6 | 2 | 1 |
+----+-----+-----+I want to find the bar.baz value of the row in bar which has the highest bar.id for each foo.id. In good ol' SQL, the query would be:
SELECT foo.id AS foo_id, bar.id AS bar_id, bar.baz AS bar_baz FROM foo LEFT JOIN bar ON bar.id = (SELECT MAX(id) FROM bar WHERE foo = foo.id);
+--------+--------+---------+
| foo_id | bar_id | bar_baz |
+--------+--------+---------+
| 1 | 3 | 3 |
| 2 | 6 | 1 |
| 3 | NULL | NULL |
+--------+--------+---------+Yeah, a big fat ugly subquery, but if there's another way to do this, it's got me and others stumped.
In real world terms, in the Ubercart Auction module, I'm trying to find the value of the highest bid and the user that placed that bid. Bids are stored in the uc_auction_bids table which currently looks something like
+-----+-----+-----+------------+---------+
| bid | nid | uid | time | amount |
+-----+-----+-----+------------+---------+
| 2 | 3 | 1 | 1231443500 | 70.000 |
| 3 | 3 | 4 | 1231451178 | 75.000 |
| 10 | 1 | 4 | 1231973929 | 22.000 |
| 11 | 1 | 1 | 1231973973 | 52.250 |
| 12 | 2 | 4 | 1231976911 | 28.000 |
| 13 | 2 | 1 | 1231976959 | 60.125 |
| 14 | 7 | 4 | 1231979198 | 130.000 |
| 15 | 7 | 6 | 1231979767 | 200.000 |
+-----+-----+-----+------------+---------+…which is hopefully self-explanatory. So I want to find the row with the highest bid ID (or bid… a recursive acronym of sorts) for each nid.
Of course, hopefully I'm wrong and there's an easy way to implement this query - both in SQL and in Views - which doesn't require a subquery; please prove me wrong if you can. Otherwise, any ideas on what the best way to approach this with Views would be?
Comments
That depends. Do you have
That depends. Do you have the ability to change the schema slightly?
Because it's a lot more efficient to record high bid on write than it is to discover it on read. If so, I'd just add a high bid table that relates a nid to a bid. Then suddenly your querying gets really easy.
I have the ability to change
I have the ability to change the schema all over the place, and older versions of it did in fact do what you said -- a column on the table which stores one-to-one information on auctioned nodes, such as its starting price and expiration time, also stored which bid ID was the most recent. I actually removed it, though, because it was essentially storing the same info twice - and having to update it when new bids are placed, bids are deleted, etc seemed unnecessary.
Views support is pretty important to this project, though, so if you think that'll be the best way to get the job done, I'll do it.
The Boise Drupal Guy!
Given the nature of SQL I
Given the nature of SQL I think you are best off putting it back.
would you use a MySQL View object for this?
Hi. I have been trying to solve a very similiar problem.
Could you -
Create a database-view by running something like
CREATE VIEW max_id_view
AS
SELECT MAX(id), foo FROM bar GROUP BY foo
then treat this as a normal sql table from within the Drupal Views2 API and create a relationship between max_id_view and bar.
Would this work? Would it be inefficient? I was hoping I could get a views_query object object to create the query directly.
I can see that the views_query object supports a $groupby member but I'm not sure how I would get the aggregate function into the views_query object. Can I just set the field as 'max(id)', include a $groupby value and expect it to work?
Hi! Another one with the same
Hi! Another one with the same problem, and yes, I have been considering the sql-view option too. Perhaps it is a reasonable approach, but there are times when you really want to use subqueries and I hope that the views query-abstraction could be improved too support this as well. But i just started developing with views so perhaps there exists techniques am not yet aware of.