pysnow 0.7.5-如何使用SQL中的2个表进行复杂的查询

Question

我正在使用pysnow0.7.5。

我现在想从2个服务表(">rm_defect"one_answers">u_cmdb_ci_appl_entreprise(中提取一部分数据

这些表通过u_cmdb_ci_appl_entreprise.sys_id=rm_defect.u_application.value链接

如何像SQL中那样用这两个表进行复杂的查询？类似的东西：

qb = (
pysnow.QueryBuilder()
.field('rm_defect.number').contains('ANO01234')
.AND()
.field('rm_defect.u_application.value').equals('u_cmdb_ci_appl_entreprise.sys_id')
)

这是我的源

import pysnow 
import os
instance='XXXX' user='XXXX' password='XXXX'
c = pysnow.Client(instance=instance, user=user, password=password)
# List of defects defectResource = c.resource(api_path='/table/rm_defect')
qb = (
pysnow.QueryBuilder()
.field('number').contains('ANO01234') )
defectRecords = defectResource.get(query=qb,stream=True)
print('rm_defect:') for defectRecord in defectRecords.all():
number = str(defectRecord['number'])
state = str(defectRecord['state'])
if (isinstance(defectRecord['u_application'], dict)): 
application = defectRecord['u_application']['value']
else:
application = ""
print('number '+number)
print('state '+state)
print('application '+application)
print('------------')

# List of applications appResource = c.resource(api_path='/table/u_cmdb_ci_appl_entreprise')
qb = (
pysnow.QueryBuilder()
.field('name').equals('appli1') )
appRecords = appResource.get(query=qb)
for appRecord in appRecords.all():
sys_id = str(appRecords['sys_id'])
name = str(appRecords['name'])
print('sys_id '+sys_id)
print('name '+name)
print('------------')

这是的结果

number ANO01234
state 15
application 551de62ddbe02e80e478751bbf9619b0
------------
sys_id 551de62ddbe02e80e478751bbf9619b0
name appli1
------------

感谢您的帮助

Answer 1

您可以创建包含这两个表的视图，然后针对该视图而不是单个表运行查询。

系统定义=>数据库视图[sys_db_view]

例如，您可以查看包含偶发事件和名为incident_sla的task_sla的视图。请注意变量前缀列。这在查询视图时非常重要。可以通过以下方式查询此类视图。

var gr = new GlideRecord('incident_sla');
gr.setLimit(10);
gr.query();
while(gr.next()) {
gs.print(gr.getValue('inc_number') + ' ---- ' + gr.getDisplayValue('taskslatable_sla'));
}

此脚本将从偶发事件表返回number，从task_sla表返回sla。