CODEWITHSUNDEEP
sqlsymfony1symfony-1.4propel
3logy
3logy

Reputation: 2712

nested SQL Statements with Propel?

I'm trying to translate a SQL Statement into Propel, without so much success. I have this SQL Statement:

SELECT id, param1, param2
              FROM Field1
              WHERE id
              in
              (
                  SELECT DISTINCT Field1_id
                  FROM Field2
                  WHERE id in
                  (
                      SELECT DISTINCT `Field2_id`
                      FROM `Field3`
                      WHERE
                      `param7` is null
                      AND param5 > 40
                  )
              ) LIMIT 0, 1000

i started doing it in a raw Way:

$connection = Propel::getConnection();
$query = "my Query";
$statement = $connection->prepare($query);
$statement->execute();
$results = $statement->fetch(PDO::FETCH_ASSOC);

This works pretty well, but i can't do any Propel Actions on $results cause it is an Array.

So how can i translate this SQL into Propel without the raw Way?

Upvotes: 3

Views: 1117

Answers (2)

halfer
halfer

Reputation: 20487

A good quick-fix for this is to create a view in your database, and set up a read-only table in Propel for that view. You can then use your view model just as you would an ordinary Propel table. Now of course this is cheating, and is frowned upon by ORM purists, but it will get you going in the short term.

My approach is to try to refactor back to tables where I've used this for a trivial query, but for big, central queries (in particular complex queries that are used for primary drill-down screens) I've been more than happy to keep the view approach permanently.

Upvotes: 1

K-Phoen
K-Phoen

Reputation: 1270

As far as I know, Propel does not handle nested queries. Instead, you can write table subqueries.

Upvotes: 0

Related Questions