ORM解决方案，用于真正复杂的查询 [英] ORM Solution for really complex queries

问题描述

可以/应该使用任何ORM解决方案是这样的复杂情况？
可以通过 Propel 或 Doctrine 来完成？目前我正在使用Propel。所以我会很高兴如果有Propel解决方案。

如果我直接查询propel有什么建议？

  SELECT I。*，（（I.width * 175）/I.height）as relativeWidth FROM 
（SELECT * FROM Image WHERE owner = 1 LIMIT 5，10）I 
 order by relativeWidth asc 
  

另外什么是需要的场景的标准解决方案在应用程序的大部分地方执行真正复杂的查询？

有一个 LIMIT 5，10 用于分页。并在其内查询。如果在查看中如何控制分页？

解决方案

您的查询可以如下使用Propel 1.6.3： p>

 <？php 
 
 // SELECT * FROM Image WHERE owner = 1 LIMIT 5，10 
 $ subQuery = ImageQuery :: create（）
  - > filterByOwner（1）
  - > limit（10）
  - >偏移量（5）
; 
 
 $ query = ImageQuery :: create（）
  - > addSelectQuery（$ subQuery，'I'，true）
  - > withColumn（'（（I.WIDTH * 175）/I.HEIGHT）'，'relativeWidth'）//（（I.width * 175）/I.height）as relativeWidth 
  - > orderBy（'relativeWidth'）// default是'asc '
; 
 
 $ params = array（）; 
 var_dump（\BasePeer :: createSelectSql（$ query，$ params））; 
  

输出是：

  SELECT I.ID，I.WIDTH，I.HEIGHT，I.OWNER，（（I.WIDTH * 175）/I.HEIGHT）AS relativeWidth 
 FROM（
 SELECT图像.ID，image.WIDTH，image.HEIGHT，image.OWNER 
 FROM`image` 
 WHERE image.OWNER =：p1 LIMIT 5，10 
）AS I 
 ORDER BY相对宽度ASC 
  

请注意：p1 是绑定参数与所有者值相关。

所以使用Propel创建复杂查询非常简单：）

William

Can/should One use any ORM Solution is a Complex Situation like this ? can this even be done with Propel or Doctrine ? at the moment I am using Propel. So I'd be glad If there is Propel solution for it.
If I do a direct query with propel what are the suggestion ?

SELECT I.*,((I.width*175)/I.height) as relativeWidth FROM 
(SELECT * FROM Image WHERE owner = 1 LIMIT 5, 10) I
order by relativeWidth asc

and additionally What is the standard solution for the scenario where one needs to exequte really complex queries in most of the places in the application ?

There is a LIMIT 5, 10 that I use for Pagination. and its in inner Query. If its in View how can I control Pagination ?

解决方案

Your query can be written as following with Propel 1.6.3:

<?php

// SELECT * FROM Image WHERE owner = 1 LIMIT 5, 10
$subQuery = ImageQuery::create()
    ->filterByOwner(1)
    ->limit(10)
    ->offset(5)
    ;

$query = ImageQuery::create()
    ->addSelectQuery($subQuery, 'I', true)
    ->withColumn('((I.WIDTH*175)/I.HEIGHT)', 'relativeWidth') // ((I.width*175)/I.height) as relativeWidth
    ->orderBy('relativeWidth') // default is 'asc'
    ;

$params = array();
var_dump(\BasePeer::createSelectSql($query, $params));

Output is:

SELECT I.ID, I.WIDTH, I.HEIGHT, I.OWNER, ((I.WIDTH*175)/I.HEIGHT) AS relativeWidth
FROM (
    SELECT image.ID, image.WIDTH, image.HEIGHT, image.OWNER
    FROM `image`
    WHERE image.OWNER=:p1 LIMIT 5, 10
) AS I
ORDER BY relativeWidth ASC

Note the :p1 is the binding parameter related to the owner value.

So it's really easy to create complex queries using Propel :)

William

这篇关于ORM解决方案，用于真正复杂的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！