Custom queries?

[darkredz]

Hi, I am new to Recess. I wonder how do I run custom query in Recess?
something like $db->query('SELECT * FROM some_table'); ?
I know it's now a good practice but I have some existing complex
queries.

[Mike.P]

Take a look at the model chapter here to get an idea of the custom
queries available to you:
http://www.recessframework.org/book/html/ch12s02.html

[darkredz]

Oh thanks, but can I just dump my SQL into a method so that Recess
will run the query and get the result back?

[Joshua Paine]

On Tue, 2009-05-19 at 10:46 -0700, darkredz wrote:
> Oh thanks, but can I just dump my SQL into a method so that Recess
> will run the query and get the result back?

$db = Databases::getSource('nameOfYourDbInRecessConf');

$db is a PDO connection object <http://php.net/pdo> -- do whatever you
want with it from there.

--
Joshua Paine
LetterBlock: Web applications built with joy
http://letterblock.com/
301-576-1920

[darkredz]

Ah I see, it's returning a PDO. Thanks for giving me a headstart.

[Kris Jordan]

In the next iteration on Models there should be an easier way to get the PDO object so you could do something like:

$car = new Car();

$rows = $car->pdo()->query('SELECT ...');

foreach($rows as $row) {

   print $row['column'];

}

Would be dead simple to do something like that. My question is, is there a better way of opening up raw SQL? We could *try* to return actual models but the challenge is many raw sql statements are specifically for data that isn't a defined Model.

What have you guys needed to use raw SQL for? How was it ugly?

[Jamie Rumbelow]

I think the point of a model-based system is to remove the need for SQL queries entirely, and for the most part, it does it's job. But when I need to create a complicated SQL query with JOIN statements and other nasties, I use a raw SQL string. 

It's the right tool for the job, and as of yet, I haven't seen an ORM that could handle REALLY complicated SQL strings with ease and efficiency.

Jamie

[KevBurnsJr]

In my experience working with propel, I eventually came across a need
for complex joins which the ORM really didn't support very well.
propel is a Table Data Gateway / Row Data Gateway ORM as opposed to
Recess's ActiveRecord
http://propel.phpdb.org

With propel, you can execute some custom sql ...

$sql = "SELECT books.* FROM books WHERE NOT EXISTS (SELECT id FROM
review WHERE book_id = book.id)";
$stmt = $con->createStatement();
$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);

... then feed the result to a TDG Peer to generate a collection of RDG
Models ...

$books = BookPeer::populateObjects($rs);

So long as the user understands that the populateObjects method
(wherever it resides) expects a resultset containing tablename.* this
seems it might be a useful piece of functionality to bridge the gap
between assuming a user has selecting the proper fields and leaving
them stuck with arrays.

More on propel's custom SQL
http://propel.phpdb.org/trac/wiki/Users/Documentation/1.2/BasicCRUD#UsingCustomSQL

- Kev

On May 20, 12:15 pm, Kris Jordan <krisjor...@gmail.com> wrote:
> In the next iteration on Models there should be an easier way to get the PDO
> object so you could do something like:
> $car = new Car();
>
> $rows = $car->pdo()->query('SELECT ...');
>
> foreach($rows as $row) {
>    print $row['column'];
>
> }
>
> Would be dead simple to do something like that. My question is, is there a
> better way of opening up raw SQL? We could *try* to return actual models but
> the challenge is many raw sql statements are specifically for data that
> isn't a defined Model.
>
> What have you guys needed to use raw SQL for? How was it ugly?
>

[darkredz]

Another interesting implementation of ORM would be DataMapper, it's a
more elegant solution than ActiveRecord. http://www.martinfowler.com/eaaCatalog/dataMapper.html
There's an implementation for DataMapper in PHP which I find quite
interesting for the moment
http://phpdatamapper.com/goals/

@Kris - $car->pdo()->... is short and nice. Well, I have pretty ugly
sql which i find time consuming to change all of them to methods.


@Jamie - I too haven't seen an ORM that could handle REALLY complicated
(aka ugly) SQL, I think that's what raw SQL is for after all.

[Mike.P]

Having spent a few years using MS SQL databases I became accustomed to
using views to do JOINS/UNIONS. I am wondering why you wouldn't use a
View when presented with complex queries and to take it a step further
I thought MySQL 5 supports stored procedures as well? Most of our
work was in the business layer so I found it easiest to create my
tables, views, and procedures before jumping into coding. I am not
sure if MySQL contains the same added value of indexing views but I
wanted to open this up for discussion and wanted to get a feel for
other's experience with MySQL views and procedures.

On May 21, 2:19 am, darkredz <darkr...@gmail.com> wrote:
> Another interesting implementation of ORM would be DataMapper, it's a

> more elegant solution than ActiveRecord.http://www.martinfowler.com/eaaCatalog/dataMapper.html

> There's an implementation for DataMapper in PHP which I find quite

> interesting for the momenthttp://phpdatamapper.com/goals/