Ask your Symfony questions! Pay money and get answers fast! (more info)

Anyway to do true raw sql queries with Propel Symfony

This code works:

$connection = Propel::getConnection();
$query = "
SELECT sum(prize_amount_paid_for_this_answer) as prizeTotal
FROM answer
";
$statement = $connection->prepare($query);
$statement->execute();
$this->resultset = $statement->fetch(PDO::FETCH_OBJ);
$this->totalPrize = $this->resultset->prizeTotal;
$this->onePercentOfTotalPrize = round($this->totalPrize / 100);


This code does not work:


$connection = Propel::getConnection();
$query = "
SELECT sum(prizeTotal)
FROM user_and_answer_data
";
$statement = $connection->prepare($query);
$statement->execute();
$this->resultset = $statement->fetch(PDO::FETCH_OBJ);
$this->totalPrize = $this->resultset->prizeTotal;
$this->onePercentOfTotalPrize = round($this->totalPrize / 100);


user_and_answer_data is a database view, not a real table.

This second bit of code gets me the error "Can not build SQL query".

I'm using Propel and Symfony 1.2.5.

The view exists for a reason, its suppose to have all the data I need for a given page. Is there any way to run raw queries against it?

Answers (3)

2010-11-30

José Nahuel Cuesta Luengo answers:

Is 'prizeTotal' a column in the view? If not, you should either add it or use an alias for it..


Lawrence Krubner comments:

It is a column in the view. The query does not work even if I add an alias for it.

2010-11-30

Nate Flink answers:

So you tried:

SELECT sum(prizeTotal) as PRIZETOTALCOL

FROM user_and_answer_data


?


Nate Flink comments:

Yeah, can you try:


$connection = Propel::getConnection();

$query = "

SELECT sum(prizeTotal) as PRIZETOTAL

FROM user_and_answer_data

";

$statement = $connection->prepare($query);

$statement->execute();

$this->resultset = $statement->fetch(PDO::FETCH_OBJ);

$this->totalPrize = $this->resultset->prizeTotal;

$this->onePercentOfTotalPrize = round($this->totalPrize / 100);

2010-11-30

Loban Rahman answers:

The database view should, for almost all purposes, work just like a real table as far as propel is concerned. The problem is that sum(prizeTotal) needs to be aliased, otherwise it cannot be referred to easily. Also, I'm not sure what the backend database is, so I dunno if there will be any problem using PRIZETOTAL as the alias, because it is exactly the same as prizeTotal (except for case). Use an alias like sum_of_prize_total. Also, in your view, name the virtual column prize_total instead of prizeTotal.

Soo ....

$connection = Propel::getConnection();
$query = "
SELECT sum(prize_total) AS sum_of_prize_total
FROM user_and_answer_data
";
$statement = $connection->prepare($query);
$statement->execute();
$this->resultset = $statement->fetch(PDO::FETCH_OBJ);
$this->totalPrize = $this->resultset->sum_of_prize_total;
$this->onePercentOfTotalPrize = round($this->totalPrize / 100);

I always use underscores and all lowercase for any database columns, tables, views, etc, just to be safe.


Loban Rahman comments:

ALSO, why not just define an object schema for your database view? Then use Propel ORM as normal?

Check out http://www.propelorm.org/wiki/Documentation/1.3/FAQ

Excerpt below ....

------------------------------------------------
Does Propel support Views

Propel handles views like normal tables. But to check if your views are updatable, and not read-only, consult your database documentation.

You will need to define the view yourself (e.g. using a custom SQL file). In your schema.xml, you can create a table that matches your view columns and add skipSql="true" readOnly="true" to prevent Propel from attempting to create the table and from adding save methods to the generated object class.

See also this mailing list post on the topic.
------------------------------------------------------