custom made order by mysql

Question

I have a row of entities stored in a mysql table;

entity table

order id name type (refers to Type(id))

and a row of types stored in another table

Type table

 id name order

the ORDER column in Type table specifies by what order the entities should be sorted - some should be ordered by name and some by id.

How do I create a mysql query that gets the ORDER BY clause from the type table and sorts the entities in the entity table by the ORDER stored for that entity type in the Type table

for example, let us say I have the following rows:

Entity table:

row 1:

id = 1 name = Virginia type = 1

row 2:

id = 2 name = Virginia type = 1

row 3:

id = 3 name = Canada type = 2

types (rows in Type table)

row 1

id = 1 name = states order = "name"

row 2:

id = 2 name = countries order = id

I want to do the following query

SELECT entities.id, entities.name FROM entities INNER JOIN type ON entities.type = type.id ORDER BY ....

in the ORDER BY I want to order the entities based on what is stored in the ORDER row in the type table. So countries should be sorted by Entity(ID) and states should be sorted by Entity(name). How can I do that?

Travesty3 · Accepted Answer · 2012-01-31 21:08:42Z

This doesn't seem like a very good design for a database. For your example, I would suggest something more similar to this:

CREATE TABLE countries ( countryID INT NOT NULL, countryName VARCHAR(30) ); CREATE TABLE states ( stateID INT NOT NULL, countryID INT, stateName VARCHAR(30) );

Then you can perform queries like:

SELECT c.countryName, s.stateName FROM countries c LEFT JOIN states s ON c.countryID = s.countryID ORDER BY countryName, stateName;

At the very least, I would suggest using more obvious names for your columns, like in your entity table, you have a column named 'type' which refers to the 'id' field in the type table. Perhaps name them both typeID or something more obvious.

I also think it's a bad idea to create a column that stores information about which column to order by. Not only does that mean that you'll have to execute two queries every time (one to fetch the order by, and one to fetch the actual data), but you will also be storing a lot of extra data unnecessarily.

Collectives™ on Stack Overflow

custom made order by mysql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related