function listSkus() { $curSkus = new Cursor($this->db); // the below sql statement, formed by a union of two nearly identical select statements, is done this way to avoid // the fact that MySQL would not use the available indices when the "on" clause for the prod_sku table join tried to use // an "or" to select skus in either of two columns. So we get all the records formed with the join on one column, and // union those with the records formed with the join on the other column. Ugly. $sql1 = "select distinct prod_sku.sku, ". "Prom_ad_blk_sku.no_raincheck, ". "Prom_ad_blk_sku.line_mention, ". "Prom_ad_blk_sku.sunday, ". "Prom_ad_blk_sku.dsd_retl_amt, ". "Prom_ad_version_blk_sku.pg_plan, ". "Prom_ad_version_blk_sku.pct_dol_off, ". "Prom_ad_version_blk_sku.use_reg_retl, ". "Prom_ad_version_blk_sku.offer_sale_qty, ". "Prom_ad_version_blk_sku.offer_sale_amt, ". "Prom_ad_version_blk_sku.offer_buy_qty, ". "Prom_ad_version_blk_sku.offer_get_qty, ". "Prom_ad_version_blk_sku.offer_for_dol, ". "Prom_ad_version_blk_sku.offer_buy_get, ". "Prom_ad_version_blk_sku.cat_nbr, ". "Prom_ad_version_blk_sku.seg_nbr, ". "Prom_ad_version_blk_sku.zone1_calc_amt, ". "Prom_ad_version_blk_sku.sale_amt, ". "Prom_ad_version_blk_sku.sale_offer_text, ". "Prom_ad_version_blk_sku.sale_offer_text_calc, ". "Prom_ad_version_blk_sku.get_offer_text_calc, ". "prod_sku.sku_desc, ". "prod_sku.cm, ". "prod_sku.vpmm, ". "prod_sku.seg, ". "prod_sku.flavor_nbr, ". "prod_sku.size, ". "prod_sku.z1_retl_amt, " . "prod_sku.zoned_price ". "from Prom_ad_version_blk_sku ". "right join prod_sku on prod_sku.sku = Prom_ad_version_blk_sku.sku " . "join Prom_ad_blk_sku on Prom_ad_blk_sku.sku = Prom_ad_version_blk_sku.sku " . "AND Prom_ad_blk_sku.adblock = Prom_ad_version_blk_sku.adblock ". "AND Prom_ad_blk_sku.ad_date = Prom_ad_version_blk_sku.ad_date ". "AND Prom_ad_blk_sku.ad_ptype = Prom_ad_version_blk_sku.ad_ptype ". "where Prom_ad_version_blk_sku.adblock = '" . $this->adblock . "' " . "AND Prom_ad_version_blk_sku.ad_date = '" . $this->ad_date . "' " . "AND Prom_ad_version_blk_sku.ad_ptype = '" . $this->ad_ptype . "' ". "AND Prom_ad_version_blk_sku.ad_version = '" . $this->ad_version . "' ". "AND (Prom_ad_version_blk_sku.deletedFlag is null OR (Prom_ad_version_blk_sku.deletedFlag <> 'Y'))" . "group by sku " ; $sql2 = "select distinct prod_sku.sku, ". "Prom_ad_blk_sku.no_raincheck, ". "Prom_ad_blk_sku.line_mention, ". "Prom_ad_blk_sku.sunday, ". "Prom_ad_blk_sku.dsd_retl_amt, ". "Prom_ad_version_blk_sku.pg_plan, ". "Prom_ad_version_blk_sku.pct_dol_off, ". "Prom_ad_version_blk_sku.use_reg_retl, ". "Prom_ad_version_blk_sku.offer_sale_qty, ". "Prom_ad_version_blk_sku.offer_sale_amt, ". "Prom_ad_version_blk_sku.offer_buy_qty, ". "Prom_ad_version_blk_sku.offer_get_qty, ". "Prom_ad_version_blk_sku.offer_for_dol, ". "Prom_ad_version_blk_sku.offer_buy_get, ". "Prom_ad_version_blk_sku.cat_nbr, ". "Prom_ad_version_blk_sku.seg_nbr, ". "Prom_ad_version_blk_sku.zone1_calc_amt, ". "Prom_ad_version_blk_sku.sale_amt, ". "Prom_ad_version_blk_sku.sale_offer_text, ". "Prom_ad_version_blk_sku.sale_offer_text_calc, ". "Prom_ad_version_blk_sku.get_offer_text_calc, ". "prod_sku.sku_desc, ". "prod_sku.cm, ". "prod_sku.vpmm, ". "prod_sku.seg, ". "prod_sku.flavor_nbr, ". "prod_sku.size, ". "prod_sku.z1_retl_amt, " . "prod_sku.zoned_price " . "from Prom_ad_version_blk_sku ". "right join prod_sku on prod_sku.prim_flavor_sku = Prom_ad_version_blk_sku.sku " . "join Prom_ad_blk_sku on Prom_ad_blk_sku.sku = Prom_ad_version_blk_sku.sku " . "AND Prom_ad_blk_sku.adblock = Prom_ad_version_blk_sku.adblock ". "AND Prom_ad_blk_sku.ad_date = Prom_ad_version_blk_sku.ad_date ". "AND Prom_ad_blk_sku.ad_ptype = Prom_ad_version_blk_sku.ad_ptype ". "where Prom_ad_version_blk_sku.adblock = '" . $this->adblock . "' " . "AND Prom_ad_version_blk_sku.ad_date = '" . $this->ad_date . "' " . "AND Prom_ad_version_blk_sku.ad_ptype = '" . $this->ad_ptype . "' ". "AND Prom_ad_version_blk_sku.ad_version = '" . $this->ad_version . "' ". "AND (Prom_ad_version_blk_sku.deletedFlag is null OR (Prom_ad_version_blk_sku.deletedFlag <> 'Y'))" . "group by sku " ; $sql = "(" . $sql1 . ") UNION (" . $sql2 . ") order by sku "; //echo $sql; $curSkus->execute($sql); return $curSkus; }
i can't decide whether this is elegant, or insane. either way - i take my hat off to ya. that's one mouthful of a query. VG
hmm... must have taken quite a while to type that query out when you did your coding... anyway, you may want to test out how long the query takes before you make it live. if your dataset is large, it may cause problems...
Here is another query thats gets used in this application: /******************************************************************************\ * METHOD: populatePages * PURPOSE: for a given marketing version connected to this page set, connects every * relevant ad block to its correct page (as slotted by the promo database) * INPUT: marketing version * (optional) page version (set to 9999 if want to build temporary page) * (optional) page number if only want to populate one page. * (optional) zone qualifier string * OUTPUT: none \*******************************************************************************/ function populatePages($ad_version_parm, $pg_version_parm=0, $pg_num_parm=-1, $zq_parm='') { statusUpdate('populating pages for ad version ' . $ad_version_parm); $pg_num_var = ($pg_num_parm==-1)?null:_buildPageName($pg_num_parm); $curAb = new Cursor($this->db); $sql = "( select a.adblock as ab, a.pg_target as pgnum, c.adblock_name as name from prom_ad_version_blk as a left join ad_override as b on a.ad_date=b.ad_date and a.ad_ptype=b.ad_ptype and a.adblock=b.adblock and (a.ad_version= b.ad_version OR b.ad_version='++') and b.override_type='2' left join prom_ad_blk_lib_header as c on a.adblock=c.adblock where a.ad_date='$this->ad_date' and a.ad_ptype='$this->ad_ptype' and a.ad_version='$ad_version_parm' "; if (!empty($pg_num_var)) $sql .= " and a.pg_target='$pg_num_var' "; $sql .= "and b.override_no is null ) UNION ( select a.adblock as ab, if (b1.override_page is not null,b1.override_page, if (b2.override_page is not null, b2.override_page,a.pg_target)) as pgnum, c.adblock_name as name from prom_ad_version_blk as a left join ad_override as b1 on a.ad_date=b1.ad_date and a.ad_ptype=b1.ad_ptype and a.adblock=b1.adblock and a.ad_version= b1.ad_version and (b1.override_type='1') left join ad_override as b2 on a.ad_date=b2.ad_date and a.ad_ptype=b2.ad_ptype and a.adblock=b2.adblock and b2.ad_version='++' and (b2.override_type='1') left join prom_ad_blk_lib_header as c on a.adblock=c.adblock where a.ad_date='$this->ad_date' and a.ad_ptype='$this->ad_ptype' and a.ad_version='$ad_version_parm' and ((b1.override_page is not null "; if (!empty($pg_num_var)) $sql .= " and b1.override_page='$pg_num_var' "; $sql .= ") or (b1.override_page is null and b2.override_page is not null "; if (!empty($pg_num_var)) $sql .= " and b2.override_page='$pg_num_var' "; $sql .= ")) ) UNION ( select a.adblock as ab, if (b1.override_page is not null,b1.override_page, if (b2.override_page is not null, b2.override_page,a.pg_target)) as pgnum, c.adblock_name as name from prom_ad_version_blk as a left join ad_override as b1 on a.ad_date=b1.ad_date and a.ad_ptype=b1.ad_ptype and a.adblock=b1.adblock and a.ad_version= b1.ad_version and (b1.override_type='2') left join ad_override as b2 on a.ad_date=b2.ad_date and a.ad_ptype=b2.ad_ptype and a.adblock=b2.adblock and b2.ad_version='++' and (b2.override_type='2') left join prom_ad_blk_lib_header as c on a.adblock=c.adblock where a.ad_date='$this->ad_date' and a.ad_ptype='$this->ad_ptype' and a.ad_version='$ad_version_parm' and ((b1.override_page is not null "; if (!empty($pg_num_var)) $sql .= " and b1.override_page='$pg_num_var' "; $sql .= ") or (b1.override_page is null and b2.override_page is not null "; if (!empty($pg_num_var)) $sql .= " and b2.override_page='$pg_num_var' "; $sql .= ")) ) order by pgnum,ab"; //$err = fopen("php://stderr","w"); //fwrite($err,$sql); //fclose($err); $curAb->execute($sql); while ($curAb->getRecord()) { // for each ad block $ap = $curAb->pgnum; // get page number and normalize to integer $ab = $curAb->ab; $ap = _unbuildPageName($ap); $procare = (substr($curAb->name,0,7) == 'PROCARE')?true:false; if (($ap < 900) && !$procare) { // pages over 900 are deleted -- ignore them $oPage = $this->createPage($ap, $pg_version_parm, $zq_parm); // get page object (usually already there). $oPage->connectVersion($ad_version_parm); $oPage->connectAdBlockVersion($ab, $curAb->linkblock); } } $curAb->close(); }
"*" and joins can be toxic together - and he's redefining field names from different tables i.e. "select a.adblock as ab" for easier reference throughout the remainder of the app. it gets cumbersome to have to call each variable by table.fieldname throughout your app, and if you have identical field names, it's even more of a nightmare.
It's actually fun to hear that things used in programming language are currently bad... That makes me laugh. Do you have any proof to support your statement?
um, well i thought i backed my statement up pretty well, i never said it was "bad" though i did say it was "toxic" That said - i'll totally concurr that this is mostly from my own experience - while using "*" works, i just find that when using joins, it gets, as i stated, cumbersome. Bad practice? not really, i use "*" all the time. it makes for excellent shorthand in the right places at the right time, it's a huge timesaver.
bad practice ... who defines what it is Each company, programmer builds his own set of good/bad practices, and there is not really one guys that is going to be right about it. My reason for NOT using the * is: 1. Memory ( save memory on the db server, a few bytes I hear you say... multiple by let's say a thousand rows, multiply by maybe 250-500 users). 2. easier to debug and give more control 3. Less disk i/o aka better performance 4. Can't remem the rest, but there is quite a few ...