Propel custom sql for view tables

For some reason propel is not generating model for view tables, it doesn’t even include the structure of the view table if you use the reverse task. So I have no option but to use a custom query. That I know how to do if the model exists:

<?php 
    $con = Propel::getConnection(BookPeer::DATABASE_NAME);
    $sql = "complicated query here...";
    $stmt = $con->prepare($sql);
    $stmt->execute();

but since propel does not generate a model for my view table, i don’t know how to do that. I’ve tried this but it doesn’t work

<?php 
    $con = Propel::getConnection(MyViewTable::DATABASE_NAME);
    $sql = "SELECT * FROM MyViewTable";
    $stmt = $con->prepare($sql);
    $stmt->execute();

I really need to have this work. Please help 🙂

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Another alternative is to define your view by adding and setting “readonly” and “skipSql” attributes to “true” like this:

<table name="BookAuthor" phpName="BookAuthor" readOnly="true" skipSql="true">
  <column type="integer" size="10" name="AuthorID" phpName="AuthorID" />
  <column type="integer" size="10" name="BookID"   phpName="BookID" />
  <!-- Some other columns, etc. -->

  <foreign-key foreignTable="Author">
        <reference local="AuthorID" foreign="ID" /><!-- Assuming you have Author.ID -->
  </foreign-key>
  <foreign-key foreignTable="Book">
        <reference local="BookID" foreign="ID" /><!-- Assuming you have Book.ID -->
  </foreign-key>
</table>

Once you’ve generated you classes (via “propel-gen” command), you will have the benefits as though it were a table, like this:

// Fetch a BookAuthor row (the view)
$oBookAuthor = BookAuthor::create()->findOne();

// Get the Author (physical table)
$oUser = $oBookAuthor->getAuthor();

// and if you want the "Book" (physical table)
$oBook = $oBookAuthor->getBook();

And you don’t even need a new connection to the DB

Method 2

$con = Propel::getConnection();

You will get your current database connection and you can make any sql query you like,

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0