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

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

$4
Anyway to do true raw sql queries with Propel

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?

Lawrence Krubner | 11/29/10 at 11:55pm Edit
Tutorial: How to assign prize money


The experts have suggested, on average, a prize of $5 for this question.

(6) Responses

See a threaded view of answers?

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

  • avatar
    Last edited:
    11/30/10
    10:41am
    José Nahuel Cuesta Luengo says:

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

  • avatar
    Last edited:
    11/30/10
    10:50pm
    Nate Flink says:

    So you tried:

    SELECT sum(prizeTotal) as PRIZETOTALCOL

    FROM user_and_answer_data


    ?

  • avatar
    Last edited:
    11/30/10
    11:04pm
    Loban Rahman says:

    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.

  • avatar
    Last edited:
    11/30/10
    10:05pm
    Lawrence Krubner says:

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

  • avatar
    Last edited:
    11/30/10
    10:51pm
    Nate Flink says:

    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);

  • avatar
    Last edited:
    11/30/10
    11:08pm
    Loban Rahman says:

    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.
    ------------------------------------------------------

This question has expired.





Current status of this question: Community pot



Please log in to add additional discourse to this page.





Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.