ORM解决方案，用于真正复杂的查询

本文介绍了ORM解决方案，用于真正复杂的查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 可以/应该使用任何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