vrana / notorm Goto Github PK

Hi,

Can notrom handle multiple databases? If so, is there an example anywhere?

Many thanks

V projektu na ktereme delam mam za ukol provest portaci na NotORM, a hned pri prvni metode jsem narazil na podivne chovani.

Puvodni SQL prikaz je

SELECT *, users.id as uid FROM users LEFT JOIN groups ON users.groups_id=groups.id WHERE gname<>'user';

ktery jsem prepsal na

$notorm->users()->select("*, users.id as uid")->where("groups.gname != 'user'");

Ten si podle ladiciho vystupu NotORM prevede na

SELECT *, users.id as uid FROM users LEFT JOIN groups ON users.groups_id = groups.id WHERE (groups.gname != 'user');

coz je temer totozne s puvodnim prikazem.

Problem ale je, ze vrati jen 4 vysledky, zatimco v databazi jich je 7, viz screenshot z Adminera (loginy a hesla jsou zamerne zcenzurovany pomoci DOM editoru pred porizenim screenshotu):

Vracene radky jsou ccc, eee, fff a ggg, tedy jako by posledni radky od kazdeho groups_id (nebo gname).

Vyzkouseno jak na verzi 2011-06-07 tak na aktualnim Gitu.

Hi guys,

first of all I really like the stuff. Brilliant. Unfortunately MSSQL and Oracle doesn't support LIMIT,OFFSET this way how it is implemented here. Need to extremely writing over original query and put some row numbering "emulation" around. Moreover it will complicates further joins and subqueries if any. There are several limit,offset hack but each has its own drawbacks.
I'll try to implement MSSQL solution. Is there any discussion platform where we can talk about just in case?

NotORM is a great piece of work. It saves my time and makes my code cleaner. Thank you!

The following is a feature suggestion. Current way of handling conditions with OR in between is incomplete, in my opinion.

Flaws of the current method:

1. NotORM_Result->where() doesn't support arrays and NotORM results if they're used along with parameter markers
   where("(id IN ?) OR (attr = ?)", $array, $scalar)
   doesn't work, as opposed to a simple call
   where('id', $array)

2. conditions joined with OR cannot be built gradually with the same comfort as those joined with AND

To resolve (1), we could rewrite where() to treat all cases the same way. The way to go would probably be to isolate parameters treatment into a separate method. While this would be quite easy to implement, it still wouldn't make using OR operators as natural as AND.

A solution that would target both issues would unfortunately require new API commands and changes throughout NotORM_Result. My idea is something like:
$db->my_table->or()->where('id', $subQuery)->where('title LIKE ?', '%foo%'))->and()->where('title LIKE ?', '%bar%');

This would execute the following query:
SELECT * FROM my_table WHERE ((id IN (...)) OR (title LIKE '%foo%')) AND (title LIKE '%bar%')

NotORM would start in AND mode (the current behaviour) and calls to or()/and() would switch the mode back and forth.

An alternative way to pass OR conditions would be via a multi dimensional array:

$db->my_table->where(array(
  'or' => array(
    'id' => $subQuery,
    'title LIKE ?' => '%foo%'
  ),
  'title LIKE ?' => '%bar%'
));

To add another OR at the end, you'd have to rewrite the parameters as follows:

$db->my_table->where(array(
  'or' => array(
    'and' => array(
      'or' => array(
        'id' => $subQuery,
        'title LIKE ?' => '%foo%'
      ),
      'title LIKE ?' => '%bar%'
    ),
    'title' => 'something',
  ),
));

Please let me know what you think.
Petr

I know that from class NotORM_Row i can access column values by $table['column'] but problem is when I need to get associative array and pass it somewhere..

plz. add sth. like :
public function toArray() {
return $this->row;
}
in NotORM_Row class
And maybe to RowSet as well ?

A generated query: SELECT * FROM scrl_context WHERE (key = 'default');
Fails because "key" is a reserved word and must be quoted: key

Please have a look at these two tests:
http://tmp.pepa.info/notorm/32.1-failing-repeated-loop.phpt
http://tmp.pepa.info/notorm/32.2-working-repeated-loop.phpt

The only difference between the two is that in the failing case, I store $software->author() in a variable and use that variable when looping.

I found out that the second loop ends up prematurely because of nulling in NotORM_Result::__destruct().
https://github.com/vrana/notorm/blob/master/NotORM/Result.php#L34

To work around the issue, I can clone my variable before the first loop however, that causes related SQL queries to be executed again for each loop.

Zadany vyraz vrati prazdne pole v pripade, ze stlpec 'b' neexistuje.
V pripade ak existuje, vrati polia s hodnotami stlpcov 'b' a 'c', nie 'a' a 'c'.

$table->select('a AS b', 'c')->fetchPairs('b', 'c');

jedina moznost je zatial pisat

$table->select('a AS b', 'c')->fetchPairs('a AS b', 'c');

co mi ale pride ako zbytocne zdvojovanie kodu, najme pri zlozitejsich selectoch..

Assume the following tables:
minisites {
id
campaign_id
title
}

campaigns {
   id
   title
}

Assume a declared the NotORM_Structure_Convention:
$structure = new NotORM_Structure_Convention("id","%s_id","%ss");

NotORM still can't get it right, or am I missing something?

$_minisites = $nORM->minisites($criteria);
foreach ($_minisites as $m) {
    print $m['title'].'|';
    print $m->campaigns['title'].'|'; // wrong,
    print $m->campaign['title'].':'; // wrong,
}
// NOTORM try to access the campaigns_id which is wrong

Any ideas how to make it work?

This is a rephrased version of #64

You've objected to the idea of exposing the PDO connection. I get the point of your counterarguments but that doesn't help me with what I wanted the PDO reference for.

NotORM does only certain things but what if I need more? Was my workaround a real solution then? (To store a reference to PDO myself at the time I give it to NotORM.)

For example

$url = "www.site.com/path";
table->where("'$url' LIKE CONCAT(`host`,'%')");
...

Will create a JOINs on tables www and site. Clearly that's not what we want. The solution is to ignore quoted strings in the Result::createJoins method.

before line 90 in Result.php add:

$val = preg_replace('~\'[^\']+\'~',"'dummy'",$val);
$val = preg_replace('~\"[^\"]+\"~','"dummy"',$val);

These could be combined into one regex but I don't think it would be faster. You could probably also change the preg_match_all on line 90 to achieve the same affect.

$orm = new NotORM($pdo, null, new NotORM_Cache_Session());

    ?><pre><?php

    foreach($orm->lean_route()->order('lean_controller.name')
        as /*@var $route NotORM_Row */ $route)
    {
        echo 'route_id:' . $route['id']
            . ' and controller '. $route->lean_controller['name']  //Exception occurs here when caching
            . PHP_EOL;
    }

CREATE TABLE `lean_controller` (
  `id` INT(10) UNSIGNED NULL AUTO_INCREMENT,
  `name` VARCHAR(512) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `lean_route` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `url` VARCHAR(255) NOT NULL,
  `lean_controller_id` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url-controller_id` (`url`, `lean_controller_id`),
  CONSTRAINT `lean_controller_id` FOREIGN KEY (`lean_controller_id`) REFERENCES `lean_controller` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

[exception trace info]

(exception) Undefined index: lean_controller_id

+0.011938 (exception) Undefined index: lean_controller_id
#0 /var/www/lean/sys/vendor/notorm/NotORM/Row.php(159): {closure}(8, 'Undefined index...', '/var/www/lean/s...', 159, Array)
#1 /var/www/lean/sys/vendor/notorm/NotORM/Row.php(32): NotORM_Row->offsetGet('lean_controller...')
#2 /var/www/lean/index.php(49): NotORM_Row->__get('lean_controller')
#3 {main}

/var/www/lean/sys/class/app.php:22 - Debug::message
/var/www/lean/index.php:65 - App::showException

+0.011722 Query:

SELECT
lean_route.*
FROM
lean_route
LEFT JOIN lean_controller ON lean_route.lean_controller_id = lean_controller.id
ORDER BY
lean_controller.name

/var/www/lean/mod/database/class/pdo/wrapper.php:7 - Debug::message
/var/www/lean/sys/vendor/notorm/NotORM/Result.php:158 - PDOWrapper::prepare
/var/www/lean/sys/vendor/notorm/NotORM/Result.php:580 - NotORM_Result::query
/var/www/lean/sys/vendor/notorm/NotORM/Result.php:725 - NotORM_Result::execute
/var/www/lean/sys/vendor/notorm/NotORM/Row.php:118 - NotORM_Result::offsetGet
/var/www/lean/sys/vendor/notorm/NotORM/Row.php:155 - NotORM_Row::access
/var/www/lean/sys/vendor/notorm/NotORM/Row.php:32 - NotORM_Row::offsetGet
/var/www/lean/index.php:49 - NotORM_Row::__get

+0.011054 Query:

SELECT
lean_route.id,
lean_route.url
FROM
lean_route
LEFT JOIN lean_controller ON lean_route.lean_controller_id = lean_controller.id
ORDER BY
lean_controller.name

/var/www/lean/mod/database/class/pdo/wrapper.php:7 - Debug::message
/var/www/lean/sys/vendor/notorm/NotORM/Result.php:158 - PDOWrapper::prepare
/var/www/lean/sys/vendor/notorm/NotORM/Result.php:580 - NotORM_Result::query
/var/www/lean/sys/vendor/notorm/NotORM/Result.php:674 - NotORM_Result::execute
/var/www/lean/index.php:46 - NotORM_Result::rewind

Hi there, first of all I must say I LOVE NOTORM, it is just a great tool!
I found a little bug today when I was trying to count table's rows.
I have got table (lets name it A) referencing to table (lets name it B).
Now, when I am doing:
foreach($db->B as $row) {
echo $row->A->count('*');
}
It gives an error when there are zero columns referencing to table B.
Even so, when I change it to count() (which is probably less efficient) it did work.

Hope that was helpful,
Shay

In case I want to get particular user's classroom list from joining table, this returns error:

$result = $this->db->user_classroom->fetchPairs("classroom.id");

Undefined index: classroom.id

This would fix it (last foreach in fetchPairs(). But is it ok?

foreach ($clone as $row) {
    $values = array_values(iterator_to_array($row));
    $return[$values[0]] = ($value != "" ? $values[(isset($values[1]) ? 1 : 0)] : iterator_to_array($row));
}

Hi,

It'll be super useful to insert multiple rows by array and not with arguments,
look at this example:
21, 'name'=>'PHP'); $tags[1] = array('id'=>22, 'name'=>'MySQL'); $tags[2] = array('id'=>23, 'name'=>'Javascript'); $db->tags()->insert($tags); ?>
for now we can only insert rows by passing them as arguments:
tags()->insert($tags[0],$tags[1], $tags[2]); ?>

this not very useful, when your rows created in code.

I am using NotORM as managed by Composer with PHP 5.3.10. Commit a3cec36 seems to break for me with the following error and stack trace:

PHP Fatal error: Cannot redeclare class NotORM in /var/www/vendor/vrana/notorm/NotORM.php on line 101
PHP Stack trace:
PHP 1. {main}() /var/www/public/index.php:0
PHP 2. require_once() /var/www/public/index.php:2
PHP 3. ComposerAutoloaderInitad12cee8065d7226d3ed513a93485bbd::getLoader() /var/www/vendor/autoload.php:7
PHP 4. require() /var/www/vendor/composer/autoload_real.php:41
PHP 5. interface_exists() /var/www/vendor/vrana/notorm/NotORM.php:10
PHP 6. Composer\Autoload\ClassLoader->loadClass() /var/www/vendor/composer/ClassLoader.php:0

bd5db46 works for me wonderfully.

Hi,

There seems to be a problem with left joining, when using the convention structure. The prefix was not removed from the column name, which suppose to be a must.

Take a look this sample tables below:

• user : id, name, email
• question : id, user_id, text, html
• answer : id, user_id, question_id, text, html

If I do this call, then it will return the left join with foreign column being referenced together with table prefix:
$db->question()->where('answer:user_id = ?', 1);

I fix this with:

protected function getColumnFromTable($name) {
    if ($this->table != '%s' && preg_match('(^' . str_replace('%s', '(.*)', preg_quote($this->table)) . '$)', $name, $match)) {
        return $match[1];
    }
    return substr($name, strlen($this->prefix)); // FIX: Remove Unnecessary Prefix
}

I haven't tested further, but I assume it might trigger abnormal behavior. Would you mind taking a look into this?

Thanks.
Wendy

I believe it's currently not possible to execute PDO's query via NotORM. As a workaround, I have to store an instance of PDO before passing it over to NotORM during its initialization.
Or I have to define a class that extends NotORM and provides access to $this->connection.

Query() comes handy when you need to switch databases, optimize tables, run a batch of SQL commands or generally speaking, do things that NotORM can't do.

Thanks
Petr

I've had problems using via() in my project which led me to your tests. The test for via() appeared to be working until I removed author_id attribute from the database.

Then I found an easier way to prove that the test is a bogus. I enabled query logging by removing your comment in connect.inc.php ($software->debug = true;).

This is the result:

php -f run-tests.php
12-via.phpt:7:SELECT * FROM author;
12-via.phpt:8:SELECT * FROM application WHERE (application.author_id IN (11, 12));
0.012 s, 974 KiB

Please notice that although the test code asks for maintainer_id to be used, author_id is used instead.

foreach ($software->author() as $author) {
    foreach ($author->application()->via("maintainer_id") as $application) {
        echo "$author[name]: $application[title]\n";
    }
}

Here's a very ugly workaround for the via() bug:

function via($column) {
  // workaround begin
  $oldCondition = $this->table . "." . $this->column . " IN " . $this->quote(array_keys((array) $this->result->rows));
  $newCondition = $this->table . "." . $column . " IN " . $this->quote(array_keys((array) $this->result->rows));
  foreach ($this->where as &$condition) {
    if ($condition == $oldCondition) {
      $condition = $newCondition;
      break;
    }
  }
  // workaround end
  $this->column = $column;
  return $this;
}

Example:

MySql testing data:

mysql> create database notormtest;
mysql> use notormtest;
mysql> create table fetchtest (id int not null auto_increment primary key, data1 int, data2 varchar(10));
mysql> insert into fetchtest (data1, data2) values (1, 'one'), (null, 'null'), (3, 'three');
mysql> select * from fetchtest;
+----+-------+-------+
| id | data1 | data2 |
+----+-------+-------+
|  1 |     1 | one   |
|  2 |  NULL | null  |
|  3 |     3 | three |
+----+-------+-------+
mysql> grant all to notormtest.* on 'notormtest'@'localhost';

PHP code:

require_once(dirname(__FILE__).'/NotORM.php');
$pdo = new PDO("mysql:dbname=notormtest", 'notormtest');
$db = new NotORM($pdo);

$data = array_map('iterator_to_array', $db->fetchtest()->fetchPairs('id'));
print_r($data);

$data = $db->fetchtest()->fetchPairs('id', 'data1');
print_r($data);

Output after executing php code:

Array
(
    [1] => Array
        (
            [id] => 1
            [data1] => 1
            [data2] => one
        )

    [2] => Array
        (
            [id] => 2
            [data1] => 
            [data2] => null
        )

    [3] => Array
        (
            [id] => 3
            [data1] => 3
            [data2] => three
        )

)
Array
(
    [1] => 1
    [2] => 2
    [3] => 3
)

If second parametr of fetchPairs method is specified NULL is transfered to '1'.

echo $product->product_element()->count('*') . "\n";
echo $product->product_element()->delete() . "\n";
echo $product->product_element()->count('*') . "\n";

...vypíše 13, 13, 13, kdežto

echo $this->db->product_element()->where('productId', $product['id'])->count('*') . "\n";
echo $this->db->product_element()->where('productId', $product['id'])->delete() . "\n";
echo $this->db->product_element()->where('productId', $product['id'])->count('*') . "\n";

...vypíše 13, 13, 0. Jakto? Mám pullnutou nejnovější revizi.

hi Jakub,

at first - I'm not PHP user so I don't understand PDO internals etc. But now I'm asked to evaluate a design of an application which would potentially use NotORM with Oracle 11.2.0.3. The Oracle server is used for many applications while this PHP one would serve for read only quick overview display for ~50 users. No big deal.

While I studied NoORM source code briefly, playing with your example, I discovered one critical thing (from Oracle DB point of view). NotORM statemens are generated as "raw" statements - eg. in Result.php:

$condition .= " IN (" . implode(", ", $in) . ")";

resulting in statements like eg.:

SELECT ... FROM a_table WHERE (foo IN ('1', '2', '3'));
SELECT ... FROM a_table WHERE (foo IN ('6', '7', '8'));
SELECT ... FROM a_table WHERE (foo IN ('12', '222', '666'));
etc.

Which is very bad statement for Oracle DB. It's because of Oracle's architecture. It uses so called "Shared Pool" where are statements kept as in cache to avoid "Hard Parsing" of statements which is extremely costy.

So as this approach can work in small environment it would be killing in high volume OLTP processing.

Please check some classic articles like:
http://www.akadia.com/services/ora_bind_variables.html
http://psoug.org/reference/bindvars.html
...

I know it's NotORM approach to be used like this. So just add this note (or similar) to documentaion, please, to avoid PHP developers screwing my systems ;)

thanks

nebolo by odveci keby order() umoznoval zrusenie - tj ak zadam NULL alebo FALSE tak zrusi vsetky order-y a umozni tak nadefinovat nove

Hello,
First of all I most say thank you for this great class.
I'm trying to make a query with two "Where" like that:
$db->table1()->where('last_update > 0 and last_update <= (UNIX_TIMESTAMP()-((avg_days+0.5)*60*60*24))');
And I get the following SQL statement:
SELECT table1.* FROM table1 INNER JOIN 0 ON table1.0_id = 0.id WHERE (last_update > 0 and last_update <= (UNIX_TIMESTAMP()-((avg_days+0.5)*60*60*24)))
It shouldn't insert any JOIN in here.
I went to NotORM/Result.php at line 83 and changed it to:
if ($name != $this->table and $name != 0) { // case-sensitive
And it seems to be okay.
But what is the reason for that?

Thanks,
Bnaya Livne

Getting a single row
$application = $db->application[1]; // get by primary key
$application = $db->application("title = ?", "Adminer")->fetch();

První případ:
funguje jen když se primární klíč jmenuje "id". Vygenerovaný dotaz používá zatvrzele "id", i když se primární klíč jmenuje jinak.

Druhý případ:
$db->storage("name = ?", 'access_token')->fetch(); vygeneruje query "SELECT * FROM storage WHERE (name = ?)" Otazník se tedy nenahradí.

Ahoj,
na webu tahám data do radiolistu pomocí následujícího kódu:
$materials = $this->context->createMaterial()->fetchPairs('cena', 'nazev');

Problém je v tom, že cena v db je se setinami (např. 0.85) a po dumpnutí (zkontrolování) hodnot, které mají být v radiolistu jsou takovéto hodnoty zaokrouhleny na nulu. Desetinná místa (např. 0.9) to nikam nezaokrouhlí.

Při výpisu dat z db je vše v pořádku a čísla mají normálně setiny.

Kde by prosím mohla být chyba?

Hi, im trying to use your db management script, when I try to write paginator I discover some problems :

    $products = $this->db->product()->where('category_id = 2')->limit(3,3);
    echo get_class($products);
    foreach($products as $p) {
        echo $p['id'].' '.$p['name'].'<br />';
    }

This code outputs :

NotORM_Result
string 'SELECT * FROM product WHERE (category_id = 2') LIMIT 3 OFFSET 3' (length=62)
5 ghgh
6 frgfrgt
7 multi photo

And this is working Just fine, but if I try another way like :
$prod = $this->db->category[2]->product()->limit(3,3);
echo get_class($prod);
foreach($prod as $p)
echo $p['id'] . ' ' . $p['name'] . '
';

Im getting :

string 'SELECT * FROM category WHERE (id = '2')' (length=39)
NotORM_MultiResult
string 'SELECT * FROM product WHERE (product.category_id IN ('2')) LIMIT 3 OFFSET 3' (length=75)

and no data no errors nothing, I think where is some problem with offset because if I set ->limit(3) I'm getting data

Please have a look at this tests:
http://tmp.pepa.info/notorm/33-failing-join-group.phpt

It joins tables application and author and groups by title and author_id. The executed query is correct:

FROM application 
LEFT JOIN author ON application.author_id = author.id 
GROUP BY title
ORDER BY title;

However, the result is not.
--FILE--
JUSH: Jakub Vrana
Nette: David Grudl
--EXPECTF--
Adminer: Jakub Vrana
Dibi: David Grudl
JUSH: Jakub Vrana
Nette: David Grudl

Edit: I just found out that the problem is in fact in select("title, author.*") and not in group() as I had though. I changed the ticket title to reflect it.

The test passes when I replace 'author.*' in the select() call with 'author_id' and use $row->author when printing the author name.
See here: http://tmp.pepa.info/notorm/33-working-join-group.phpt

can you add ->fetchKey('some_key') it's will be similar to fetchPairs but embed all row
function fetchKey($key) {
$return = array();
foreach ($this as $row) {
$return[$row[$key]] = $row;
}
return $return;
}

Now I can overcome the missing joins functionality
$leads = $nORM->leads()->where($criteria)->order('leads.id DESC');
$leads_data = $nORM->leads_data()->where('lead_id', $leads)->fetchKey('lead_id');

If I have 2 tables with the same column name and use them together as a where condition, then it will triggers ambigue error from MySql.

Take a look this sample tables below:

• user : id, name, email
• question : id, user_id, text, html
• answer : id, user_id, question_id, text, html

If I do this call, then it will trigger the error:
$db->question()->where('answer:user_id = ?', 1)->where('user_id = ?', 2);

The first where statement will be generated with the right table prefix, while the second where statement will be generated without any prefix and causes ambiguity.

EDIT: I revised the notation to use semicolon, instead of dot, for referencing column from question to answer.

While the debug property can be used to log queries, there is no way to log the information that transaction has begun, has commited or that it has been rolled back.

$orm->debug = true;//'dump';
NotORM/Result.php Line: 106

Use of undefined constant STDERR - assumed 'STDERR'

Hi, unfortunately the DBLIB extension is not available anymore on Windows with PHP 5.3 or later. :-(
http://www.php.net/manual/en/ref.pdo-dblib.php

and the other two drivers avaiable doesn't accept NotORM's "limit" !!! Help !!!

example:

require_once 'NotORM.php';

$pdo = new PDO("mssql:host=localhost,1433;dbname=notorm", 'sa', 'test');
//$pdo = new PDO("sqlsrv:Server=localhost;Database=notorm", 'sa', 'test'); // same behaviour
$db = new NotORM($pdo);

$articles= $db->article()->limit(2);

echo 'query: '.$articles."\n";
// query: SELECT * FROM article LIMIT 2 OFFSET 0

foreach($articles as $a)
 echo $a['title']."\n";

// nothing displayed...

I fetched a single row from a table with non-standard ID naming. When I try to fetch a related column from another table, I tried to specify the foreign key column name using via('ColumnName'). However the end result of the query would have a where clause with the wrong names in it.
After some investigation the where clause was added right after the creation of the MultiResult instance which was returned by the call.
$return->where("$table.$column", array_keys((array) $this->result->rows));
There is a problem here because via() is called after this so $column in the where clause is always an incorrect value no matter via was set or not.

Hello , I need some help please.
I need to select the most repeated value in a table!!!
I thought that I can use order and count together but it doesn't work :S

I try to use NotORM with composer autoload but don't work. It use namespaces?

Hello,

i'm trying this ( with "debug" option set to TRUE) :

$postList = $db->post()->where('date >= ?', 1363906800);

the SQL output is :

SELECT * FROM post WHERE (date >= ?)
Array ( [0] => 1363906800 )

I would think the output should be :

SELECT * FROM post WHERE (date >= 1363906800 )

thanks by advance for any comment

Sorry: this works fine (with the double quote):
$postList = $db->post()->where("date >= ?", 1363906800);

Prefixovani tabulke je bohuzel udelano nedokonale, pri sestaveni spojovaciho sloupce se metode getReferencingColumn respektive getReferencedColumn predava jiz tabulka oprefixovana, takze vysledny prefix "ziska" i spojovaci sloupec, coz je samozrejme spatne.

Zde: https://github.com/vrana/notorm/blob/master/NotORM/Row.php#L66 se predava dany oprefixovany tablename, bohuzel nevim, jak to koncepcne opravit, zatim se v notorm moc nevyznam.

Hi,

i was triying to insert_update on a referenced table and seems that when inserted it does not use the reference

content 1-------many content_language

    $onecontent = $db->content[10];
    $onecontent->content_language()->insert(array('alias'=>'atest'));  // working, resulting row is referencing the content 10
    $contenidos->content_language()->insert_update(array('alias'=>'anothertest'), array('title'=>'anothertest', 'alias'=>'anothertest'));    // not working

Any intent in adding manual join chain?
->join('tbl ON X=Y')
??

in NotORM/Result.php, line 453

This loop uses a function. To avoid the overhead of executing the function n times, you should precalculate it before the loop.

                if ($nulls) {
                    $condition = "$condition OR $column IS NULL";
                }
            } else { // http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions014.htm
                $or = array();
                               $numParameters = count($parameters);
                for ($i=0; $i < $numParameters; $i += 1000) {
                    $or[] = "$condition IN " . $this->quote(array_slice($parameters, $i, 1000));
                }
                $condition = implode(" OR ", $or);
            }
        }

Posted from SensioLabsInsight

Hi Jakub, commit 3419027 broke my code. I use NotORM with language extension inspired by (http://php.vrana.cz/co-je-noveho-v-notorm.php). I have text fields translated in second table (e.g. article and article_translation(article_id, lang, title,....)). After I applied "git pull" this commit on this code:

foreach ($notorm->article() as $article) {
    echo "$article[id], $article[title]\n";
}

I got this error:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

File: /home/marek/www/new/libs/notorm/NotORM/Result.php   Line: 157

155:            }
156:            $return = $this->notORM->connection->prepare($query);
157:            if (!$return || !$return->execute(array_map(array($this, 'formatValue'), $parameters))) {
158:                return false;
159:            }
160:            return $return;
161:        }

Do you know where the problem is?
thanks
Marek.

The problem is that, when I try to fetch content from a NotORM_MultiResult, there could be problems for repeated fetching. For example,

$owner = $db->owner[1]; $items1 = $owner->item(); $item1 = $items->fetch(); $items2 = $owner->item(); $item2 = $items->fetch();
Here count($items1) is 1.
What I was expecting (as with NotORM_Result) is that I would have something meaningful for $item1 and $item2, but apparently $items1 and $items2 shares the same pile of data such that $item2 would false.

Hi,
I'm using the latest master,

consider the following:
$rows = $nORM->any_table()->where('field',5);
print json_encode($rows);

this will not work, as the query is not executed,
I need to loop all the elements and build the array then pass it to php function.

even if I force execution with count($rows), json_encode, still can't read the results and returns an empty results.

is there any solution for this?

$pages = db->pagesdescription('pagesdescription.languages_id',1)->aggregation('pagesdescription.*,pages.user_id,pages.created');
var_dump($pages);
And this produces nice query, which works when I paste it to phpmyadmin, but var_dump returns string '1' (length=1)

I think you should modify NotORM_Abstract class to let us get the pdo connection through it, so that we can use only the NotORM object even if we need to the pdo connection.

It should be something like that:

abstract class NotORM_Abstract {
    protected $connection, $driver, $structure, $cache;
    protected $notORM, $table, $primary, $rows, $referenced = array();
    
    protected $debug = false;
    protected $freeze = false;
    protected $rowClass = 'NotORM_Row';
    
    abstract protected function __construct();
    
    protected function access($key, $delete = false) {
    }
    
    /** Get PDO connection
    * @return PDO
    */
    function connection() {
        return $this->connection;
    }
    
}

By the way I love NotORM, thanks for creating it :)

Yours,
Shay Ben Moshe

Mám objekt Product, který v sobě uchovává adekvátní NotORM_Row (dejme tomu $this->databaseRow, DatabaseRow je poděděná třída). Později chci udělat toto:

$this->databaseRow->product_element()

...jenže to vyhodí prázdný result, ačkoliv v databázi nějaké takové elementy jsou. Když dumpnu objekt, vypadá to nějak takto:

NotORM_MultiResult(35) {
   "result" private => NotORM_Result(32) {
      "single" protected => TRUE
      "select" protected => array(0) 
      "conditions" protected => array(1) [
         0 => "id" (2)
      ]
      "where" protected => array(1) [
         0 => "id = '156378'" (13)
      ]
      "parameters" protected => array(0) 
      "order" protected => array(0) 
      "limit" protected => NULL
      "offset" protected => NULL
      "group" protected => ""
      "having" protected => ""
      "union" protected => array(0) 
      "unionOrder" protected => array(0) 
      "unionLimit" protected => NULL
      "unionOffset" protected => NULL
      "data" protected => array(1) {
         156378 => Javorek\Model\DatabaseRow(15) { ... }
      }
      "referencing" protected => array(1) {
         "SELECT * FROM product_element WHERE (product_element.product_id IN (156378))" => array(1) { ... }
      }
      "aggregation" protected => array(0) 
      "accessed" protected => NULL
      "access" protected => array(1) {
         "id" => TRUE
      }
      "keys" protected => array(0) 
      "connection" protected => NULL
      "driver" protected => NULL
      "structure" protected => NULL
      "cache" protected => NULL
      "notORM" protected => NotORM(12) {
         "connection" protected => Javorek\Model\DatabaseConnection(1) { ... }
         "driver" protected => "mysql" (5)
         "structure" protected => NotORM_Structure_Convention(3) { ... }
         "cache" protected => NULL
         "notORM" protected => NULL
         "table" protected => NULL
         "primary" protected => NULL
         "rows" protected => NULL
         "referenced" protected => array(0) 
         "debug" protected => array(2) [ ... ]
         "freeze" protected => TRUE
         "rowClass" protected => "Javorek\Model\DatabaseRow" (25)
      }
      "table" protected => "product" (7)
      "primary" protected => "id" (2)
      "rows" protected => array(1) {
         156378 => Javorek\Model\DatabaseRow(15) { ... }
      }
      "referenced" protected => array(1) {
         "project" => NotORM_Result(32) { ... }
      }
      "debug" protected => FALSE
      "freeze" protected => FALSE
      "rowClass" protected => "NotORM_Row" (10)
   }
   "column" private => "product_id" (10)
   "active" private => "156378" (6)
   "single" protected => FALSE
   "select" protected => array(0) 
   "conditions" protected => array(1) [
      0 => "product_element.product_id" (26)
   ]
   "where" protected => array(1) [
      0 => "product_element.product_id IN (156378)" (38)
   ]
   "parameters" protected => array(0) 
   "order" protected => array(0) 
   "limit" protected => NULL
   "offset" protected => NULL
   "group" protected => ""
   "having" protected => ""
   "union" protected => array(0) 
   "unionOrder" protected => array(0) 
   "unionLimit" protected => NULL
   "unionOffset" protected => NULL
   "data" protected => NULL
   "referencing" protected => array(0) 
   "aggregation" protected => array(0) 
   "accessed" protected => NULL
   "access" protected => NULL
   "keys" protected => array(0) 
   "connection" protected => NULL
   "driver" protected => NULL
   "structure" protected => NULL
   "cache" protected => NULL
   "notORM" protected => NotORM(12) {
      "connection" protected => Javorek\Model\DatabaseConnection(1) {
         "convertIdentifiers" private => TRUE
      }
      "driver" protected => "mysql" (5)
      "structure" protected => NotORM_Structure_Convention(3) {
         "primary" protected => "id" (2)
         "foreign" protected => "%s_id" (5)
         "table" protected => "%s" (2)
      }
      "cache" protected => NULL
      "notORM" protected => NULL
      "table" protected => NULL
      "primary" protected => NULL
      "rows" protected => NULL
      "referenced" protected => array(0) 
      "debug" protected => array(2) [
         0 => "Javorek\Model\DatabaseModel" (27)
         1 => "debug" (5)
      ]
      "freeze" protected => TRUE
      "rowClass" protected => "Javorek\Model\DatabaseRow" (25)
   }
   "table" protected => "product_element" (15)
   "primary" protected => "id" (2)
   "rows" protected => NULL
   "referenced" protected => array(0) 
   "debug" protected => FALSE
   "freeze" protected => FALSE
   "rowClass" protected => "NotORM_Row" (10)
}

Vidím tam SQL dotaz, tedy

SELECT * FROM product_element WHERE (product_element.product_id IN (156378))

...ten jsem zkoušel normálně přes Adminer a vyhodí správný set výsledků. Nechápu co se děje, ale stálo mě to dnes hrozně moc vlasů :( Nějak víc do hloubky to ladit nemůžu (čas), ale pokud by bylo třeba ještě něco poskytnout, můžu se pokusit.

EDIT1: Jinak když dumpnu ten samotný $this->databaseRow, vypadá takto:

Javorek\Model\DatabaseRow(15) {
   "modified" private => array(0) 
   "row" protected => array(4) {
      "id" => "156378" (6)
      "project_id" => "13" (2)
      "variant_of" => NULL
      "hidden" => "0"
   }
   "result" protected => NotORM_Result(32) {
      "single" protected => TRUE
      "select" protected => array(0) 
      "conditions" protected => array(1) [
         0 => "id" (2)
      ]
      "where" protected => array(1) [
         0 => "id = '156378'" (13)
      ]
      "parameters" protected => array(0) 
      "order" protected => array(0) 
      "limit" protected => NULL
      "offset" protected => NULL
      "group" protected => ""
      "having" protected => ""
      "union" protected => array(0) 
      "unionOrder" protected => array(0) 
      "unionLimit" protected => NULL
      "unionOffset" protected => NULL
      "data" protected => array(1) {
         156378 => Javorek\Model\DatabaseRow(15) { *RECURSION* }
      }
      "referencing" protected => array(1) {
         "SELECT * FROM product_element WHERE (product_element.product_id IN (156378))" => array(1) { ... }
      }
      "aggregation" protected => array(0) 
      "accessed" protected => NULL
      "access" protected => array(1) {
         "id" => TRUE
      }
      "keys" protected => array(0) 
      "connection" protected => NULL
      "driver" protected => NULL
      "structure" protected => NULL
      "cache" protected => NULL
      "notORM" protected => NotORM(12) {
         "connection" protected => Javorek\Model\DatabaseConnection(1) { ... }
         "driver" protected => "mysql" (5)
         "structure" protected => NotORM_Structure_Convention(3) { ... }
         "cache" protected => NULL
         "notORM" protected => NULL
         "table" protected => NULL
         "primary" protected => NULL
         "rows" protected => NULL
         "referenced" protected => array(0) 
         "debug" protected => array(2) [ ... ]
         "freeze" protected => TRUE
         "rowClass" protected => "Javorek\Model\DatabaseRow" (25)
      }
      "table" protected => "product" (7)
      "primary" protected => "id" (2)
      "rows" protected => array(1) {
         156378 => Javorek\Model\DatabaseRow(15) { *RECURSION* }
      }
      "referenced" protected => array(1) {
         "project" => NotORM_Result(32) { ... }
      }
      "debug" protected => FALSE
      "freeze" protected => FALSE
      "rowClass" protected => "NotORM_Row" (10)
   }
   "connection" protected => NULL
   "driver" protected => NULL
   "structure" protected => NULL
   "cache" protected => NULL
   "notORM" protected => NULL
   "table" protected => NULL
   "primary" protected => NULL
   "rows" protected => NULL
   "referenced" protected => array(0) 
   "debug" protected => FALSE
   "freeze" protected => FALSE
   "rowClass" protected => "NotORM_Row" (10)
}

EDIT2: Nový poznatek je, že pokud si to nechám vypsat v šabloně poprvé, vyhodí mi to správný počet elementů. Pokud to udělám podruhé hned o řádek níže, vyhodí 0. Takže opět nějaká zákeřná cache? :(

MySQL: create table bit_test ( my_column BIT );

Values of my_column must be wrapped in quotes otherwise MySQL rejects the resulting dump with a syntax error message. Compare dump made with mysqldump and Adminer.

Just like the title, Can NotORM handle this?

Hello,

as title says, using extra parameters in ->search() clause of UNION causes Invalid parameter number: number of bound variables does not match number of tokens. Failing example:

$term = 'Brno';
$termRE = '[[:<:]]' . preg_quote($term);

$search = $notorm->location
    ->select(new \NotORM_Literal("
        location.name AS name,
        IF(STRCMP(location.name, ?), 0, 1) AS equal_to_term
    ", $term))
    ->where('location.name REGEXP ?', $termRE)
    ->group('location.name')
    ->union($notorm->venue
        ->select(new \NotORM_Literal("
            venue.city AS name,
            IF(STRCMP(venue.city, ?), 0, 1) AS equal_to_term
        ", $term))
        ->where('venue.city REGEXP ?', $termRE)
        ->group('venue.city')
    )
    ->order('equal_to_term DESC')
    ->limit(10);

foreach ($search as $place) {
    echo $place['name'] . '<br>';
}

Results in generating this:

SELECT location.name AS name IF(STRCMP(location.name, ?), 0, 1) AS equal_to_term FROM location WHERE (location.name REGEXP ?) GROUP BY location.name) UNION (SELECT venue.city AS name, IF(STRCMP(venue.city, ?), 0, 1) AS equal_to_term FROM venue WHERE (venue.city REGEXP ?) GROUP BY venue.city) ORDER BY equal_to_term DESC LIMIT 10

array(3) [
    0 => "Brno" (4)
    1 => "[[:<:]]Brno" (11)
    2 => "[[:<:]]Brno" (11)
]

All works when UNION clause is dropped from the query. Workaround is using already quoted parameters:

$qTerm = $pdo->quote($term);
...
 ->search("venue.city AS name IF(STRCMP(venue.name, $qTerm), 0, 1) AS equal_to_term")
...

Thanks for fixing.