MySQL: Dynamic ORDER BY clause
When developing some discography software for a website, I came across the need to sort a list of releases differently depending on their release date. I wanted to sort future releases in ascending order (oldest to newest) and sort past releases in descending order (newest to oldest). This isn't an easy task, but after many hours of trial and error, I finally figured it out with some MySQL trickery.
Given the following MySQL Database table named releases:
+-------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | title | varchar(256) | NO | | NULL | | | releasedate | date | NO | | NULL | | +-------------+------------------+------+-----+---------+----------------+
I accomplished the dynamic ORDER BY clause by using the following query:
SELECT id,
title,
releasedate,
UNIX_TIMESTAMP(releasedate) AS releasedate_unix,
CASE WHEN releasedate > NOW() THEN 0 ELSE 1 END AS futureorpast
FROM releases
ORDER BY futureorpast ASC,
CASE WHEN releasedate_unix > UNIX_TIMESTAMP(NOW()) THEN
releasedate_unix
ELSE
(releasedate_unix * -1)
END
Now for an explanation. First, we need to "seperate" the results into two sets: future releases and past releases. This is accomplished by calculating the futureorpast column upon which we can sort, so future releases are assigned a value of 0 while past releases are assigned a value of 1. These are then sorted with future releases coming first, because obviously 0 comes before 1.
Next comes the tricky part: we want to sort future releases by ASC but past release by DESC. This is unable to be accomplished using dynamic ASC/DESC in a CASE clause because you can only calculate values using CASE, not clauses. The trick then is to convert the date column into a unix timestamp so that we have an integer, and then take the inverse of that integer on the values we want to sort in reverse.
Using this technique I was able to sort a subset of results in one direction and another subset of the same results in a different direction. I'm not sure if this is the best way to accomplish this, so if anyone has other suggestions on how to accomplish this, I'd love to hear them!
MySQL: Global find & replace
Global find & replace is easy in MySQL:
UPDATE table_name SET column_name = replace(column_name, "searchString", "replaceString");
PostgreSQL: Determine if a column exists or not.
Here's a quick query you can run to determine whether or not a particular column in a table exists or not:
SELECT attname FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'YOURTABLENAME') AND attname = 'YOURCOLUMNNAME';
Of course, replace YOURTABLENAME and YOURCOLUMNNAME with the proper values. If a row is returned, a column with that name exists, otherwise it does not.
PostgreSQL: DB Error: constraint violation.
When inserting a row into a PostgreSQL database table, PHP's PEAR DB class was giving me the following error:
DB Error: constraint violation
After some research I found out that when we moved the tables to a new database server, the SEQUENCE values were not updated, causing this problem. Sequences are the equivalent of MySQL's auto-increment and are used to generate a unique identity value for newly inserted rows.
Because our table already had plenty of rows in it and the sequence value was not in sync, the insert would fail because the ID generated for it by the sequence already existed in the database. This is an easy fix, all you have to do is update the sequence value to be back in sync with your data. Simply run the following command in psql to bring everything back into sync:
select setval('your_table_id_seq', (SELECT max(id) FROM your_table) + 1);
Of course you will want to substitute your own sequence, table, and field values into this command. Once you've done this everything will be back in sync and you'll be able to insert new rows into your DB once again.