Django 1.11.11 ORM 内关联 起别名 尝试CASE WHEN

这是模型类

class Market(models.Model):\"\"\"市场\"\"\"id = models.AutoField(primary_key=True)market_name = models.CharField(max_length=100L, unique=True, help_text=\'市场名称\')market_url = models.CharField(max_length=100L, help_text=\'市场地址\')platform = models.CharField(max_length=50, null=True, help_text=\'平台,Amazon,ebay等\')country_code = models.CharField(max_length=30L, help_text=\'市场地区代号,US,CN\')market_abbreviation = models.CharField(max_length=30L, null=True, help_text=\'市场简写\')class Meta:db_table = \'market_market\'app_label = \'market\'managed = Falsedef __unicode__(self):return \'%s\' % self.market_nameclass UrgentRule(models.Model):CHANNEL = ((\'Aliexpress\', \'Aliexpress\'),...)MESSAGE = {(\'a1\',\'加急单无发票\'),(\'a2\', \'加急单有发票\'),(\'a3\', \'Lazada加急单\'),(\'a4\', \'Ebay加急单\'),(\'a5\', \'Shopee加急单\'),(\'a6\', \'加急\'),(\'3\',\'亚马逊加急\')}id = models.AutoField(primary_key=True,help_text=\'id\')channel = models.CharField(max_length=50L,blank=True,null=True,choices=channel_choices(),help_text=\'平台\')market = models.ForeignKey(\'pro_db_models.Market\', blank=True, null=True, on_delete=models.SET_NULL,help_text=\'站点(不填则是全站点)\')store =  models.ForeignKey(\'order.Store\',db_constraint=False,null=True, blank=True, on_delete=models.SET_NULL, db_index = True,help_text=\'店铺(不填则是全店铺)\')#网店编号priority_status = models.CharField(max_length=50L, blank=True, null=True, choices=MESSAGE, help_text=\'加急标识\')priority_status_msg = models.CharField(max_length=50L, blank=True, null=True, help_text=\'加急备注\')is_alive = models.BooleanField(default=True,help_text=\'是否可用\')level = models.IntegerField(blank=False,null=False,help_text=\'等级\')def __init__(self, *args, **kargs):super(UrgentRule, self).__init__(*args, **kargs)self._meta.get_field(\'channel\').choices = channel_choices()class Meta:db_table = \'urgent_rule\'app_label = \'order\'managed = False

这里是查询

import itertoolsMESSAGE = [repr(str(i)) for i in itertools.chain.from_iterable(UrgentRule.MESSAGE) if isinstance(i, (str,unicode))]channel = request.POST.get(\'channel\', \'\')is_active = request.POST.get(\'is_active\', \'\')rules = UrgentRule.objects.extra(select={\'market\':\'market_market.market_name\',\'priority_status\':\'case priority_status {} else priority_status end\'.format((len(MESSAGE)/2 * \'when {} then {} \').format(*MESSAGE))},where=[\'urgent_rule.market_id = market_market.id\'],tables=[\'market_market\']).values(\'id\',\'channel\',\'market\',\'priority_status\',\'priority_status_msg\',\'is_alive\',\'level\')rows = list(rules)

这里是结果 使用内连接查询

print rules.querySELECT (case priority_status when \'3\' then \'\\xe4\\xba\\x9a\\xe9\\xa9\\xac\\xe9\\x80\\x8a\\xe5\\x8a\\xa0\\xe6\\x80\\xa5\' when \'a3\' then \'Lazada\\xe5\\x8a\\xa0\\xe6\\x80\\xa5\\xe5\\x8d\\x95\' when \'a2\' then \'\\xe5\\x8a\\xa0\\xe6\\x80\\xa5\\xe5\\x8d\\x95\\xe6\\x9c\\x89\\xe5\\x8f\\x91\\xe7\\xa5\\xa8\' when \'a6\' then \'\\xe5\\x8a\\xa0\\xe6\\x80\\xa5\' when \'a5\' then \'Shopee\\xe5\\x8a\\xa0\\xe6\\x80\\xa5\\xe5\\x8d\\x95\' when \'a4\' then \'Ebay\\xe5\\x8a\\xa0\\xe6\\x80\\xa5\\xe5\\x8d\\x95\' when \'a1\' then \'\\xe5\\x8a\\xa0\\xe6\\x80\\xa5\\xe5\\x8d\\x95\\xe6\\x97\\xa0\\xe5\\x8f\\x91\\xe7\\xa5\\xa8\'  else priority_status end) AS `priority_status`, (market_market.market_name) AS `market`, `urgent_rule`.`id`, `urgent_rule`.`channel`, `urgent_rule`.`priority_status_msg`, `urgent_rule`.`is_alive`, `urgent_rule`.`level` FROM `urgent_rule` , `market_market` WHERE (urgent_rule.market_id = market_market.id)rule<QuerySet [{\'level\': 3L, \'priority_status_msg\': u\'\', \'id\': 3L, \'channel\': u\'Amazon\', \'priority_status\': u\'xe4xbax9axe9xa9xacxe9x80x8axe5x8axa0xe6x80xa5\', \'is_alive\': True, \'market\': u\'Amazon.co.jp\'}, {\'level\': 2L, \'priority_status_msg\': u\'\', \'id\': 4L, \'channel\': u\'Rakuten\', \'priority_status\': u\'xe5x8axa0xe6x80xa5\', \'is_alive\': True, \'market\': u\'Rakuten.com\'}, {\'level\': 2L, \'priority_status_msg\': u\'\', \'id\': 5L, \'channel\': u\'Rakuten\', \'priority_status\': u\'xe5x8axa0xe6x80xa5\', \'is_alive\': True, \'market\': u\'Rakuten.com\'}, {\'level\': 1L, \'priority_status_msg\': u\'\', \'id\': 6L, \'channel\': u\'WeMore\', \'priority_status\': u\'xe5x8axa0xe6x80xa5\', \'is_alive\': True, \'market\': u\'WeMore.com\'}, {\'level\': 1L, \'priority_status_msg\': u\'\', \'id\': 7L, \'channel\': u\'Wish\', \'priority_status\': u\'xe5x8axa0xe6x80xa5\', \'is_alive\': True, \'market\': u\'Wish.com\'}, {\'level\': 1L, \'priority_status_msg\': u\'\', \'id\': 8L, \'channel\': u\'B2W\', \'priority_status\': u\'xe5x8axa0xe6x80xa5\', \'is_alive\': True, \'market\': u\'B2W.com\'}]>s

这里是参考

# python将嵌套数组转为单层数组参考连接：https://blog.csdn.net/weixin_39541558/article/details/80060175import itertoolsa = [[1,2,3],[4,5,6], [7], [8,9]]out = list(itertools.chain.from_iterable(a)). >>> [ 1,2,3,4,5,6,7,8,9]

这里是参考

# 方法一、annotatedata = Artitle.objects.annotate(alias=F(\'name\')).values(\'alias\')# 更简单的方式 这里如果用market作为别名 会与UrgentRule的market可他冲突UrgentRule.objects.annotate(market_name=F(\'market__market_name\')).values(\'id\',\'channel\',\'market_name\',\'priority_status\',\'priority_status_msg\',\'is_alive\',\'level\')# sql# SELECT `urgent_rule`.`id`, `urgent_rule`.`channel`, `urgent_rule`.`priority_status`, `urgent_rule`.`priority_status_msg`, `urgent_rule`.`is_alive`, `urgent_rule`.`level`, `market_market`.`market_name` AS `market_name` FROM `urgent_rule` LEFT OUTER JOIN `market_market` ON (`urgent_rule`.`market_id` = `market_market`.`id`)# 方法二、extra,对于关系表的字段取别名这种方法好像不行，也许是方法没用对data= Artitle.objects.extra({\'alias\':\'name\'}).values(\'alias\')# 原文链接: https://blog.csdn.net/qq_32744307/article/details/79145188# 参考链接: https://blog.csdn.net/max229max/article/details/84373821

Django 3.0.3中可以直接实现

from django.db.models import FStore.objects.values(xxxx=F(\'channel_center\')).first()# output {\'xxxx\': 3}Store.objects.values(xxxx=F(\'channel_center__name\')).first()# output {\'xxxx\': \'center one\'}# https://stackoverflow.com/questions/10598940/how-to-rename-items-in-values-in-django

其他

## mysql replace## http://www.itkeyword.com/doc/7243359683935386x450/django-bulk-update-with-string-replace## https://blog.csdn.net/myarche/article/details/84312787UPDATE oms_store SET name=replace(name, \'TANK\', \'tank\') WHERE id=3;from django.db.models import Value,Func,FStore.objects.filter(id=3).update(name=Func(F(\'name\'), Value(\'tank\'), Value(\'TANK\'), function=\'replace\'))

CASE WHEN

https://docs.djangoproject.com/en/dev/ref/models/conditional-expressions/