j4mie / idiorm Goto Github PK

Hi,

Idiorm works very well for me but now there is one thing for that I need a complex query:

$insertNetwork = ORM::for_table('svc_networks')->raw_query('INSERT INTO svc_networks (svc_organisation_id,svc_contact_id,svc_network_label_id,url) VALUES (:one,:two,:three,:four) ON DUPLICATE KEY UPDATE url=:four', array('one' => 1,'two' => 14,'three' => 4,'four' => 'test'));

Is there any way to do this with Idiorm?

Add documentation for how to deal with transactions.

I'm using Paris and idiorm for one of my sites and I've got lots of foreign key relationships expressed like this:

public function children() {
  return $this->has_many('Child')->find_many();
}

The problem is that each time I execute the children() function on the same object, idiorm executes the following SQL (for example):

SELECT * FROM child WHERE parent_id = 1

If I'm using information about the children several times on the same page - e.g. checking whether there are any children using count(), then iterating over them - there are lots of unnecessary queries made. I'm never going to change the data once I've entered a view, so if idiorm was able to cache these queries that would be really helpful for my application.

I think that using where_raw clause eliminates where clauses. Is possible this?

$items = ORM::for_table('profiles');
if ($gender) $items = $items->where("gender", $gender);    
if ($region) $items = $items->where("region_slug", $region);    
if ($age_from AND $age_to) $items = $items->where_raw('(age >= ? AND age <= ?)', array($age_from, $age_to));    
$items = $items->offset(0)->limit(15);
$items = $items->find_many();

There is another way to do it?

Regards

See pull request #49 for inspiration.

It appears that $orm->hydrate(array) will entirely replace a model's internal data. Can you add a method to easily merge an array of external data into a Model's existing data? Here's an example:

//The external data
$_POST['page']['title'] = 'My New Page Title';
$_POST['page']['content'] = 'The new content';

//Copy data into model via merge
$page = Model::factory('page')->find_one(1);
if ( $page ) $page->merge($_POST['page']);

Thoughts? As is I can only assign attributes one at a time. Thanks!

sqlite://:memory:

should be

sqlite::memory:

Hi,

Could you please make a composer.json file in your repo?

Thanks,
Zhenya.

Hi!

I was quite impressed by compact and clean code of idiorm - so, I'm giving it a try in project where Postgresql database is be used.
Problem I had was with ORM::_quote_identifier_part() - which quotes table/column names with "`", but a SQL statement in postgres commits seppuku when treated that way :)
Could quoting character be made as a configuration option?

.. code-block:: php

protected function _quote_identifier_part($part) {
   $quote = self::$_config['identifier_quote_ch'];
   return $quote . $part . $quote;
}

Or - probably, identifier quoting could be called as a callback, leaving ORM::_quote_identifier_part just as a default method for that and be overridable in configuration?

How do You feel about such changes? What the correct approach would be?

you should add AUTO_INCREMENT to the create table statement in your demo_mysql.php to get working example.
keep up the good work

I found a need for a group_by_expr because backticking of the column names was getting in the way. I wanted to do something like:

   ->group_by_expr("FROM_UNIXTIME(`time`, '%Y-%m')")

A super simple patch. Not sure whether it would be of interest to you:

    /**
    * Add an unquoted expression to the list of columns to GROUP BY 
    */
    public function group_by_expr($expr) {
          $this->_group_by[] = $expr;
          return $this;
    }

From Sérgio Diniz (by email):

I found one thing that I really miss, is the max function, instead of using a raw query it could be already in your ORM… so I implemented it quickly:

/**
* Tell the ORM that you wish to execute a MAX query.
* Will return the max value of the choosen column.
*/
public function max($column)  {
   $this->select_expr('MAX('.$column.')', 'maxvalue');
   $result = $this->find_one();
   return ($result !== false && isset($result->maxvalue)) ? (int) $result->maxvalue : 0;
}

I only tested it in MySQL…

First - great orm, hits a sweet spot of basic functionality. I'm looking to extend it for some specific needs - But I see that most of the methods/members that are not public are private. I had thought that that approach makes if difficult to extend because then I have to redeclare the methods - would it be possible, better to use protected visibility? Is there some reason it's defined as private that's better than protected?

This is a modified version of the get method:

public function getAllData()
{
return isset($this->_data) ? $this->_data : null;
}

You somehow missed this in the orm class.

I'm running in an issue that only manifests when logging is on:

     select_expr("FROM_UNIXTIME(`time`, '%Y-%c')", 'month')

I get a php warning:

    vsprintf(): Too few arguments in <strong><code>/path/to/idiorm/idiorm.php</code></strong> line <strong><code>267</code></strong>

A little examination reveals that the $parameters array in _log_query() function gets an extra item that is undefined. Not sure what is happening though. It doesn't happen with a simpler expression, like COUNT().

Hi Jamie,

I am missing a affected-rows function that could be used for updating the database and ensure that the rows were properly inserted:

$person = ORM::for_table('person')->find_one(5);
$person->set('name', 'Bob Smith');
$person->age = 20;
$person->save();

if ($person->affectedRows() > 0)
return true;

Or is this already hidden in the code?

Do you have any plans to allow idiorm to switch between multiple databases during runtime?

Hi again Jamie,

I guess that this is a no-brainer for you, but it's starting to give me grey hairs :)

I have instantiated idiorm like in your example;
ORM::configure('mysql:host=192.168.1.1;dbname=myDb');
ORM::configure('username', 'root');
ORM::configure('password', 'root');

I need to access the ORM static variable from a method inside a class, like this for instance

class Sessions extends myModels {
    function get($id) {
        ORM::for_table('sessions')->find_one($id); // Throws Fatal error: Class 'ORM' not found
    }  
}

How should I do this the most proper way?

"If you wish to override this behaviour (for example, to supply a database expression) you should instead use the select_expr method. Again, this takes the alias as an optional second argument.

// NOTE: For illustrative purposes only. To perform a count query, use the count() method.
$people_count = ORM::for_table('person')->select('COUNT(*)', 'count')->find_many();"
-->
"If you wish to override this behaviour (for example, to supply a database expression) you should instead use the select_expr method. Again, this takes the alias as an optional second argument.

// NOTE: For illustrative purposes only. To perform a count query, use the count() method.
$people_count = ORM::for_table('person')->select_expr('COUNT(*)', 'count')->find_many();"

You should use more phpdocs, variables defination and return types

Hi there,

I was wondering if this is supposed to work:

$rows = ORM::for_table( 'mytable' )
->join( 'othertable', array( 'mytable.someid', '=', 'othertable.someid' ) )
->find_many();

foreach ( $rows as $row )
{
$row->SomeField = 'MyValue';
$result = $row->save();
if ( !$result )
{
print( 'Failed saving row' );
}
}

SomeField is column in mytable. After execution I see that 'MyValue' has not been saved in database although no errors occurred. Is this supposed to work?

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

On MySQL the following Query is possible:
SELECT * FROM table WHERE LOWER(field) = 'stringtomatch';

I personally use that A LOT on some webapps .. is there a way for this kind of comparison on idiorm as well? ->select('LOWER(field)', 'stringtomatch') isn't working for me.

The same is for other things like transforming a datetime to a timestamp using UNIX_TIMESTAMP(field).

While building selects via fluent syntax, I felt that at least 2 more where_xxx helper methods would be very helpful:

• ORM::where_null($field) and ORM::where_not_null($field)

I can, of course, write
where_raw('field IS NOT NULL', array())

but it breaks the charm and is not very intuitive (e.g., where_raw('field IS NOT ?', array(null)) gave me error)

Have I overlooked something already designed for this?

also, I found temptating to write smth like: where_ne($field, $notEqualToThis).

They would be nice "syntactic sugar" and quite simple to implement...
Additionally, having column name in "where_xx" methods as a param, could allow to build more advanced interface layers in future on top of idiORM (e.g., converting object property names to column names).

To make Idiorm UTF-8 compatible, add:

array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")

to the setup_db function in the main class.

Code:

    private static function setup_db() {
        if (!is_object(self::$db)) {
            if (self::$config['username'] && self::$config['password']) {
                self::$db = new PDO(
                    self::$config['connection_string'],
                    self::$config['username'],
                    self::$config['password'],
                    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
                );
            } else {
                self::$db = new PDO(self::$config['connection_string']);
            }
            self::$db->setAttribute(PDO::ATTR_ERRMODE, self::$config['error_mode']);
        }
    }

Self-Explanatory, some form of support for doing where queries with OR would be amazing!

Idiorm and Paris are seeking a new maintainer: https://github.com/j4mie/words/blob/master/2012-07-15-idiorm-and-paris.md

Mustache.php supports the so called dot notation.
However seems like it's not working with ORM objects:

#index.php
$album = ORM::for_table('album')->find_one($id)

#index.mustache
{{album.name}} #empty string
{{#album}}{{name}}{{/album}} # album's name

Hi
I want to user idiorm on PHP Server version 5.2.17 and i just got this error. what do you think?

Parse error: syntax error, unexpected T_STATIC in /home/emitsite/public_html/lib/idiorm.php on line 1420

In most cases it's fine. But, if you're using "natural keys" (not auto_increment) everything goes wrong.

I tried:

ORM::for_table('jobs')->raw_query('TRUNCATE :jobs_table',array('jobs_table', 'jobs'));

It is currently not possible to update the id property -- I end up with a query which looks like
UPDATE sessions SET id = 'xB-xTW-9sZ6WlvlTacrbPEfxpVi9hDcpkFZhLPqAKYg' WHERE id = 'xB-xTW-9sZ6WlvlTacrbPEfxpVi9hDcpkFZhLPqAKYg'

Where clearly what was intended was to have the second 'id' be what was originally in the db.

Is there some way to make idiorm save() work with tables with compound primary keys? The configuration allows only single column to be specified as primary key.

I think this would be simpler to use the same approach throughout.

I want to do this:

Select * From Table GROUP BY YEAR (DateField)

my DateField is of type datetime (yyyy-mm-dd ....)

I need that is grouped only for year and not for month and day

How do you do the YEAR() function of mysql ?

It took me a while to figure out what was going on, but I am getting an exception when attempting to connect to the DB under localhost, that otherwise works fine for 127.0.0.1.

The culprit is PDO uses a socket connection when using 'localhost'. On my box for whatever reason mysql.sock is in a different place than the the PHP default. The fix is easy, simply add unix_socket=/path/to/mysql.sock, to the PDO connection string.

Obviously, your library doesn't have to work around a missing socket, but it probably shouldn't throw an exception showing the DB username/password. Perhaps in setup_db() wrap the connection in a try/catch block?

Hi, first, great lib!

I'm triying to set a date field using NOW() in mySQL, is this possible with idiorm?

$content->created = 'NOW()';

does escape and fill the database with wrong data 0000-00-00

Issue

When logging is on:

ORM::configure('logging', true);

Queries containing the "%":

$many = Model::factory('user')->where("active",1)->where_raw('username like "ben%"')->find_many();

Raise PHP warning:

Warning: vsprintf() : Too few arguments in idiorm.php on line 267

Because % must be escaped in the query for vsprintf.

Fix

In idiorm.php, line 264:

$query = str_replace("?", "%s", $query);

should be:

$query = str_replace("?", "%s", str_replace("%","%%",$query));

This fixes the problem for the "%" chars.

Second issue

The problem remains with the "?" char inside the query:

$many = Model::factory('book')->where("active",1)->where_raw('comments like "has been released?%"')->find_many();

vsprintf raises the same error.

— Ben

create then save a default empty row
$one = Model::factory('foo')->create();
$one->save();

raises:
Warning: array_fill() : Number of elements must be positive in idiorm.php on line 592

fix

line 591 idiorm.php:
protected function _create_placeholders($number_of_placeholders) {
if ($number_of_placeholders) {
return join(", ", array_fill(0, $number_of_placeholders, "?"));
}
return "";
}

Would be great to implement that interfaces instead the actual set, get.
Not instead, I mean "in addition"

Say I use the following:

$page_size = 100;
$page_number = 3;
$users = Model::factory('BlergUsers')
                 ->where('session_id', $session)
                 ->where('user_type',$user_type)
                 ->order_by_asc('id')
                 ->offset($page_size * $page_number)
                 ->limit($page_size)
                 ->find_many();

foreach($users as $user)
{
  $username = $user->name;
}

I get an array that is 100 elements long, no matter how many rows are really available.

Should there not just be the number of rows returned from the query?

Thanks,
Per

Any thought of adding DISTINCT and GROUP BY to Idiorm?

Hi again Jamie,

I tried the following, which didn't work. Is there any current solution to my issue?

    $existingSessions = ORM::for_table('sessions')->where_lt('expire', date("Y:m:d H:i:S", time()))->find_many();

    $existingSessions->delete();

Thanks

Hey j4mie,

firstable, thanks for this great, simple ORM!

I just encountered a slight bug, when using $sth->offset(), but not specifiying a row limit count with $sth->limit(). The required LIMIT is not emitted in the final SQL (but required for the statement to work).

Following patch fixes the issue:

diff --git a/idiorm.php b/idiorm.php
index 4d454cb..87c8518 100644
--- a/idiorm.php
+++ b/idiorm.php
@@ -856,6 +856,9 @@
             if (!is_null($this->_limit)) {
                 return "LIMIT " . $this->_limit;
             }
+            if (!is_null($this->_offset)) {
+                return "LIMIT 18446744073709551615";
+            }
             return '';
         }

-- 
1.7.3.1

Thanks and regards,

ViFo.

Using 1.2.2 in my app I have a model like this:

class Config extends Model {
   public static $_table = 'Sites';
}

When I call a find_array it does not use the table information

    $config = Model::factory('Config')->where('id',$siteId)->find_array();

Error is that table config doesn't exist. If I add the for_table('Sites') method, it works.

Method "_quote_identifier_part" do basic escaping - it just wrap raw string by extra quote symbols in end and begining:
return $quote_character . $part . $quote_character;

this code can be broken by string , that already contain $quote_character inside.

Simplest solution will be - just dublicating $quote_character inside of string. In that case we will have valid syntax.

For more details see example with "Hello" on: http://dev.mysql.com/doc/refman/5.1/en/string-literals.html

This tested and work with mySql, but other databeses probably have same behavior.

From Osman Üngür (by email):

Is as_array() can be usable with find_many() for array output ? (I tested, as_array() with
find_many() gives me an error)
(Its simply possible with iterating result object to Redis Lists but i want to set json encoded
array output to Redis key) (I dont want to iterate it again and again)

I'm not sure if it should be posted as an issue or as a feature request. I have problem to use raw queries to send update query to the database. For example I need to change many rows at once with simple where condition.
It would be great if one could use it like that:

ORM::for_table('person')->raw_query('UPDATE worker SET income = :income WHERE job = :job',array('job' => $job, 'income' => $income))->save();

But save doesn't send it.
find_one / find_many runs that query, but with problems with null parameters (I had to set null and it set 0) and with PDO error at the end.

I think this feature will help those who need to update on many rows - without refactoring much in the library.

Error in example for Raw Query:
$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();

Fix:
$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();

*Note the 2nd ) near find_many()

Hi,

There's no having statement in idiorm. Could you add this please ?
I've already added it in the orm but it's not a good thing to have 2 versions...

Thanks