1С: Программное получение структуры подчиненности

Время от времени прилетают задачки, связанные с получением подчиненных объектов. Последний раз было необходимо автоматизировано проконтролировать весь ли набор документов был создан на основании заказа.

Один из способов контроля – это использование структуры подчиненности. Но структура подчиненности в чистом виде удобна для использования в пользовательских режимах, но не очень удобная для программистов.

Рассматриваем на примере 1С: УНФ 3.0 (одна из наиболее простых конфигураций).

Стоит начать с того, что под капотом структура подчиненности работает на базе объектов конфигурации «Критерии отбора»

Где основные настройки находятся в разделах «Данные» и «Состав»

Если упрощать, то искать связанные (речь пока не о структуре подчиненности) объекты можно двумя способами, которые в целом под капотом работают одинаково.

Кодом:

НайденныеСвязанныеДокументы = КритерииОтбора.СвязанныеДокументы.Найти(ДокументСсылка);

1	НайденныеСвязанныеДокументы = КритерииОтбора.СвязанныеДокументы.Найти(ДокументСсылка);

Запросом:

ВЫБРАТЬ
    СвязанныеДокументы.Ссылка КАК Ссылка
ИЗ
    КритерийОтбора.СвязанныеДокументы(&ДокументСсылка) КАК СвязанныеДокументы

ВЫБРАТЬ

СвязанныеДокументы.Ссылка КАК Ссылка

ИЗ

КритерийОтбора.СвязанныеДокументы(&ДокументСсылка) КАК СвязанныеДокументы

На примере 1С: УНФ, с виду простой простой запрос, всё равно преобразуется в длиннющий SQL запрос к базе данных.

SELECT
T1.IDTRef,
T1.IDRRef
FROM (SELECT
0x0000009A AS IDTRef,
T2._IDRRef AS IDRRef
FROM dbo._Document154 T2
WHERE ((T2._Fld405 = @P1)) AND (T2._Fld3101RRef = @P2)
UNION SELECT
0x0000009A AS IDTRef,
T3._IDRRef AS IDRRef
FROM dbo._Document154 T3
WHERE ((T3._Fld405 = @P3)) AND EXISTS(
SELECT 1 
FROM dbo._Document154_VT3119 T4
WHERE T3._Fld405 = T4._Fld405 AND T3._IDRRef = T4._Document154_IDRRef AND ((T4._Fld3131RRef = @P4)))
UNION ALL SELECT
0x00007F84 AS IDTRef,
T5._IDRRef AS IDRRef
FROM dbo._Document32644 T5
WHERE ((T5._Fld405 = @P5)) AND (T5._Fld32692_TYPE = 0x08 AND T5._Fld32692_RTRef = 0x000000A4 AND T5._Fld32692_RRRef = @P6)
UNION ALL SELECT
0x00007F85 AS IDTRef,
T6._IDRRef AS IDRRef
FROM dbo._Document32645 T6
WHERE ((T6._Fld405 = @P7)) AND (T6._Fld32842_TYPE = 0x08 AND T6._Fld32842_RTRef = 0x000000A4 AND T6._Fld32842_RRRef = @P8)
UNION SELECT
0x00007F85 AS IDTRef,
T7._IDRRef AS IDRRef
FROM dbo._Document32645 T7
WHERE ((T7._Fld405 = @P9)) AND (T7._Fld32844RRef = @P10)
UNION SELECT
0x00007F85 AS IDTRef,
T8._IDRRef AS IDRRef
FROM dbo._Document32645 T8
WHERE ((T8._Fld405 = @P11)) AND (T8._Fld32846_TYPE = 0x08 AND T8._Fld32846_RTRef = 0x000000A4 AND T8._Fld32846_RRRef = @P12)
UNION ALL SELECT
0x000000A2 AS IDTRef,
T9._IDRRef AS IDRRef
FROM dbo._Document162 T9
WHERE ((T9._Fld405 = @P13)) AND EXISTS(
SELECT 1 
FROM dbo._Document162_VT3520 T10
WHERE T9._Fld405 = T10._Fld405 AND T9._IDRRef = T10._Document162_IDRRef AND ((T10._Fld3523_TYPE = 0x08 AND T10._Fld3523_RTRef = 0x000000A4 AND T10._Fld3523_RRRef = @P14)))
UNION ALL SELECT
0x00007CF7 AS IDTRef,
T11._IDRRef AS IDRRef
FROM dbo._Document31991 T11
WHERE ((T11._Fld405 = @P15)) AND (T11._Fld32007_TYPE = 0x08 AND T11._Fld32007_RTRef = 0x000000A4 AND T11._Fld32007_RRRef = @P16)
UNION SELECT
0x00007CF7 AS IDTRef,
T12._IDRRef AS IDRRef
FROM dbo._Document31991 T12
WHERE ((T12._Fld405 = @P17)) AND (T12._Fld32008RRef = @P18)
UNION SELECT
0x00007CF7 AS IDTRef,
T13._IDRRef AS IDRRef
FROM dbo._Document31991 T13
WHERE ((T13._Fld405 = @P19)) AND EXISTS(
SELECT 1 
FROM dbo._Document31991_VT32021 T14
WHERE T13._Fld405 = T14._Fld405 AND T13._IDRRef = T14._Document31991_IDRRef AND ((T14._Fld32029RRef = @P20)))
UNION ALL SELECT
0x000000A3 AS IDTRef,
T15._IDRRef AS IDRRef
FROM dbo._Document163 T15
WHERE ((T15._Fld405 = @P21)) AND (T15._Fld13438_TYPE = 0x08 AND T15._Fld13438_RTRef = 0x000000A4 AND T15._Fld13438_RRRef = @P22)
UNION SELECT
0x000000A3 AS IDTRef,
T16._IDRRef AS IDRRef
FROM dbo._Document163 T16
WHERE ((T16._Fld405 = @P23)) AND (T16._Fld3543RRef = @P24)
UNION SELECT
0x000000A3 AS IDTRef,
T17._IDRRef AS IDRRef
FROM dbo._Document163 T17
WHERE ((T17._Fld405 = @P25)) AND EXISTS(
SELECT 1 
FROM dbo._Document163_VT3562 T18
WHERE T17._Fld405 = T18._Fld405 AND T17._IDRRef = T18._Document163_IDRRef AND ((T18._Fld21905RRef = @P26)))
UNION ALL SELECT
0x000000A5 AS IDTRef,
T19._IDRRef AS IDRRef
FROM dbo._Document165 T19
WHERE ((T19._Fld405 = @P27)) AND (T19._Fld3757RRef = @P28)
UNION SELECT
0x000000A5 AS IDTRef,
T20._IDRRef AS IDRRef
FROM dbo._Document165 T20
WHERE ((T20._Fld405 = @P29)) AND EXISTS(
SELECT 1 
FROM dbo._Document165_VT3774 T21
WHERE T20._Fld405 = T21._Fld405 AND T20._IDRRef = T21._Document165_IDRRef AND ((T21._Fld25776RRef = @P30)))
UNION ALL SELECT
0x00002438 AS IDTRef,
T22._IDRRef AS IDRRef
FROM dbo._Document9272 T22
WHERE ((T22._Fld405 = @P31)) AND (T22._Fld11239_TYPE = 0x08 AND T22._Fld11239_RTRef = 0x000000A4 AND T22._Fld11239_RRRef = @P32)
UNION SELECT
0x00002438 AS IDTRef,
T23._IDRRef AS IDRRef
FROM dbo._Document9272 T23
WHERE ((T23._Fld405 = @P33)) AND (T23._Fld11240RRef = @P34)
UNION ALL SELECT
0x00002142 AS IDTRef,
T24._IDRRef AS IDRRef
FROM dbo._Document8514 T24
WHERE ((T24._Fld405 = @P35)) AND (T24._Fld8735_TYPE = 0x08 AND T24._Fld8735_RTRef = 0x000000A4 AND T24._Fld8735_RRRef = @P36)
UNION SELECT
0x00002142 AS IDTRef,
T25._IDRRef AS IDRRef
FROM dbo._Document8514 T25
WHERE ((T25._Fld405 = @P37)) AND EXISTS(
SELECT 1 
FROM dbo._Document8514_VT8756 T26
WHERE T25._Fld405 = T26._Fld405 AND T25._IDRRef = T26._Document8514_IDRRef AND ((T26._Fld8760_TYPE = 0x08 AND T26._Fld8760_RTRef = 0x000000A4 AND T26._Fld8760_RRRef = @P38)))
UNION ALL SELECT
0x000000B2 AS IDTRef,
T27._IDRRef AS IDRRef
FROM dbo._Document178 T27
WHERE ((T27._Fld405 = @P39)) AND (T27._Fld4125RRef = @P40)
UNION ALL SELECT
0x000000B4 AS IDTRef,
T28._IDRRef AS IDRRef
FROM dbo._Document180 T28
WHERE ((T28._Fld405 = @P41)) AND (T28._Fld4261RRef = @P42)
UNION ALL SELECT
0x000000B9 AS IDTRef,
T29._IDRRef AS IDRRef
FROM dbo._Document185 T29
WHERE ((T29._Fld405 = @P43)) AND (T29._Fld4411_TYPE = 0x08 AND T29._Fld4411_RTRef = 0x000000A4 AND T29._Fld4411_RRRef = @P44)
UNION ALL SELECT
0x000000BF AS IDTRef,
T30._IDRRef AS IDRRef
FROM dbo._Document191 T30
WHERE ((T30._Fld405 = @P45)) AND (T30._Fld4572_TYPE = 0x08 AND T30._Fld4572_RTRef = 0x000000A4 AND T30._Fld4572_RRRef = @P46)
UNION SELECT
0x000000BF AS IDTRef,
T31._IDRRef AS IDRRef
FROM dbo._Document191 T31
WHERE ((T31._Fld405 = @P47)) AND EXISTS(
SELECT 1 
FROM dbo._Document191_VT4583 T32
WHERE T31._Fld405 = T32._Fld405 AND T31._IDRRef = T32._Document191_IDRRef AND ((T32._Fld4587_TYPE = 0x08 AND T32._Fld4587_RTRef = 0x000000A4 AND T32._Fld4587_RRRef = @P48)))
UNION ALL SELECT
0x000000C0 AS IDTRef,
T33._IDRRef AS IDRRef
FROM dbo._Document192 T33
WHERE ((T33._Fld405 = @P49)) AND (T33._Fld4617_TYPE = 0x08 AND T33._Fld4617_RTRef = 0x000000A4 AND T33._Fld4617_RRRef = @P50)
UNION ALL SELECT
0x000000C1 AS IDTRef,
T34._IDRRef AS IDRRef
FROM dbo._Document193 T34
WHERE ((T34._Fld405 = @P51)) AND (T34._Fld4628_TYPE = 0x08 AND T34._Fld4628_RTRef = 0x000000A4 AND T34._Fld4628_RRRef = @P52)
UNION SELECT
0x000000C1 AS IDTRef,
T35._IDRRef AS IDRRef
FROM dbo._Document193 T35
WHERE ((T35._Fld405 = @P53)) AND EXISTS(
SELECT 1 
FROM dbo._Document193_VT4646 T36
WHERE T35._Fld405 = T36._Fld405 AND T35._IDRRef = T36._Document193_IDRRef AND ((T36._Fld4650_TYPE = 0x08 AND T36._Fld4650_RTRef = 0x000000A4 AND T36._Fld4650_RRRef = @P54)))
UNION ALL SELECT
0x000000C4 AS IDTRef,
T37._IDRRef AS IDRRef
FROM dbo._Document196 T37
WHERE ((T37._Fld405 = @P55)) AND (T37._Fld4722_TYPE = 0x08 AND T37._Fld4722_RTRef = 0x000000A4 AND T37._Fld4722_RRRef = @P56)
UNION ALL SELECT
0x000000CA AS IDTRef,
T38._IDRRef AS IDRRef
FROM dbo._Document202 T38
WHERE ((T38._Fld405 = @P57)) AND EXISTS(
SELECT 1 
FROM dbo._Document202_VT4939 T39
WHERE T38._Fld405 = T39._Fld405 AND T38._IDRRef = T39._Document202_IDRRef AND ((T39._Fld4943_TYPE = 0x08 AND T39._Fld4943_RTRef = 0x000000A4 AND T39._Fld4943_RRRef = @P58)))
UNION ALL SELECT
0x000000CB AS IDTRef,
T40._IDRRef AS IDRRef
FROM dbo._Document203 T40
WHERE ((T40._Fld405 = @P59)) AND (T40._Fld4994_TYPE = 0x08 AND T40._Fld4994_RTRef = 0x000000A4 AND T40._Fld4994_RRRef = @P60)
UNION SELECT
0x000000CB AS IDTRef,
T41._IDRRef AS IDRRef
FROM dbo._Document203 T41
WHERE ((T41._Fld405 = @P61)) AND EXISTS(
SELECT 1 
FROM dbo._Document203_VT5021 T42
WHERE T41._Fld405 = T42._Fld405 AND T41._IDRRef = T42._Document203_IDRRef AND ((T42._Fld5036_TYPE = 0x08 AND T42._Fld5036_RTRef = 0x000000A4 AND T42._Fld5036_RRRef = @P62)))
UNION ALL SELECT
0x000000CC AS IDTRef,
T43._IDRRef AS IDRRef
FROM dbo._Document204 T43
WHERE ((T43._Fld405 = @P63)) AND (T43._Fld5071_TYPE = 0x08 AND T43._Fld5071_RTRef = 0x000000A4 AND T43._Fld5071_RRRef = @P64)
UNION ALL SELECT
0x000000CD AS IDTRef,
T44._IDRRef AS IDRRef
FROM dbo._Document205 T44
WHERE ((T44._Fld405 = @P65)) AND EXISTS(
SELECT 1 
FROM dbo._Document205_VT5104 T45
WHERE T44._Fld405 = T45._Fld405 AND T44._IDRRef = T45._Document205_IDRRef AND ((T45._Fld5108_TYPE = 0x08 AND T45._Fld5108_RTRef = 0x000000A4 AND T45._Fld5108_RRRef = @P66)))
UNION ALL SELECT
0x000000CE AS IDTRef,
T46._IDRRef AS IDRRef
FROM dbo._Document206 T46
WHERE ((T46._Fld405 = @P67)) AND (T46._Fld5139RRef = @P68)
UNION SELECT
0x000000CE AS IDTRef,
T47._IDRRef AS IDRRef
FROM dbo._Document206 T47
WHERE ((T47._Fld405 = @P69)) AND EXISTS(
SELECT 1 
FROM dbo._Document206_VT5142 T48
WHERE T47._Fld405 = T48._Fld405 AND T47._IDRRef = T48._Document206_IDRRef AND ((T48._Fld5149_TYPE = 0x08 AND T48._Fld5149_RTRef = 0x000000A4 AND T48._Fld5149_RRRef = @P70)))
UNION SELECT
0x000000CE AS IDTRef,
T49._IDRRef AS IDRRef
FROM dbo._Document206 T49
WHERE ((T49._Fld405 = @P71)) AND EXISTS(
SELECT 1 
FROM dbo._Document206_VT5142 T50
WHERE T49._Fld405 = T50._Fld405 AND T49._IDRRef = T50._Document206_IDRRef AND ((T50._Fld5150_TYPE = 0x08 AND T50._Fld5150_RTRef = 0x000000A4 AND T50._Fld5150_RRRef = @P72)))
UNION ALL SELECT
0x000000CF AS IDTRef,
T51._IDRRef AS IDRRef
FROM dbo._Document207 T51
WHERE ((T51._Fld405 = @P73)) AND (T51._Fld13552_TYPE = 0x08 AND T51._Fld13552_RTRef = 0x000000A4 AND T51._Fld13552_RRRef = @P74)
UNION SELECT
0x000000CF AS IDTRef,
T52._IDRRef AS IDRRef
FROM dbo._Document207 T52
WHERE ((T52._Fld405 = @P75)) AND (T52._Fld5158RRef = @P76)
UNION SELECT
0x000000CF AS IDTRef,
T53._IDRRef AS IDRRef
FROM dbo._Document207 T53
WHERE ((T53._Fld405 = @P77)) AND EXISTS(
SELECT 1 
FROM dbo._Document207_VT5169 T54
WHERE T53._Fld405 = T54._Fld405 AND T53._IDRRef = T54._Document207_IDRRef AND ((T54._Fld21956RRef = @P78)))
UNION ALL SELECT
0x000000D1 AS IDTRef,
T55._IDRRef AS IDRRef
FROM dbo._Document209 T55
WHERE ((T55._Fld405 = @P79)) AND (T55._Fld17535_TYPE = 0x08 AND T55._Fld17535_RTRef = 0x000000A4 AND T55._Fld17535_RRRef = @P80)
UNION SELECT
0x000000D1 AS IDTRef,
T56._IDRRef AS IDRRef
FROM dbo._Document209 T56
WHERE ((T56._Fld405 = @P81)) AND EXISTS(
SELECT 1 
FROM dbo._Document209_VT5262 T57
WHERE T56._Fld405 = T57._Fld405 AND T56._IDRRef = T57._Document209_IDRRef AND ((T57._Fld5265RRef = @P82)))
UNION ALL SELECT
0x000000D2 AS IDTRef,
T58._IDRRef AS IDRRef
FROM dbo._Document210 T58
WHERE ((T58._Fld405 = @P83)) AND (T58._Fld5289_TYPE = 0x08 AND T58._Fld5289_RTRef = 0x000000A4 AND T58._Fld5289_RRRef = @P84)
UNION SELECT
0x000000D2 AS IDTRef,
T59._IDRRef AS IDRRef
FROM dbo._Document210 T59
WHERE ((T59._Fld405 = @P85)) AND EXISTS(
SELECT 1 
FROM dbo._Document210_VT5299 T60
WHERE T59._Fld405 = T60._Fld405 AND T59._IDRRef = T60._Document210_IDRRef AND ((T60._Fld5301_TYPE = 0x08 AND T60._Fld5301_RTRef = 0x000000A4 AND T60._Fld5301_RRRef = @P86)))
UNION ALL SELECT
0x000000D5 AS IDTRef,
T61._IDRRef AS IDRRef
FROM dbo._Document213 T61
WHERE ((T61._Fld405 = @P87)) AND (T61._Fld5348_TYPE = 0x08 AND T61._Fld5348_RTRef = 0x000000A4 AND T61._Fld5348_RRRef = @P88)
UNION ALL SELECT
0x000000D7 AS IDTRef,
T62._IDRRef AS IDRRef
FROM dbo._Document215 T62
WHERE ((T62._Fld405 = @P89)) AND (T62._Fld5466_TYPE = 0x08 AND T62._Fld5466_RTRef = 0x000000A4 AND T62._Fld5466_RRRef = @P90)
UNION SELECT
0x000000D7 AS IDTRef,
T63._IDRRef AS IDRRef
FROM dbo._Document215 T63
WHERE ((T63._Fld405 = @P91)) AND EXISTS(
SELECT 1 
FROM dbo._Document215_VT5497 T64
WHERE T63._Fld405 = T64._Fld405 AND T63._IDRRef = T64._Document215_IDRRef AND ((T64._Fld5499_TYPE = 0x08 AND T64._Fld5499_RTRef = 0x000000A4 AND T64._Fld5499_RRRef = @P92)))
UNION ALL SELECT
0x000000DC AS IDTRef,
T65._IDRRef AS IDRRef
FROM dbo._Document220 T65
WHERE ((T65._Fld405 = @P93)) AND (T65._Fld14110RRef = @P94)
UNION SELECT
0x000000DC AS IDTRef,
T66._IDRRef AS IDRRef
FROM dbo._Document220 T66
WHERE ((T66._Fld405 = @P95)) AND EXISTS(
SELECT 1 
FROM dbo._Document220_VT5841 T67
WHERE T66._Fld405 = T67._Fld405 AND T66._IDRRef = T67._Document220_IDRRef AND ((T67._Fld14112RRef = @P96)))
UNION ALL SELECT
0x000000DD AS IDTRef,
T68._IDRRef AS IDRRef
FROM dbo._Document221 T68
WHERE ((T68._Fld405 = @P97)) AND (T68._Fld14120RRef = @P98)
UNION SELECT
0x000000DD AS IDTRef,
T69._IDRRef AS IDRRef
FROM dbo._Document221 T69
WHERE ((T69._Fld405 = @P99)) AND EXISTS(
SELECT 1 
FROM dbo._Document221_VT5892 T70
WHERE T69._Fld405 = T70._Fld405 AND T69._IDRRef = T70._Document221_IDRRef AND ((T70._Fld14122RRef = @P100)))) T1

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

SELECT

T1.IDTRef,

T1.IDRRef

FROM (SELECT

0x0000009A AS IDTRef,

T2._IDRRef AS IDRRef

FROM dbo._Document154 T2

WHERE ((T2._Fld405 = @P1)) AND (T2._Fld3101RRef = @P2)

UNION SELECT

0x0000009A AS IDTRef,

T3._IDRRef AS IDRRef

FROM dbo._Document154 T3

WHERE ((T3._Fld405 = @P3)) AND EXISTS(

SELECT 1

FROM dbo._Document154_VT3119 T4

WHERE T3._Fld405 = T4._Fld405 AND T3._IDRRef = T4._Document154_IDRRef AND ((T4._Fld3131RRef = @P4)))

UNION ALL SELECT

0x00007F84 AS IDTRef,

T5._IDRRef AS IDRRef

FROM dbo._Document32644 T5

WHERE ((T5._Fld405 = @P5)) AND (T5._Fld32692_TYPE = 0x08 AND T5._Fld32692_RTRef = 0x000000A4 AND T5._Fld32692_RRRef = @P6)

UNION ALL SELECT

0x00007F85 AS IDTRef,

T6._IDRRef AS IDRRef

FROM dbo._Document32645 T6

WHERE ((T6._Fld405 = @P7)) AND (T6._Fld32842_TYPE = 0x08 AND T6._Fld32842_RTRef = 0x000000A4 AND T6._Fld32842_RRRef = @P8)

UNION SELECT

0x00007F85 AS IDTRef,

T7._IDRRef AS IDRRef

FROM dbo._Document32645 T7

WHERE ((T7._Fld405 = @P9)) AND (T7._Fld32844RRef = @P10)

UNION SELECT

0x00007F85 AS IDTRef,

T8._IDRRef AS IDRRef

FROM dbo._Document32645 T8

WHERE ((T8._Fld405 = @P11)) AND (T8._Fld32846_TYPE = 0x08 AND T8._Fld32846_RTRef = 0x000000A4 AND T8._Fld32846_RRRef = @P12)

UNION ALL SELECT

0x000000A2 AS IDTRef,

T9._IDRRef AS IDRRef

FROM dbo._Document162 T9

WHERE ((T9._Fld405 = @P13)) AND EXISTS(

SELECT 1

FROM dbo._Document162_VT3520 T10

WHERE T9._Fld405 = T10._Fld405 AND T9._IDRRef = T10._Document162_IDRRef AND ((T10._Fld3523_TYPE = 0x08 AND T10._Fld3523_RTRef = 0x000000A4 AND T10._Fld3523_RRRef = @P14)))

UNION ALL SELECT

0x00007CF7 AS IDTRef,

T11._IDRRef AS IDRRef

FROM dbo._Document31991 T11

WHERE ((T11._Fld405 = @P15)) AND (T11._Fld32007_TYPE = 0x08 AND T11._Fld32007_RTRef = 0x000000A4 AND T11._Fld32007_RRRef = @P16)

UNION SELECT

0x00007CF7 AS IDTRef,

T12._IDRRef AS IDRRef

FROM dbo._Document31991 T12

WHERE ((T12._Fld405 = @P17)) AND (T12._Fld32008RRef = @P18)

UNION SELECT

0x00007CF7 AS IDTRef,

T13._IDRRef AS IDRRef

FROM dbo._Document31991 T13

WHERE ((T13._Fld405 = @P19)) AND EXISTS(

SELECT 1

FROM dbo._Document31991_VT32021 T14

WHERE T13._Fld405 = T14._Fld405 AND T13._IDRRef = T14._Document31991_IDRRef AND ((T14._Fld32029RRef = @P20)))

UNION ALL SELECT

0x000000A3 AS IDTRef,

T15._IDRRef AS IDRRef

FROM dbo._Document163 T15

WHERE ((T15._Fld405 = @P21)) AND (T15._Fld13438_TYPE = 0x08 AND T15._Fld13438_RTRef = 0x000000A4 AND T15._Fld13438_RRRef = @P22)

UNION SELECT

0x000000A3 AS IDTRef,

T16._IDRRef AS IDRRef

FROM dbo._Document163 T16

WHERE ((T16._Fld405 = @P23)) AND (T16._Fld3543RRef = @P24)

UNION SELECT

0x000000A3 AS IDTRef,

T17._IDRRef AS IDRRef

FROM dbo._Document163 T17

WHERE ((T17._Fld405 = @P25)) AND EXISTS(

SELECT 1

FROM dbo._Document163_VT3562 T18

WHERE T17._Fld405 = T18._Fld405 AND T17._IDRRef = T18._Document163_IDRRef AND ((T18._Fld21905RRef = @P26)))

UNION ALL SELECT

0x000000A5 AS IDTRef,

T19._IDRRef AS IDRRef

FROM dbo._Document165 T19

WHERE ((T19._Fld405 = @P27)) AND (T19._Fld3757RRef = @P28)

UNION SELECT

0x000000A5 AS IDTRef,

T20._IDRRef AS IDRRef

FROM dbo._Document165 T20

WHERE ((T20._Fld405 = @P29)) AND EXISTS(

SELECT 1

FROM dbo._Document165_VT3774 T21

WHERE T20._Fld405 = T21._Fld405 AND T20._IDRRef = T21._Document165_IDRRef AND ((T21._Fld25776RRef = @P30)))

UNION ALL SELECT

0x00002438 AS IDTRef,

T22._IDRRef AS IDRRef

FROM dbo._Document9272 T22

WHERE ((T22._Fld405 = @P31)) AND (T22._Fld11239_TYPE = 0x08 AND T22._Fld11239_RTRef = 0x000000A4 AND T22._Fld11239_RRRef = @P32)

UNION SELECT

0x00002438 AS IDTRef,

T23._IDRRef AS IDRRef

FROM dbo._Document9272 T23

WHERE ((T23._Fld405 = @P33)) AND (T23._Fld11240RRef = @P34)

UNION ALL SELECT

0x00002142 AS IDTRef,

T24._IDRRef AS IDRRef

FROM dbo._Document8514 T24

WHERE ((T24._Fld405 = @P35)) AND (T24._Fld8735_TYPE = 0x08 AND T24._Fld8735_RTRef = 0x000000A4 AND T24._Fld8735_RRRef = @P36)

UNION SELECT

0x00002142 AS IDTRef,

T25._IDRRef AS IDRRef

FROM dbo._Document8514 T25

WHERE ((T25._Fld405 = @P37)) AND EXISTS(

SELECT 1

FROM dbo._Document8514_VT8756 T26

WHERE T25._Fld405 = T26._Fld405 AND T25._IDRRef = T26._Document8514_IDRRef AND ((T26._Fld8760_TYPE = 0x08 AND T26._Fld8760_RTRef = 0x000000A4 AND T26._Fld8760_RRRef = @P38)))

UNION ALL SELECT

0x000000B2 AS IDTRef,

T27._IDRRef AS IDRRef

FROM dbo._Document178 T27

WHERE ((T27._Fld405 = @P39)) AND (T27._Fld4125RRef = @P40)

UNION ALL SELECT

0x000000B4 AS IDTRef,

T28._IDRRef AS IDRRef

FROM dbo._Document180 T28

WHERE ((T28._Fld405 = @P41)) AND (T28._Fld4261RRef = @P42)

UNION ALL SELECT

0x000000B9 AS IDTRef,

T29._IDRRef AS IDRRef

FROM dbo._Document185 T29

WHERE ((T29._Fld405 = @P43)) AND (T29._Fld4411_TYPE = 0x08 AND T29._Fld4411_RTRef = 0x000000A4 AND T29._Fld4411_RRRef = @P44)

UNION ALL SELECT

0x000000BF AS IDTRef,

T30._IDRRef AS IDRRef

FROM dbo._Document191 T30

WHERE ((T30._Fld405 = @P45)) AND (T30._Fld4572_TYPE = 0x08 AND T30._Fld4572_RTRef = 0x000000A4 AND T30._Fld4572_RRRef = @P46)

UNION SELECT

0x000000BF AS IDTRef,

T31._IDRRef AS IDRRef

FROM dbo._Document191 T31

WHERE ((T31._Fld405 = @P47)) AND EXISTS(

SELECT 1

FROM dbo._Document191_VT4583 T32

WHERE T31._Fld405 = T32._Fld405 AND T31._IDRRef = T32._Document191_IDRRef AND ((T32._Fld4587_TYPE = 0x08 AND T32._Fld4587_RTRef = 0x000000A4 AND T32._Fld4587_RRRef = @P48)))

UNION ALL SELECT

0x000000C0 AS IDTRef,

T33._IDRRef AS IDRRef

FROM dbo._Document192 T33

WHERE ((T33._Fld405 = @P49)) AND (T33._Fld4617_TYPE = 0x08 AND T33._Fld4617_RTRef = 0x000000A4 AND T33._Fld4617_RRRef = @P50)

UNION ALL SELECT

0x000000C1 AS IDTRef,

T34._IDRRef AS IDRRef

FROM dbo._Document193 T34

WHERE ((T34._Fld405 = @P51)) AND (T34._Fld4628_TYPE = 0x08 AND T34._Fld4628_RTRef = 0x000000A4 AND T34._Fld4628_RRRef = @P52)

UNION SELECT

0x000000C1 AS IDTRef,

T35._IDRRef AS IDRRef

FROM dbo._Document193 T35

WHERE ((T35._Fld405 = @P53)) AND EXISTS(

SELECT 1

FROM dbo._Document193_VT4646 T36

WHERE T35._Fld405 = T36._Fld405 AND T35._IDRRef = T36._Document193_IDRRef AND ((T36._Fld4650_TYPE = 0x08 AND T36._Fld4650_RTRef = 0x000000A4 AND T36._Fld4650_RRRef = @P54)))

UNION ALL SELECT

0x000000C4 AS IDTRef,

T37._IDRRef AS IDRRef

FROM dbo._Document196 T37

WHERE ((T37._Fld405 = @P55)) AND (T37._Fld4722_TYPE = 0x08 AND T37._Fld4722_RTRef = 0x000000A4 AND T37._Fld4722_RRRef = @P56)

UNION ALL SELECT

0x000000CA AS IDTRef,

T38._IDRRef AS IDRRef

FROM dbo._Document202 T38

WHERE ((T38._Fld405 = @P57)) AND EXISTS(

SELECT 1

FROM dbo._Document202_VT4939 T39

WHERE T38._Fld405 = T39._Fld405 AND T38._IDRRef = T39._Document202_IDRRef AND ((T39._Fld4943_TYPE = 0x08 AND T39._Fld4943_RTRef = 0x000000A4 AND T39._Fld4943_RRRef = @P58)))

UNION ALL SELECT

0x000000CB AS IDTRef,

T40._IDRRef AS IDRRef

FROM dbo._Document203 T40

WHERE ((T40._Fld405 = @P59)) AND (T40._Fld4994_TYPE = 0x08 AND T40._Fld4994_RTRef = 0x000000A4 AND T40._Fld4994_RRRef = @P60)

UNION SELECT

0x000000CB AS IDTRef,

T41._IDRRef AS IDRRef

FROM dbo._Document203 T41

WHERE ((T41._Fld405 = @P61)) AND EXISTS(

SELECT 1

FROM dbo._Document203_VT5021 T42

WHERE T41._Fld405 = T42._Fld405 AND T41._IDRRef = T42._Document203_IDRRef AND ((T42._Fld5036_TYPE = 0x08 AND T42._Fld5036_RTRef = 0x000000A4 AND T42._Fld5036_RRRef = @P62)))

UNION ALL SELECT

0x000000CC AS IDTRef,

T43._IDRRef AS IDRRef

FROM dbo._Document204 T43

WHERE ((T43._Fld405 = @P63)) AND (T43._Fld5071_TYPE = 0x08 AND T43._Fld5071_RTRef = 0x000000A4 AND T43._Fld5071_RRRef = @P64)

UNION ALL SELECT

0x000000CD AS IDTRef,

T44._IDRRef AS IDRRef

FROM dbo._Document205 T44

WHERE ((T44._Fld405 = @P65)) AND EXISTS(

SELECT 1

FROM dbo._Document205_VT5104 T45

WHERE T44._Fld405 = T45._Fld405 AND T44._IDRRef = T45._Document205_IDRRef AND ((T45._Fld5108_TYPE = 0x08 AND T45._Fld5108_RTRef = 0x000000A4 AND T45._Fld5108_RRRef = @P66)))

UNION ALL SELECT

0x000000CE AS IDTRef,

T46._IDRRef AS IDRRef

FROM dbo._Document206 T46

WHERE ((T46._Fld405 = @P67)) AND (T46._Fld5139RRef = @P68)

UNION SELECT

0x000000CE AS IDTRef,

T47._IDRRef AS IDRRef

FROM dbo._Document206 T47

WHERE ((T47._Fld405 = @P69)) AND EXISTS(

SELECT 1

FROM dbo._Document206_VT5142 T48

WHERE T47._Fld405 = T48._Fld405 AND T47._IDRRef = T48._Document206_IDRRef AND ((T48._Fld5149_TYPE = 0x08 AND T48._Fld5149_RTRef = 0x000000A4 AND T48._Fld5149_RRRef = @P70)))

UNION SELECT

0x000000CE AS IDTRef,

T49._IDRRef AS IDRRef

FROM dbo._Document206 T49

WHERE ((T49._Fld405 = @P71)) AND EXISTS(

SELECT 1

FROM dbo._Document206_VT5142 T50

WHERE T49._Fld405 = T50._Fld405 AND T49._IDRRef = T50._Document206_IDRRef AND ((T50._Fld5150_TYPE = 0x08 AND T50._Fld5150_RTRef = 0x000000A4 AND T50._Fld5150_RRRef = @P72)))

UNION ALL SELECT

0x000000CF AS IDTRef,

T51._IDRRef AS IDRRef

FROM dbo._Document207 T51

WHERE ((T51._Fld405 = @P73)) AND (T51._Fld13552_TYPE = 0x08 AND T51._Fld13552_RTRef = 0x000000A4 AND T51._Fld13552_RRRef = @P74)

UNION SELECT

0x000000CF AS IDTRef,

T52._IDRRef AS IDRRef

FROM dbo._Document207 T52

WHERE ((T52._Fld405 = @P75)) AND (T52._Fld5158RRef = @P76)

UNION SELECT

0x000000CF AS IDTRef,

T53._IDRRef AS IDRRef

FROM dbo._Document207 T53

WHERE ((T53._Fld405 = @P77)) AND EXISTS(

SELECT 1

FROM dbo._Document207_VT5169 T54

WHERE T53._Fld405 = T54._Fld405 AND T53._IDRRef = T54._Document207_IDRRef AND ((T54._Fld21956RRef = @P78)))

UNION ALL SELECT

0x000000D1 AS IDTRef,

T55._IDRRef AS IDRRef

FROM dbo._Document209 T55

WHERE ((T55._Fld405 = @P79)) AND (T55._Fld17535_TYPE = 0x08 AND T55._Fld17535_RTRef = 0x000000A4 AND T55._Fld17535_RRRef = @P80)

UNION SELECT

0x000000D1 AS IDTRef,

T56._IDRRef AS IDRRef

FROM dbo._Document209 T56

WHERE ((T56._Fld405 = @P81)) AND EXISTS(

SELECT 1

FROM dbo._Document209_VT5262 T57

WHERE T56._Fld405 = T57._Fld405 AND T56._IDRRef = T57._Document209_IDRRef AND ((T57._Fld5265RRef = @P82)))

UNION ALL SELECT

0x000000D2 AS IDTRef,

T58._IDRRef AS IDRRef

FROM dbo._Document210 T58

WHERE ((T58._Fld405 = @P83)) AND (T58._Fld5289_TYPE = 0x08 AND T58._Fld5289_RTRef = 0x000000A4 AND T58._Fld5289_RRRef = @P84)

UNION SELECT

0x000000D2 AS IDTRef,

T59._IDRRef AS IDRRef

FROM dbo._Document210 T59

WHERE ((T59._Fld405 = @P85)) AND EXISTS(

SELECT 1

FROM dbo._Document210_VT5299 T60

WHERE T59._Fld405 = T60._Fld405 AND T59._IDRRef = T60._Document210_IDRRef AND ((T60._Fld5301_TYPE = 0x08 AND T60._Fld5301_RTRef = 0x000000A4 AND T60._Fld5301_RRRef = @P86)))

UNION ALL SELECT

0x000000D5 AS IDTRef,

T61._IDRRef AS IDRRef

FROM dbo._Document213 T61

WHERE ((T61._Fld405 = @P87)) AND (T61._Fld5348_TYPE = 0x08 AND T61._Fld5348_RTRef = 0x000000A4 AND T61._Fld5348_RRRef = @P88)

UNION ALL SELECT

0x000000D7 AS IDTRef,

T62._IDRRef AS IDRRef

FROM dbo._Document215 T62

WHERE ((T62._Fld405 = @P89)) AND (T62._Fld5466_TYPE = 0x08 AND T62._Fld5466_RTRef = 0x000000A4 AND T62._Fld5466_RRRef = @P90)

UNION SELECT

0x000000D7 AS IDTRef,

T63._IDRRef AS IDRRef

FROM dbo._Document215 T63

WHERE ((T63._Fld405 = @P91)) AND EXISTS(

SELECT 1

FROM dbo._Document215_VT5497 T64

WHERE T63._Fld405 = T64._Fld405 AND T63._IDRRef = T64._Document215_IDRRef AND ((T64._Fld5499_TYPE = 0x08 AND T64._Fld5499_RTRef = 0x000000A4 AND T64._Fld5499_RRRef = @P92)))

UNION ALL SELECT

0x000000DC AS IDTRef,

T65._IDRRef AS IDRRef

FROM dbo._Document220 T65

WHERE ((T65._Fld405 = @P93)) AND (T65._Fld14110RRef = @P94)

UNION SELECT

0x000000DC AS IDTRef,

T66._IDRRef AS IDRRef

FROM dbo._Document220 T66

WHERE ((T66._Fld405 = @P95)) AND EXISTS(

SELECT 1

FROM dbo._Document220_VT5841 T67

WHERE T66._Fld405 = T67._Fld405 AND T66._IDRRef = T67._Document220_IDRRef AND ((T67._Fld14112RRef = @P96)))

UNION ALL SELECT

0x000000DD AS IDTRef,

T68._IDRRef AS IDRRef

FROM dbo._Document221 T68

WHERE ((T68._Fld405 = @P97)) AND (T68._Fld14120RRef = @P98)

UNION SELECT

0x000000DD AS IDTRef,

T69._IDRRef AS IDRRef

FROM dbo._Document221 T69

WHERE ((T69._Fld405 = @P99)) AND EXISTS(

SELECT 1

FROM dbo._Document221_VT5892 T70

WHERE T69._Fld405 = T70._Fld405 AND T69._IDRRef = T70._Document221_IDRRef AND ((T70._Fld14122RRef = @P100)))) T1

На уровне СУБД запрос становится таким длинным, так как он объединяется по всем возможным источникам связи указанным в конфигураторе в нашем критерии отбора. Хотя сам по себе запрос выполнится оптимально, так как оптимизатор на уровне СУБД более менее гарантированно будет искать данные по индексам, а не простым перебором.

Но давайте представим, что у нас есть задача найти все расходные накладные к которым привязан наш заказ покупателя. На первый взгляд лучшим решением будет написать запрос на подобии такого:

ВЫБРАТЬ
    РасходнаяНакладная.Ссылка КАК Ссылка
ИЗ
    Документ.РасходнаяНакладная КАК РасходнаяНакладная
ГДЕ
    РасходнаяНакладная.Заказ = &ЗаказПокупаетеля

ВЫБРАТЬ

РасходнаяНакладная.Ссылка КАК Ссылка

ИЗ

Документ.РасходнаяНакладная КАК РасходнаяНакладная

ГДЕ

РасходнаяНакладная.Заказ = &ЗаказПокупаетеля

На уровне СУБД он будет выглядеть так:

SELECT
T1._IDRRef
FROM dbo._Document203 T1
WHERE ((T1._Fld405 = @P1)) AND ((T1._Fld4994_TYPE = 0x08 AND T1._Fld4994_RTRef = 0x000000A4 AND T1._Fld4994_RRRef = @P2))

SELECT

T1._IDRRef

FROM dbo._Document203 T1

WHERE ((T1._Fld405 = @P1)) AND ((T1._Fld4994_TYPE = 0x08 AND T1._Fld4994_RTRef = 0x000000A4 AND T1._Fld4994_RRRef = @P2))

Выглядит компактнее. И в случае с УНФ он будет вполне оптимальным, потому что платформа создала индексы для реквизита Заказ, потому что он включен в состав критерия расчета “Связанные документы”. Хотя сам реквизит “Заказ” не является индексируемым.

Подробнее об индексах можно почитать на портале ИТС https://its.1c.ru/db/metod8dev/content/1590/hdoc

Это всё замечательно, однако пора вернуться к структуре подчиненности. Порой нужно рекурсивно получить не просто связанные с каким-то одним документом объекты, а подчиненные, подчиненные на подчиненные, подчиненные на подчиненные на подчиненные и далее по рекурсии объекты.

На Инфостарте у Максима Хмелева есть интересная, хоть и немного устаревшая статья. https://infostart.ru/1c/articles/205090/

Главный недостаток в этой статье – изменение поведения платформы начиная с версии 8.3.10 при вызове метода ПравоДоступа(). Но можно просто обернуть вызов права доступа в попытку и теперь мы можем рекурсивно получать родительские и подчиненные объекты.

Немного правим получившийся код (результат можно скачать чуть ниже по ссылке)

ПрограммноеПолучениеСтруктурыПодчиненности.zip Скачать