Come together with the global Drupal community in Rotterdam, 28 Sept – 1 Oct 2026. Sessions, contribution, connection, and Early Bird savings until 8 June.
By JJacobsson on
I have a rater complex ORDER BY scenario that I don't know how to solve...
Table has 3 columns, `a`,`b`,`c`. I want all rows where `b` is X and Y ordered by (`c` where `b` is Z) while maintaining all `a` values that are the same next to each other. See, `a` and `b` are keys to values in `c`. I want all `a` that have certain values for `b` where one specific value of `b` indicates a "weight" property (stored in `c`) of the object with key `a`.
Hope any of that makes sense to someone, I cant seem to get it to work. Of course, I would like it in one query :) I can split it up, but that's cheating.
And even then I run into trouble if I want to order by more than the "weight"...
Comments
Refactor, please.
Seriously, redesign your table. The meaning of 'c' shouldn't change because of the value of 'b'. Break it out into separate tables with 'a' being the key for each table (if I understand what you're trying to do). Once you do that, you can do things with JOINs and then ORDER BY in more effective ways. Maybe you just need more columns. I can't tell with just names like 'a', 'b', and 'c'.
-----
Übercart -- One cart to rule them all.
Yeah I'm leaning in the
Yeah I'm leaning in the direction of "do-over"... Some things where soooo easy to do with this setup, but others (like simple SELECT with ORDER BY in any meaningful way) became impossible.
`a` is "nid", `b` is "data type" if you get my meaning and `c` is "value"... I wanted to get all values for all nid's ordered by the weight "data type" associated with each nid.
Poorly thought out.. :/ back to the drawing board.
-----
--"I have no responsibility what so ever."
That's an odd query
That's an odd query, if I may say so, but it's doable with one table. Just have columns a, b1, b2, b3,... and fill in the rows with the appropriate c values. Then you can do
The tricky part would be figuring out which order you want the columns in, but I guess you have a way to do that.
-----
Übercart -- One cart to rule them all.
Breaking normalization
Breaking normalization sounds a bit bad... :/
Still, I suppose I'm not understanding your use case right, because I do not understand how the obviously too simple "SELECT nid, type, value FROM {table} ORDER BY nid, type" falls short of the requirement.
Specifically, I think the following sentence confuses me:
Where does this "weight" come from? Is it functionally dependent on data type, nid, or both?
Yeah, you're right. I think
Yeah, you're right. I think the description of the query got me all confused.
-----
Übercart -- One cart to rule them all.
You lost me at "ordered by (where)"
Seriously, if your query takes more than three lines to describe in English, you need to rethink your schema because it will never work properly in SQL. Besides, I can tell you right from the start that you can't order by the result of another where query. You'll end up needing at least one temp table, possibly more.
Also, if you want any useful help here, you need to put names and purpose to these columns and this query. What kind of data are you trying to aggregate, and what do you want out of it? Without understanding that, it's impossible to say how to structure your query, because the way it's now it can't work.
normalize your table
the 3 lines in English rule is not a good rule for SQL. There are times when it is necessary to to have fairly complex queries because of various reasons, I've had to write SELECT statements that are dozens of lines long, much longer with formatting to make it readable, because of the amount of relationships, use cases, in-line if-else's etc. necessary to accomplish what is necessary.
But back to table design, the most common beginner DB design is trying to stuff too much into a single table resulting in a very wide table, when the data should be normalized, or broken up into smaller tables with relationships created between the tables. I'm not saying that everything needs to go to 3rd normal form, but some normalization does help.
Or as Island Usurper said, refactor, please.
Well, what I was getting at
Well, what I was getting at was that the purpose of the query (what information you want, what kind of report you wish to present, etc.) should fit into a briefer description.
An attempt at "the purpose" ;)
[purpose]
The purpose was to have a dynamic "properties" system for a certain type of node. I.e. the node type is "card", and some properties it might have are "color", "race", "type", "health" etc...
But not all properties are appropriate for all "card" nodes. If you are familiar with collectible card games like magic (not just magic) and such you get the idea, there are different types of cards that have different properties.
[/purpose] (4 sentences. I fail)
My first implementation was straight forward, just one table with one column for each possible property. This led to a lot of null values. So I started looking at maybe breaking it up into several tables, one for common properties and one for each type of card.
But I discarded that idea because I diden't want to go back and add new tables and hack all my queries again to deal with those new tables whenever the developers of the card game introduced a new card type or valid value for a property.
So I thought, why not have one table with information about what types of properties exist, one table with all the different possible values for those types (the ones that need to represented with strings anyway) and one table that maps together nid's with data types with values. Tada! Suddenly it was cake to make a card with whatever properties. Very "future proof" and Bob was my uncle.
Producing a listing of cards, ordered by the value of one of those properties, became death. Well actually, ordered by one property was doable, but it diden't scale at all. And paging became a nightmare.
Hence my thread. I'd say I'm a fairly experienced programmer (c++). But SQL and good database practice is an area where, I'll be the first to admit that, I'm a heavy practitioner of "voodoo programming".
--"I have no responsibility what so ever."
It would be helpful if you
It would be helpful if you would just post your table design and your select statement(s). The way I would model your above statement is probably different than the way you have it and someone else would probably do it yet another way.
It sounds like one of those things...
That are wonderful to implement in Object Oriented Programming, but extremely hard to implement in normalized database design. I've heard that the two clash regularly.
Does this occur frequently? It sounds like a good state of normalization that does not depend on using overly many joins (which do not scale well at all).
Depending on how much customization you need (and how many different card types there are), CCK might be able to help you. It is its own relational abstraction layer, allowing you to treat "fields" as atomic values and "nodes" as relations on fields, while juggling around your content type tables in the background. Essentially, it implements the idea I quoted, but without requiring you to manually change the schema or hack your queries.
If you have only 5-6 different card types, you could make each one its own node type with CCK, sharing the common fields between them and adding the extra fields to each of them separately. Of course if you have much more than 10 different types, this solution will produce a lot of clutter. And if you have a lot of custom code, you will still end up having to mess with your own queries whenever you change the schema with CCK.