Changes between Initial Version and Version 1 of Ticket #16165


Ignore:
Timestamp:
Jun 7, 2011, 6:08:52 AM (14 years ago)
Author:
Karen Tracey
Comment:

Wrapped SQL to allow for easier reading.

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #16165 – Description

    initial v1  
    22
    33{{{
    4 
     4#!python
    55class BiddingRule(models.Model):
    66    name = models.CharField(max_length=80)
    … …  
    140140>>> stats = stats.annotate(**annotations)
    141141>>> print(stats.query)
    142 SELECT `mars_adgroupstat`.`ad_group_id`, `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`, `mars_account`.`engine`, `mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`, `mars_biddingrule`.`name`, `mars_adgroup`.`visible_tags`, AVG(`mars_adgroupstat`.`average_position`) AS `_average_position`, SUM(`mars_adgroupstat`.`conversions`) AS `_conversions`, SUM(`mars_adgroupstat`.`clicks`) AS `_clicks`, SUM(`mars_adgroupstat`.`cost`) AS `_cost`, SUM(`mars_adgroupstat`.`revenue`) AS `_revenue`, SUM(`mars_adgroupstat`.`impressions`) AS `_impressions` FROM `mars_adgroupstat` INNER JOIN `mars_adgroup` ON (`mars_adgroupstat`.`ad_group_id` = `mars_adgroup`.`id`) INNER JOIN `mars_campaign` ON (`mars_adgroup`.`campaign_id` = `mars_campaign`.`id`) INNER JOIN `mars_account` ON (`mars_campaign`.`account_id` = `mars_account`.`id`) LEFT OUTER JOIN `mars_biddingrule` ON (`mars_adgroup`.`bidding_rule_id` = `mars_biddingrule`.`id`)
    143 GROUP BY `mars_adgroupstat`.`id`, `mars_adgroupstat`.`ad_group_id`, `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`, `mars_account`.`engine`, `mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`, `mars_biddingrule`.`name`, `mars_adgroup`.`visible_tags` ORDER BY NULL
     142SELECT
     143    `mars_adgroupstat`.`ad_group_id`,
     144    `mars_adgroup`.`engine_id`,
     145    `mars_adgroup`.`ad_group`,
     146    `mars_account`.`engine`,
     147    `mars_campaign`.`campaign`,
     148    `mars_adgroup`.`max_cpc`,
     149    `mars_adgroup`.`content_max_cpc`,
     150    `mars_adgroup`.`status`,
     151    `mars_biddingrule`.`name`,
     152    `mars_adgroup`.`visible_tags`,
     153    AVG(`mars_adgroupstat`.`average_position`) AS `_average_position`,
     154    SUM(`mars_adgroupstat`.`conversions`) AS `_conversions`,
     155    SUM(`mars_adgroupstat`.`clicks`) AS `_clicks`,
     156    SUM(`mars_adgroupstat`.`cost`) AS `_cost`,
     157    SUM(`mars_adgroupstat`.`revenue`) AS `_revenue`,
     158    SUM(`mars_adgroupstat`.`impressions`) AS `_impressions`
     159FROM
     160    `mars_adgroupstat`
     161INNER JOIN
     162    `mars_adgroup` ON (`mars_adgroupstat`.`ad_group_id` = `mars_adgroup`.`id`)
     163INNER JOIN
     164    `mars_campaign` ON (`mars_adgroup`.`campaign_id` = `mars_campaign`.`id`)
     165INNER JOIN
     166    `mars_account` ON (`mars_campaign`.`account_id` = `mars_account`.`id`)
     167LEFT OUTER JOIN
     168    `mars_biddingrule` ON (`mars_adgroup`.`bidding_rule_id` = `mars_biddingrule`.`id`)
     169GROUP BY
     170    `mars_adgroupstat`.`id`,
     171    `mars_adgroupstat`.`ad_group_id`,
     172    `mars_adgroup`.`engine_id`,
     173    `mars_adgroup`.`ad_group`,
     174    `mars_account`.`engine`,
     175    `mars_campaign`.`campaign`,
     176    `mars_adgroup`.`max_cpc`,
     177    `mars_adgroup`.`content_max_cpc`,
     178    `mars_adgroup`.`status`,
     179    `mars_biddingrule`.`name`,
     180    `mars_adgroup`.`visible_tags`
     181ORDER BY NULL
    144182>>> stats = AdGroupStat.objects.values(*ad_group_values[:-1])
    145183>>> stats = stats.annotate(**annotations)
    146184>>> print(stats.query)
    147 SELECT `mars_adgroupstat`.`ad_group_id`, `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`, `mars_account`.`engine`, `mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`, `mars_biddingrule`.`name`, AVG(`mars_adgroupstat`.`average_position`) AS `_average_position`, SUM(`mars_adgroupstat`.`conversions`) AS `_conversions`, SUM(`mars_adgroupstat`.`clicks`) AS `_clicks`, SUM(`mars_adgroupstat`.`cost`) AS `_cost`, SUM(`mars_adgroupstat`.`revenue`) AS `_revenue`, SUM(`mars_adgroupstat`.`impressions`) AS `_impressions` FROM `mars_adgroupstat` INNER JOIN `mars_adgroup` ON (`mars_adgroupstat`.`ad_group_id` = `mars_adgroup`.`id`) INNER JOIN `mars_campaign` ON (`mars_adgroup`.`campaign_id` = `mars_campaign`.`id`) INNER JOIN `mars_account` ON (`mars_campaign`.`account_id` = `mars_account`.`id`) LEFT OUTER JOIN `mars_biddingrule` ON (`mars_adgroup`.`bidding_rule_id` = `mars_biddingrule`.`id`)
    148 GROUP BY `mars_adgroupstat`.`ad_group_id`, `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`, `mars_account`.`engine`, `mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`, `mars_biddingrule`.`name`, `mars_adgroupstat`.`ad_group_id`, `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`, `mars_account`.`engine`, `mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`, `mars_biddingrule`.`name` ORDER BY NULL
     185SELECT
     186    `mars_adgroupstat`.`ad_group_id`,
     187    `mars_adgroup`.`engine_id`,
     188    `mars_adgroup`.`ad_group`,
     189    `mars_account`.`engine`,
     190    `mars_campaign`.`campaign`,
     191    `mars_adgroup`.`max_cpc`,
     192    `mars_adgroup`.`content_max_cpc`,
     193    `mars_adgroup`.`status`, 
     194    `mars_biddingrule`.`name`, 
     195    AVG(`mars_adgroupstat`.`average_position`) AS `_average_position`,
     196    SUM(`mars_adgroupstat`.`conversions`) AS `_conversions`,
     197    SUM(`mars_adgroupstat`.`clicks`) AS `_clicks`,
     198    SUM(`mars_adgroupstat`.`cost`) AS `_cost`,
     199    SUM(`mars_adgroupstat`.`revenue`) AS `_revenue`,
     200    SUM(`mars_adgroupstat`.`impressions`) AS `_impressions`
     201FROM
     202    `mars_adgroupstat`
     203INNER JOIN
     204    `mars_adgroup` ON (`mars_adgroupstat`.`ad_group_id` = `mars_adgroup`.`id`)
     205INNER JOIN
     206    `mars_campaign` ON (`mars_adgroup`.`campaign_id` = `mars_campaign`.`id`)
     207INNER JOIN
     208    `mars_account` ON (`mars_campaign`.`account_id` = `mars_account`.`id`)
     209LEFT OUTER JOIN
     210    `mars_biddingrule` ON (`mars_adgroup`.`bidding_rule_id` = `mars_biddingrule`.`id`)
     211GROUP BY
     212    `mars_adgroupstat`.`ad_group_id`,
     213    `mars_adgroup`.`engine_id`,
     214    `mars_adgroup`.`ad_group`,
     215    `mars_account`.`engine`,
     216    `mars_campaign`.`campaign`,
     217    `mars_adgroup`.`max_cpc`,
     218    `mars_adgroup`.`content_max_cpc`,
     219    `mars_adgroup`.`status`,
     220    `mars_biddingrule`.`name`,
     221    `mars_adgroupstat`.`ad_group_id`,
     222    `mars_adgroup`.`engine_id`,
     223    `mars_adgroup`.`ad_group`,
     224    `mars_account`.`engine`,
     225    `mars_campaign`.`campaign`,
     226    `mars_adgroup`.`max_cpc`,
     227    `mars_adgroup`.`content_max_cpc`,
     228    `mars_adgroup`.`status`,
     229    `mars_biddingrule`.`name`
     230ORDER BY NULL
    149231>>> stats = AdGroupStat.objects.values(*ad_group_values[1:])
    150232>>> stats = stats.annotate(**annotations)
    151233>>> print(stats.query)
    152 SELECT `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`, `mars_account`.`engine`, `mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`, `mars_biddingrule`.`name`, `mars_adgroup`.`visible_tags`, AVG(`mars_adgroupstat`.`average_position`) AS `_average_position`, SUM(`mars_adgroupstat`.`conversions`) AS `_conversions`, SUM(`mars_adgroupstat`.`clicks`) AS `_clicks`, SUM(`mars_adgroupstat`.`cost`) AS `_cost`, SUM(`mars_adgroupstat`.`revenue`) AS `_revenue`, SUM(`mars_adgroupstat`.`impressions`) AS `_impressions` FROM `mars_adgroupstat` INNER JOIN `mars_adgroup` ON (`mars_adgroupstat`.`ad_group_id` = `mars_adgroup`.`id`) INNER JOIN `mars_campaign` ON (`mars_adgroup`.`campaign_id` = `mars_campaign`.`id`) INNER JOIN `mars_account` ON (`mars_campaign`.`account_id` = `mars_account`.`id`) LEFT OUTER JOIN `mars_biddingrule` ON (`mars_adgroup`.`bidding_rule_id` = `mars_biddingrule`.`id`)
    153 GROUP BY `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`, `mars_account`.`engine`, `mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`, `mars_biddingrule`.`name`, `mars_adgroup`.`visible_tags`, `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`, `mars_account`.`engine`, `mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`, `mars_biddingrule`.`name`, `mars_adgroup`.`visible_tags` ORDER BY NULL
     234SELECT
     235    `mars_adgroup`.`engine_id`,
     236    `mars_adgroup`.`ad_group`,
     237    `mars_account`.`engine`,
     238    `mars_campaign`.`campaign`,
     239    `mars_adgroup`.`max_cpc`, 
     240    `mars_adgroup`.`content_max_cpc`,
     241    `mars_adgroup`.`status`,
     242    `mars_biddingrule`.`name`,
     243    `mars_adgroup`.`visible_tags`,
     244    AVG(`mars_adgroupstat`.`average_position`) AS `_average_position`,
     245    SUM(`mars_adgroupstat`.`conversions`) AS `_conversions`,
     246    SUM(`mars_adgroupstat`.`clicks`) AS `_clicks`,
     247    SUM(`mars_adgroupstat`.`cost`) AS `_cost`,
     248    SUM(`mars_adgroupstat`.`revenue`) AS `_revenue`,
     249    SUM(`mars_adgroupstat`.`impressions`) AS `_impressions`
     250FROM
     251    `mars_adgroupstat`
     252INNER JOIN
     253    `mars_adgroup` ON (`mars_adgroupstat`.`ad_group_id` = `mars_adgroup`.`id`)
     254INNER JOIN
     255    `mars_campaign` ON (`mars_adgroup`.`campaign_id` = `mars_campaign`.`id`)
     256INNER JOIN
     257    `mars_account` ON (`mars_campaign`.`account_id` = `mars_account`.`id`)
     258LEFT OUTER JOIN
     259    `mars_biddingrule` ON (`mars_adgroup`.`bidding_rule_id` = `mars_biddingrule`.`id`)
     260GROUP BY
     261    `mars_adgroup`.`engine_id`,
     262    `mars_adgroup`.`ad_group`,
     263    `mars_account`.`engine`,
     264    `mars_campaign`.`campaign`,
     265    `mars_adgroup`.`max_cpc`, 
     266    `mars_adgroup`.`content_max_cpc`,
     267    `mars_adgroup`.`status`,
     268    `mars_biddingrule`.`name`,
     269    `mars_adgroup`.`visible_tags`,
     270    `mars_adgroup`.`engine_id`,
     271    `mars_adgroup`.`ad_group`,
     272    `mars_account`.`engine`,
     273    `mars_campaign`.`campaign`,
     274    `mars_adgroup`.`max_cpc`,
     275    `mars_adgroup`.`content_max_cpc`,
     276    `mars_adgroup`.`status`,
     277    `mars_biddingrule`.`name`,
     278    `mars_adgroup`.`visible_tags`
     279ORDER BY NULL
    154280>>>
    155281}}}
    … …  
    157283In the first query, using all 10 arguments in .values, the GROUP BY has 11 items as it seems to be adding the local model id:
    158284{{{
    159 GROUP BY `mars_adgroupstat`.`id`, `mars_adgroupstat`.`ad_group_id`, `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`, `mars_account`.`engine`, `mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`, `mars_biddingrule`.`name`, `mars_adgroup`.`visible_tags`
     285GROUP BY
     286    `mars_adgroupstat`.`id`,
     287    `mars_adgroupstat`.`ad_group_id`, 
     288    `mars_adgroup`.`engine_id`,
     289    `mars_adgroup`.`ad_group`,
     290    `mars_account`.`engine`,
     291    `mars_campaign`.`campaign`,
     292    `mars_adgroup`.`max_cpc`,
     293    `mars_adgroup`.`content_max_cpc`,
     294    `mars_adgroup`.`status`,
     295    `mars_biddingrule`.`name`,
     296    `mars_adgroup`.`visible_tags`
    160297}}}
    161298In the second or third queries, using 9 arguments in .values, the GROUP BY has 18 items as it seems to be doubling the GROUP BY args:
    162299{{{
    163 GROUP BY `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`, `mars_account`.`engine`, `mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`, `mars_biddingrule`.`name`, `mars_adgroup`.`visible_tags`, `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`, `mars_account`.`engine`, `mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`, `mars_biddingrule`.`name`, `mars_adgroup`.`visible_tags`
    164 
    165 GROUP BY `mars_adgroupstat`.`ad_group_id`, `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`, `mars_account`.`engine`, `mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`, `mars_biddingrule`.`name`, `mars_adgroupstat`.`ad_group_id`, `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`, `mars_account`.`engine`, `mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`, `mars_biddingrule`.`name`
     300GROUP BY
     301    `mars_adgroup`.`engine_id`,
     302    `mars_adgroup`.`ad_group`,
     303    `mars_account`.`engine`,
     304    `mars_campaign`.`campaign`,
     305    `mars_adgroup`.`max_cpc`,
     306    `mars_adgroup`.`content_max_cpc`,
     307    `mars_adgroup`.`status`,
     308    `mars_biddingrule`.`name`,
     309    `mars_adgroup`.`visible_tags`,
     310    `mars_adgroup`.`engine_id`,
     311    `mars_adgroup`.`ad_group`,
     312    `mars_account`.`engine`,
     313    `mars_campaign`.`campaign`,
     314    `mars_adgroup`.`max_cpc`,
     315    `mars_adgroup`.`content_max_cpc`,
     316    `mars_adgroup`.`status`,
     317    `mars_biddingrule`.`name`,
     318    `mars_adgroup`.`visible_tags`
     319
     320GROUP BY
     321    `mars_adgroupstat`.`ad_group_id`,
     322    `mars_adgroup`.`engine_id`,
     323    `mars_adgroup`.`ad_group`,
     324    `mars_account`.`engine`,
     325    `mars_campaign`.`campaign`,
     326    `mars_adgroup`.`max_cpc`,
     327    `mars_adgroup`.`content_max_cpc`,
     328    `mars_adgroup`.`status`,
     329    `mars_biddingrule`.`name`,
     330    `mars_adgroupstat`.`ad_group_id`,
     331    `mars_adgroup`.`engine_id`,
     332    `mars_adgroup`.`ad_group`,
     333    `mars_account`.`engine`,
     334    `mars_campaign`.`campaign`,
     335    `mars_adgroup`.`max_cpc`,
     336    `mars_adgroup`.`content_max_cpc`,
     337    `mars_adgroup`.`status`,
     338    `mars_biddingrule`.`name`
    166339}}}
    167340
Back to Top