Raw queries

[jockster]

Hi Jamie,

I really like how idiorm seems to work. Simple, really :)

From reading the manual, I've found out about the raw_query function: $people = ORM::for_table('person')->raw_query('SELECT p.* FROM person p JOIN role r ON p.role_id = r.id WHERE r.name = :role', array('role' => 'janitor')->find_many();

From what I understand, only selects can be made using it.

If this is true, I would like to make an request to be able to write whatever query I want in a truly RAW form not limited to SELECTS.

This way I could use idiorm with a little more confidence that we wont outgrow it if we reach a point where we need to write some complex-ish query.

Looking forward to hear from you Jamie! Thanks

[j4mie]

Hi,

Technically, you could probably run any query you like via raw_query, as the queries are just passed through to PDO.

However, it's probably easier to just interact with PDO directly:

ORM::get_db()->exec("INSERT INTO `table`..... ");

Does that answer your question?

[jockster]

HI Jamie,

That's great - didn't know that the exec() thingy existed. Thanks a lot!

[jockster]

Hi again Jamie,

Didn't want to open another issue for you. I tried the exec() today, but it appears that it doesn't work:

$exec = ORM::get_db()->exec("DELETE FROM sessions WHERE expire <= '2011-02-18 01:04:39'");

var_dump ($exec); // Returns 1 - but nothing is done to the database
var_dump( ORM::get_last_query()  ) ; // returns another unrelated query - not my delete

I've run my query in mySQL directly to ensure that It is formatted correctly, which it is - the deletion is carried out, but not whilst doing it through exec() in idiorm.

Any ideas?

[j4mie]

Hi, Sorry, I should have been more clear in my response:

ORM::get_db() returns the PDO object that Idiorm uses to communicate with the database. When you call ORM::get_db()->exec("some sql..");, you are actually calling the exec method of this object. This is bypassing Idiorm's query building mechanism entirely, and so your query will never get logged.

I'm not sure why your query isn't being executed. I've just tried exactly the same thing on a test database on my machine and it works fine. Could it be something to do with a transaction not being committed perhaps?

I suggest you take a look at the PDO documentation: http://php.net/manual/en/book.pdo.php

Cheers

Jamie

[jockster]

Hi Jamie,

Thanks a lot! I'll go through the PDO documentation!