ORM Solution for really complex queries

Question

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 ?

Related question :http://stackoverflow.com/questions/7860871/propel-pseudo-column-sorting — halfer, Oct 25 '11 at 16:45

score 2 · Accepted Answer · answered Nov 01 '11 at 12:41

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

score 0 · Answer 2 · answered Oct 25 '11 at 16:49

0

Some queries are best fixed with views, imo. True, this isn't for ORM purists, but I've found it a very maintainable approach whilst reaping the benefits of an ORM. I would say that, within a single project, you should only do this 10% of the time or so; if you find that you are writing a view for most of your ORM statements, then perhaps another approach is required.

That all said, the example you cite is quite simple. I would be inclined to write a special method using raw SQL in the peer class, and hydrate it manually, as per my example on your related question. Just remember that when running raw sql, it is easier to fall prey to SQL injection - remember to escape, clean or parameterise your variables.

answered Oct 25 '11 at 16:49

halfer

19,824
17
99
186

There is nothing wrong with using views in ORM, it would actually make a DBA our best friend. – Andrei Drynov Oct 25 '11 at 16:51
But in the query I've given `LIMIT 5, 10` is used for pagination that is in inner Query which I cannot control if its in view !! – Neel Basu Oct 25 '11 at 17:15
Please Check My Edit in Question – Neel Basu Oct 25 '11 at 17:43
1

Ah, I see the purpose of the LIMIT statement now. Do that in Propel, not in your query. $c->setLimit() if you are using Criteria; a similar thing will be available for the Query approach. – halfer Oct 25 '11 at 18:08
How can I do it in propel even ? its in inner query !! – Neel Basu Oct 25 '11 at 18:28
No, it isn't: SELECT I.*,((I.width*175)/I.height) as relativeWidth FROM Image WHERE owner = 1 order by relativeWidth asc LIMIT 5, 10 – halfer Oct 26 '11 at 00:45
No. It is a different query. and it yields different result that the one in my question. – Neel Basu Oct 26 '11 at 04:36

ORM Solution for really complex queries

2 Answers2

Linked