1

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

user949000
  • 313
  • 2
  • 6
  • 19

2 Answers2

5

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

Qiniso
  • 2,587
  • 1
  • 24
  • 30
1
$con = Propel::getConnection();

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

user2554865
  • 365
  • 1
  • 9