~ubuntu-branches/debian/jessie/sqlalchemy/jessie

<div class="highlight-python+sql"><div class="highlight"><pre>mapper(Employee, employees, polymorphic_on=employees.c.type, polymorphic_identity='employee')

205

mapper(Engineer, engineers, inherits=Employee, polymorphic_identity='engineer')

206

mapper(Manager, managers, inherits=Employee, polymorphic_identity='manager')</pre></div>

214

215

polymorphic_identity='employee')

216

217

polymorphic_identity='engineer')

218

219

polymorphic_identity='manager')</pre></div>

207

220

</div>

208

221

And that’s it. Querying against <tt class="docutils literal">Employee</tt> will return a combination of

209

222

<tt class="docutils literal">Employee</tt>, <tt class="docutils literal">Engineer</tt> and <tt class="docutils literal">Manager</tt> objects. Newly saved <tt class="docutils literal">Engineer</tt>,

221

234

database, our joined-table setup will query from the parent table only, using

222

235

SQL such as this:

223

236

224

SELECT employees.employee_id AS employees_employee_id, employees.name AS employees_name, employees.type AS employees_type

237

SELECT employees.employee_id AS employees_employee_id,

238

employees.name AS employees_name, employees.type AS employees_type

225

239

FROM employees

226

240

[]</div></pre></div>

227

241

</div>

231

245

already loaded. So above, after accessing the objects you’d see further SQL

232

246

issued along the lines of:

233

247

234

SELECT managers.employee_id AS managers_employee_id, managers.manager_data AS managers_manager_data

248

SELECT managers.employee_id AS managers_employee_id,

249

managers.manager_data AS managers_manager_data

235

250

FROM managers

236

251

WHERE ? = managers.employee_id

237

252

[5]

238

SELECT engineers.employee_id AS engineers_employee_id, engineers.engineer_info AS engineers_engineer_info

253

SELECT engineers.employee_id AS engineers_employee_id,

254

engineers.engineer_info AS engineers_engineer_info

239

255

FROM engineers

240

256

WHERE ? = engineers.employee_id

241

257

[2]</div></pre></div>

253

269

produces a query which joins the <tt class="docutils literal">employees</tt> table to both the <tt class="docutils literal">engineers</tt> and <tt class="docutils literal">managers</tt> tables like the following:

254

270

<div class="highlight-python+sql"><div class="highlight"><pre>query.all()

255

271

256

SELECT employees.employee_id AS employees_employee_id, engineers.employee_id AS engineers_employee_id, managers.employee_id AS managers_employee_id, employees.name AS employees_name, employees.type AS employees_type, engineers.engineer_info AS engineers_engineer_info, managers.manager_data AS managers_manager_data

257

FROM employees LEFT OUTER JOIN engineers ON employees.employee_id = engineers.employee_id LEFT OUTER JOIN managers ON employees.employee_id = managers.employee_id

272

SELECT employees.employee_id AS employees_employee_id,

273

engineers.employee_id AS engineers_employee_id,

274

managers.employee_id AS managers_employee_id,

275

employees.name AS employees_name,

276

employees.type AS employees_type,

277

engineers.engineer_info AS engineers_engineer_info,

278

managers.manager_data AS managers_manager_data

279

FROM employees

280

LEFT OUTER JOIN engineers

281

ON employees.employee_id = engineers.employee_id

282

LEFT OUTER JOIN managers

283

ON employees.employee_id = managers.employee_id

258

284

[]</div></pre></div>

259

285

</div>

260

286

<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.with_polymorphic" title="sqlalchemy.orm.query.Query.with_polymorphic"><tt class="xref py py-func docutils literal">with_polymorphic()</tt></a> accepts a single class or

275

301

be used with any kind of inheritance setup in the case that specialized SQL

276

302

should be used to load polymorphically:

277

303

<div class="highlight-python+sql"><div class="highlight"><pre># custom selectable

278

query.with_polymorphic([Engineer, Manager], employees.outerjoin(managers).outerjoin(engineers))</pre></div>

304

query.with_polymorphic(

305

[Engineer, Manager],

306

employees.outerjoin(managers).outerjoin(engineers)

307

)</pre></div>

279

308

</div>

280

309

281

310

when you wish to add filter criteria that are specific to one or more

290

319

that the joined-style load will be issued automatically. This argument may be

291

320

the string <tt class="docutils literal">'*'</tt>, a list of classes, or a tuple consisting of either,

292

321

followed by a selectable.

293

294

polymorphic_identity='employee', with_polymorphic='*')

295

296

322

323

polymorphic_identity='employee',

324

with_polymorphic='*')

325

326

polymorphic_identity='engineer')

327

328

polymorphic_identity='manager')</pre></div>

297

329

</div>

298

330

The above mapping will produce a query similar to that of

299

331

<tt class="docutils literal">with_polymorphic('*')</tt> for every query of <tt class="docutils literal">Employee</tt> objects.

323

355

<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.select_from" title="sqlalchemy.orm.query.Query.select_from"><tt class="xref py py-meth docutils literal">Query.select_from()</tt></a> methods:

324

356

325

357

326

session.query(engineer.c.id).filter(engineer.c.engineer_info==manager.c.manager_data)</pre></div>

358

359

filter(engineer.c.engineer_info==manager.c.manager_data)</pre></div>

327

360

</div>

328

361

</div>

329

362

330

363

<h3>Creating Joins to Specific Subtypes<a class="headerlink" href="#creating-joins-to-specific-subtypes" title="Permalink to this headline">¶</a></h3>

331

The <tt class="xref py py-func docutils literal">of_type()</tt> method is a

364

The <a class="reference internal" href="internals.html#sqlalchemy.orm.interfaces.PropComparator.of_type" title="sqlalchemy.orm.interfaces.PropComparator.of_type"><tt class="xref py py-func docutils literal">of_type()</tt></a> method is a

332

365

helper which allows the construction of joins along

333

366

<a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> paths while narrowing the criterion to

334

367

specific subclasses. Suppose the <tt class="docutils literal">employees</tt> table represents a collection

360

393

<tt class="docutils literal">managers</tt> in the mix. If we wish to have criterion which is specifically

361

394

against the <tt class="docutils literal">Engineer</tt> class, we can tell those methods to join or subquery

362

395

against the joined table representing the subclass using the

363

<tt class="xref py py-func docutils literal">of_type()</tt> operator:

364

<div class="highlight-python"><div class="highlight"><pre>session.query(Company).join(Company.employees.of_type(Engineer)).filter(Engineer.engineer_info=='someinfo')</pre></div>

396

<a class="reference internal" href="internals.html#sqlalchemy.orm.interfaces.PropComparator.of_type" title="sqlalchemy.orm.interfaces.PropComparator.of_type"><tt class="xref py py-func docutils literal">of_type()</tt></a> operator:

397

398

join(Company.employees.of_type(Engineer)).\

399

filter(Engineer.engineer_info=='someinfo')</pre></div>

365

400

</div>

366

401

A longhand version of this would involve spelling out the full target

367

402

selectable within a 2-tuple:

368

<div class="highlight-python"><div class="highlight"><pre>session.query(Company).join((employees.join(engineers), Company.employees)).filter(Engineer.engineer_info=='someinfo')</pre></div>

403

404

join((employees.join(engineers), Company.employees)).\

405

369

406

</div>

370

Currently, <tt class="xref py py-func docutils literal">of_type()</tt> accepts a

407

Currently, <a class="reference internal" href="internals.html#sqlalchemy.orm.interfaces.PropComparator.of_type" title="sqlalchemy.orm.interfaces.PropComparator.of_type"><tt class="xref py py-func docutils literal">of_type()</tt></a> accepts a

371

408

single class argument. It may be expanded later on to accept multiple classes.

372

409

For now, to join to any group of subclasses, the longhand notation allows this

373

410

flexibility:

374

<div class="highlight-python+sql"><div class="highlight"><pre>session.query(Company).join((employees.outerjoin(engineers).outerjoin(managers), Company.employees)).\

375

filter(or_(Engineer.engineer_info=='someinfo', Manager.manager_data=='somedata'))</pre></div>

411

412

join(

413

(employees.outerjoin(engineers).outerjoin(managers),

414

Company.employees)

415

).\

416

filter(

417

or_(Engineer.engineer_info=='someinfo',

418

Manager.manager_data=='somedata')

419

)</pre></div>

376

420

</div>

377

421

The <tt class="docutils literal">any()</tt> and <tt class="docutils literal">has()</tt> operators also can be used with

378

<tt class="xref py py-func docutils literal">of_type()</tt> when the embedded

422

379

423

criterion is in terms of a subclass:

380

<div class="highlight-python+sql"><div class="highlight"><pre>session.query(Company).filter(Company.employees.of_type(Engineer).any(Engineer.engineer_info=='someinfo')).all()</pre></div>

424

425

filter(

426

Company.employees.of_type(Engineer).

427

any(Engineer.engineer_info=='someinfo')

428

).all()</pre></div>

381

429

</div>

382

430

Note that the <tt class="docutils literal">any()</tt> and <tt class="docutils literal">has()</tt> are both shorthand for a correlated

383

431

EXISTS query. To build one by hand looks like:

384

432

385

433

exists([1],

386

and_(Engineer.engineer_info=='someinfo', employees.c.company_id==companies.c.company_id),

434

435

employees.c.company_id==companies.c.company_id),

387

436

from_obj=employees.join(engineers)

388

437

)

389

438

).all()</pre></div>

413

462

414

463

415

464

polymorphic_on=employees_table.c.type, polymorphic_identity='employee')

416

manager_mapper = mapper(Manager, inherits=employee_mapper, polymorphic_identity='manager')

417

engineer_mapper = mapper(Engineer, inherits=employee_mapper, polymorphic_identity='engineer')</pre></div>

465

466

polymorphic_identity='manager')

467

468

polymorphic_identity='engineer')</pre></div>

418

469

</div>

419

470

Note that the mappers for the derived classes Manager and Engineer omit the

420

471

specification of their associated table, as it is inherited from the

461

512

'engineer': engineers_table

462

513

}, 'type', 'pjoin')

463

514

464

employee_mapper = mapper(Employee, employees_table, with_polymorphic=('*', pjoin), \

465

polymorphic_on=pjoin.c.type, polymorphic_identity='employee')

466

manager_mapper = mapper(Manager, managers_table, inherits=employee_mapper, \

467

concrete=True, polymorphic_identity='manager')

468

engineer_mapper = mapper(Engineer, engineers_table, inherits=employee_mapper, \

469

515

516

with_polymorphic=('*', pjoin),

517

518

polymorphic_identity='employee')

519

520

inherits=employee_mapper,

521

concrete=True,

522

polymorphic_identity='manager')

523

524

inherits=employee_mapper,

525

concrete=True,

526

polymorphic_identity='engineer')</pre></div>

470

527

</div>

471

528

Upon select, the polymorphic union produces a query like this:

472

529

473

530

474

SELECT pjoin.type AS pjoin_type, pjoin.manager_data AS pjoin_manager_data, pjoin.employee_id AS pjoin_employee_id,

531

SELECT pjoin.type AS pjoin_type,

532

pjoin.manager_data AS pjoin_manager_data,

533

pjoin.employee_id AS pjoin_employee_id,

475

534

pjoin.name AS pjoin_name, pjoin.engineer_info AS pjoin_engineer_info

476

535

FROM (

477

SELECT employees.employee_id AS employee_id, CAST(NULL AS VARCHAR(50)) AS manager_data, employees.name AS name,

478

CAST(NULL AS VARCHAR(50)) AS engineer_info, 'employee' AS type

536

SELECT employees.employee_id AS employee_id,

537

CAST(NULL AS VARCHAR(50)) AS manager_data, employees.name AS name,

538

CAST(NULL AS VARCHAR(50)) AS engineer_info, 'employee' AS type

479

539

FROM employees

480

540

UNION ALL

481

SELECT managers.employee_id AS employee_id, managers.manager_data AS manager_data, managers.name AS name,

482

CAST(NULL AS VARCHAR(50)) AS engineer_info, 'manager' AS type

541

SELECT managers.employee_id AS employee_id,

542

managers.manager_data AS manager_data, managers.name AS name,

543

CAST(NULL AS VARCHAR(50)) AS engineer_info, 'manager' AS type

483

544

FROM managers

484

545

UNION ALL

485

SELECT engineers.employee_id AS employee_id, CAST(NULL AS VARCHAR(50)) AS manager_data, engineers.name AS name,

546

SELECT engineers.employee_id AS employee_id,

547

CAST(NULL AS VARCHAR(50)) AS manager_data, engineers.name AS name,

486

548

engineers.engineer_info AS engineer_info, 'engineer' AS type

487

549

FROM engineers

488

550

) AS pjoin

592

654

'some_c':relationship(C, back_populates='many_a')

593

655

})

594

656

mapper(C, c_table, properties={

595

'many_a':relationship(A, collection_class=set, back_populates='some_c'),

657

658

back_populates='some_c'),

596

659

})</pre></div>

597

660

</div>

598

661

</div>

Older »