Ugly Queries

A thread for ugly queries. Not problem non-working queries mind you - just ugly or overly complicated queries :slight_smile:

Just finished writing this little monster


SELECT fac.old AS o, md.new AS n
FROM import AS fac
	JOIN facilities ON facilities.recordid = fac.new AND fac.tble = 'facilities'
	JOIN providers ON providers.facilityid = facilities.recordid
	JOIN import AS md ON md.new = providers.recordid AND md.tble = 'providers'

Ok, hereā€™s whatā€™s going on - The ā€˜importā€™ table is a temporary index of keys from a new database against the old keys from the database Iā€™m importing from. In order to preserve relationships I have to translate the keys at several points of the importer script.

(Now yes, I could put an old id field on each of the import tables, but since that data isnā€™t going to be used post import Iā€™d rather keep it on an import table that I can throw away later rather than remembering to drop the old id field off some 20 tables).

In this particular query I have a situation where Iā€™m preparing to map medical procedure codes to the doctors that use them. Unfortunately the old system maps the codes to the facility the doctor works for. This was an error on the prior programmerā€™s part - each doctor needs their own procedure list (or more accurately, procedure fee list). Fortunately the old system has a 1:1 relationship of doctors to facilities, but we wonā€™t have this moving forward.

So my import table needs to be joined to ITSELF through the provider and facility tables in order to call back an associated array of old facility id to new provider id that Iā€™ll be inserting during import.

It works, but itā€™s ugly.

Joining tables to itself is not that entirely off the wall and is done more than you think.

If only you knew what truly ugly wasā€¦Iā€™d show you, but Iā€™m just now getting over the nightmares :shifty:

This is a single SELECT query in one of my websites:

$sql = "
SELECT SQL_CALC_FOUND_ROWS 
  landing_page.id AS `landing_page_id`,
  landing_page.name AS `landing_page_name`,
  landing_page.url,
  landing_page.created_at,
  landing_page.updated_at,
  landing_page.deleted,
  ad.id AS `ad_id`,
  ad.name AS `ad_name`,	
  ad_group.id AS `ad_group_id`,
  ad_group.name AS `ad_group_name`,
  campaign.id AS `campaign_id`,
  campaign.name AS `campaign_name`,	          
  COUNT(click.id) AS `clicks`,
  (SELECT
		COUNT(*) AS `clicks`
	FROM
		click
	INNER JOIN
		landing_page
			ON click.landing_page_id = landing_page.id
	WHERE
		landing_page.ad_id = MAX(ad.id) 
		AND click.created_at >= '" . $args['ds'] . "' AND click.created_at <= '" . $args['de'] . "' 
		AND click.offer_click = 1
  ) AS `offer_clicks`,
  COALESCE(SUM(click.cost),0) AS `cost`,
  COALESCE(cost_table.cost,0) AS `flat`,
  COALESCE(action_table.`count`,0) AS `actions`,
  COALESCE(action_table.value, 0) AS `revenue`,
  COALESCE(action_table.value, 0) - COALESCE(SUM(click.cost),0) AS `profit`,
  COALESCE(COALESCE(action_table.`count`,0) / COUNT(click.id), 0) * 100 AS `convrate`,
  COALESCE(COALESCE(SUM(click.cost),0) / COALESCE(action_table.`count`,0), 0) AS `costconv`,
  COALESCE(
  	(COALESCE(action_table.value, 0) - COALESCE(SUM(click.cost),0))
  	/
  	COALESCE(SUM(click.cost),0)
  ,0) * 100 AS `roi`";
  
if (isset($args['ds']) && isset($args['de'])) {
    $sql .= ", DATE(CONVERT_TZ(click.created_at,'GMT','$timezone')) AS `date`";
}  
  
$sql .= "		  
FROM
  landing_page
INNER JOIN
  ad
    ON landing_page.ad_id = ad.id ";
 
if (isset($args['ad_id']))
    $sql .= " AND ad.id = " . $args['ad_id'];

$sql .= "		    
INNER JOIN
  ad_group
    ON ad.ad_group_id = ad_group.id ";
 
if (isset($args['ad_group_id'])) 
    $sql .= " AND ad_group.id = " . $args['ad_group_id'];

$sql .= "		    
INNER JOIN
  campaign
    ON ad_group.campaign_id = campaign.id ";
 
if (isset($args['campaign_id'])) 
    $sql .= " AND ad_group.campaign_id = " . $args['campaign_id'];

if (!empty($args['restrict_to']))
    $sql .= " AND campaign.id = " . $args['restrict_to'] . " ";

$sql .= "
LEFT OUTER JOIN
  click
    ON click.landing_page_id = landing_page.id 
    	AND click.created_at >= '" . $args['ds'] . "' AND click.created_at <= '" . $args['de'] . "' 
    	AND click.offer_click = 0
    	AND click.deleted = 0
LEFT OUTER JOIN
	(
		SELECT 
			landing_page.id AS `landing_page_id`,
	  		COUNT(action.id) AS `count`,
	  		COALESCE(SUM(action.custom_amount),0) AS `value`,
	  		DATE(CONVERT_TZ(action.created_at,'GMT','$timezone')) AS `date` 
	  	FROM
	  		action
	  	INNER JOIN
	  		click
	  			ON click.id = action.click_id
	  	INNER JOIN
	  		landing_page
	  			ON landing_page.id = click.landing_page_id
	  	INNER JOIN
	  		ad
	  			ON ad.id = landing_page.ad_id
	  	INNER JOIN
	  		ad_group
	  			ON ad_group.id = ad.ad_group_id
	  	INNER JOIN
	  		campaign
	  			ON campaign.id = ad_group.campaign_id
	  	";
	  	if (isset($args['ad_id'])) $sql .= " AND ad.id = " . $args['ad_id'];
	  	if (isset($args['ad_group_id'])) $sql .= " AND ad_group.id = " . $args['ad_group_id'];
	  	if (isset($args['campaign_id'])) $sql .= " AND campaign.id = " . $args['campaign_id'];
		if (!empty($args['restrict_to'])) $sql .= " AND campaign.id = " . $args['restrict_to'];
	  	
	  	$sql .= "
	  	WHERE
	  		action.deleted = 0
    			AND action.created_at >= '" . $args['ds'] . "' AND action.created_at <= '" . $args['de'] . "' 
    	";
		if (count($args['selected']) > 0) {
			$sql .= "AND landing_page.id IN (" . implode(', ', $args['selected']) . ") ";
		}				 
    	
		$sql .= " GROUP BY ";
				
		if (isset($args['graph'])) {
			$sql .= "DATE(CONVERT_TZ(action.created_at,'GMT','$timezone')) ";
		} else {
			$sql .= "landing_page.id";
		}
    	
    	$sql .= "
  	) AS `action_table` 
  	";
  	
  	if (isset($args['graph'])) {
  		$sql .= "ON action_table.`date` = DATE(CONVERT_TZ(click.created_at,'GMT','$timezone'))";
  	} else {
  		$sql .= "ON action_table.landing_page_id = landing_page.id";
  	}

$sql .= "
LEFT OUTER JOIN
	(
		SELECT
			ad_group.id AS `ad_group_id`,
			COALESCE(
					CASE 
						WHEN ad_group.cost_type = 'monthly' 
						THEN ad_group.cost * $num_months 
						ELSE 
							CASE WHEN
								ad_group.cost_type = 'flat' 
								AND ad_group.created_at >= '{$args['ds']}' 
								AND ad_group.created_at <= '{$args['de']}' 
							THEN
								ad_group.cost
							ELSE
								0
							END
					END
			,0) AS `cost`
		FROM
			ad_group
		WHERE
			ad_group.deleted = 0
				AND (ad_group.cost_type = 'monthly' OR ad_group.cost_type = 'flat')
	) AS `cost_table`
	ON cost_table.ad_group_id = ad_group.id
WHERE
  landing_page.deleted = 0 ";

I wish the ones i have to work with look like thatā€¦ currently looking at one that fills in 3 screens and goes over 3 databases (yes, databasesā€¦).

Is there any software/editor that people find helpful when working with huge sql queries? For example, I was thinking the ability to visually collapse/expand a subquery, and maybe annotate it. Kinda like how a code editor lets you collapse a nested block of code.

i use UltraEdit, which is a ā€œgeneral purposeā€ code editor

it supports various languages like hmtl, css, sql, and of course all the various programming languages like c, vb, java, perlā€¦

all the sql iā€™ve ever written over the past nine years or so has been written in ultraedit