morris / lessql Goto Github PK

In this example, you render the inbetween results, but in modern frameworks, you first collect the data, and then hand over all the necessary data to a template engine to render the (end) result.

So the following steps will be visible:
get posts and their users and the corresponding categories
call a rendering routine

I was figuring out myself how to do it, but perhaps there is a better way.
My steps in pseudo-code:
create array $allRecords
get all postsRecords
loop over $postsRecords
per $postRecord,

• get corresponding user
• add the $userRecord to the $postsRecord
• get the corresponding $categoryRecords
• add the $categoryRecords to the $postsRecord
• put the modified $postRecord into the $allRecords
  endloop

renderthis($allRecords)

Hello,
How can I do a select using group by statement?

Wouldn't it be useful to return a \LessQL\Row instead of a \PDOStatement when inserting a row?

If I need to execute a raw SQL query. How can I do it?

$db = new \LessQL\Database($pdo);
$Post = $db->post();
if ($limit) {
    $Post->limit($limit);
}
if ($order) {
    $Post->orderBy($order, 'desc');
}

// ...

but it's not work

hi there,

I would like to know if there is a way to set a BETWEEN condition when using where() function ?
I tried to use limit(), but its not precise enough..

Maybe someone already found a solution ?
Thanks

Hi! Well, I think that it would be nice to mention that somewhere.

Hello there,

Nice Work morris !

Today, meanwhile my integration with PDO instance with SQL Server. i noticed than the delimited identifiers string and method database {Set | Get identifier} are not useful for this case.

So, the right sql statement is ;
SELECT * FROM [YourTable] ;
Not
SELECT * FROM YourTable ;

The problem is that we can not use database::quoteIdentifier( CASE ), because we should be use this delimiter "[" in the beggining and this second delimiter "]" at the end.

I know it's a simple interface with PDO. but I find it appropriate to add a Driver Manager Before create statement.

$DriverPDO = $pdo->getAttribute( PDO::ATTR_DRIVER_NAME ) // === 'sqlsrv'

Good continuation !

I log queries with setQueryCallback() nut we only have the queries, is it possible to get the query time execution?

perhaps in the $params argument?

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected' in D:\usbwebserver\root\vendor\morris\lessql\src\LessQL\Database.php:662 Stack trace: #0 D:\usbwebserver\root\vendor\morris\lessql\src\LessQL\Database.php(662): PDOStatement->execute() #1 D:\usbwebserver\root\vendor\morris\lessql\src\LessQL\Database.php(571): LessQL\Database->insertDefault('sn_users', Array) #2 D:\usbwebserver\root\vendor\morris\lessql\src\LessQL\Result.php(409): LessQL\Database->insert('sn_users', Array, NULL) #3 D:\usbwebserver\root\vendor\morris\lessql\src\LessQL\Row.php(304): LessQL\Result->insert(Array) #4 D:\usbwebserver\root\vendor\morris\lessql\src\LessQL\Row.php(345): LessQL\Row->save(false) #5 D:\usbwebserver\root\reg.php(18): LessQL\Row->save() #6 {main} thrown in D:\usbwebserver\root\vendor\morris\lessql\src\LessQL\Database.php on line 662

<?php
require_once 'vendor/autoload.php';

$pdo = new \PDO( 'mysql:sn_db' );
$db = new \LessQL\Database( $pdo );

$username = $_POST["name"];
$usersurname = $_POST['surname'];
$password = $_POST['password'];
/////////////////////////////////////////
$row = $db->createRow('sn_users', array(
	'name' => $username,
	'surname' => $usersurname,
	'password' => $password,
));
//////////////////////////////////////////
$db->begin();
$row->save();
$db->commit();
?>

Why? What is wrong?

In the Finding and Traversal, you end with the first example with 4 queries.
I would like to see what code I must use to see if my example has the expected sql queries.

I have this code:

$dataRecords = $db->my_table()
                ->orderBy(['name' => 'ASC', 'age' => 'DESC']);

But I got a complain from the quoteIdentifier method, because the identifier must be a string.

I tried, but this fails also:

$dataRecords = $db->my_table()
                ->orderBy(['name, ASC']);

Any hints are welcome

I have a many to many relationship and I'm using foreign keys in the singular:

- orders
- products
- order_has_products

order_has_products.order_id -> orders.id
order_has_products.product_id -> products.id

When I use setReference to try to configure this relationship it works only in one sense but not in the other:

$db->setReference('order_has_products', 'orders', 'order_id');

// this works
$db->order_has_products([
    'order_id' => 1000,
    'product_id' => 200
])->orders();

// this doesn't work
$db->order(1000)->order_has_productsList()->fetchAll();

// but this works
$db->order(1000)->order_has_productsList()->via('order_id')->fetchAll();

Shouldn't it work without via() ? Am I doing something wrong or this functionality is not supported?

hours
+---------+-------------+--------+------+---------+-------+
| hourID | Date | worked | sick | jobID | ect |
+---------+-------------+--------+------+---------+-------+
| 1 | 2009-10-01 | 8 | 10 | 1 | NULL |
| 2 | 2009-10-01 | 10 | 8 | 2 | NULL |
| 3 | 2009-10-01 | 0.0 | 6 | 1 | NULL |
| 4 | 2009-10-01 | 8 | 5 | 1 | NULL |
| 5 | 2009-10-01 | 9 | 8 | 2 | NULL |
| 6 | 2009-10-01 | 10 | 1 | 3 | NULL |
+---------+-------------+--------+------+---------+-------+

jobs
+---------+-------------+------------+-------+---------+-------+
| jobID | startDate | endDate | price | etc | ect |
+---------+-------------+------------+-------+---------+-------+
| 1 | 2009-10-01 | 2020-10-01 | 20 | NULL | NULL |
| 2 | 2009-10-01 | 2020-10-01 | 21 | NULL | NULL |
| 3 | 2009-10-01 | 2020-10-01 | 22 | NULL | NULL |
| 4 | 2009-10-01 | 2020-10-01 | 20 | NULL | NULL |
| 5 | 2009-10-01 | 2020-10-01 | 20 | NULL | NULL |
| 6 | 2009-10-01 | 2020-10-01 | 15 | NULL | NULL |
+---------+-------------+------------+-------+---------+-------+

I have a very large database with lots of hour records. With SUM I manage to quickly retrieve the total of different hour types.
like this:
$hoursTotal = $this->db->hours()->where('date', $date)->sum('worked + sick');

I would like to get 'worked + sick' * price via the jobID relation and retrieve the sum of the calculation by sql.

If u use $hours->jobs()->via('jobID')->fetch() and do the calculation in php the query is too big and takes too long to run.

Can anyone help me?

Hello,
i'm working with lessql on a project and i've found that when you add a condition as an array ( to make an "IN" condition ) and you have a null value in your array, the result will be column IN (values) OR column IS NULL. If you have more than one condition like this, your query can be not as you meant it, eg.
column1 IN (1,2) OR column1 IS NULL AND column2 IN (3,4) OR column2 IS NULL
instead you wanted it like this
( column1 IN (1,2) OR column1 IS NULL) AND (column2 IN (3,4) OR column2 IS NULL)

so i believe this can be accomplished by changing line

like this:
$suffix .= " WHERE (" . implode(") AND (", $where).")";

sorry for my poor english.

How can I make query like order by rand() or order by rank1+rank2

I would like to compose a query this way:

$query = $orm->table();
              ->where('foo_id', $foo['id'])
              ->like('foo_name', '%DP') 
              ->fetch();

it's possible?

Hi,

Is it possible to set a default encoding or use UTF8 to retrieve database items.
When I use json_encode($result); It doesn't work on content with special characters.

@rubenvincenten suggested adding auto-discovery of keys for engines like InnoDB that support meta-information.

Following the example on the guide page, how would you retrieve posts that belongs to one or more specific category?

user: id, name
post: id, title, body, date_published, is_published, user_id
categorization: category_id, post_id
category: id, title

Is this the best way to go?

foreach( $db->categorization() 
    ->where( 'category_id', array('14', '15', '18') )
    ->paged( 10, 1 )
    ->post() as $post ) {

    // do what you want with the data here
}

Any way I can order that by post.date_published?

Essentially what I want is 10 of the newest posts in one or more categories. Ordering by categorization.id DESC is not a reliable method as date_published is subject to changes.

Edit:

Well that was a rather stupid thing to ask, new to ORM :(

This is how to do it:

foreach( $db->categorization() 
    ->where( 'category_id', array('14', '15', '18') )
    ->orderBy('id', 'DESC')
    ->paged( 10, 1 )
    ->post()
    ->orderBy('date_published', 'DESC') as $post ) {

    // do what you want with the data here
}

Don't think this is the right way of going about it. If a post has a recent timestamp in date_published but was added a long time ago (very old id) it would not show up in the query.

Any particular reason that LessQL doesn't implement PSR-2 or PSR-4? And how come there are no visibility controls?

In the website you mention $db->setReferenceKey() but that method doesn't exist. I believe it should be $db->setReference().

Consider the following code:
$db->myTable($id)->anotherTableList()->where($column, $value)->delete();

If $db->myTable($id)->anotherTableList()->where($column, $value) returns nothing ->delete() will then build a DELETE SQL with an empty WHERE clause.

Result.php line 444 returns true and line 446 is executed.
Result.php line 466 runs the basic query and finds no results (which means nothing should be deleted), but when the code goes on running it skips the loop at 469 (because no rows where found) and runs return $result->where( implode( " OR ", $or ) ); at 483, but since $or is empty it produces DELETE FROM anotherTable WHERE which throws an exception.

I guess a very simple workaround would be to add if(empty($or)) $or[] = "0=1"; at line 482, or 'inherit' its where info from the association result, or even skip the delete procedure altogether.

Hi there

I experienced some problem when try to query for data using virtual columns.

For me it seems to (silently) drop the query for the virtual field, so it returns data, but with wrong filter!

For informations about virtual fields please see this post: http://mysqlserverteam.com/indexing-json-documents-via-virtual-columns/

If required I can provide some example code.

best,
Carsten

There is an odd mix of snake_case (table_name, etc) and camelCase (fetchAll, nameList, etc) going on in the code.

Issue happens when trying to use $row->setData($array) to update one of the objects and its relationships.
Consider that I have three tables: first, second and first_second used for the N:N relationship.

First thing I did was set the composite key for the N:N relationship table:
$db->setPrimary('first_second', array('first_id', 'second_id'));

Assume first and second both have two rows each, with their ids being 1 and 2.
Assume first_second has 3 rows:

So here I am, running this code:

$first_one = $db->first(1);
$second_two = $db->second(2);

$data = [
    'value' => 'changed something',
    'first_secondList' => [
        [
            'second' => $second_two,
        ],
    ],
];

$first_one->setData($data);
$first_one->save();

I expected it to break my relationship between first.id = 1 and second.id = 1 since I ommited it. Instead of it LessQL is trying to re-insert the relationship between first.id = 1 and second.id = 2 causing an error, here's the following output found after setting the callback to print the queries:

SELECT * FROM `first` WHERE `id` = '1'
SELECT * FROM `second` WHERE `id` = '2'
UPDATE `first` SET `value` = 'changed something' WHERE `id` = '1'
INSERT INTO `first_second` ( `first_id`, `second_id` ) VALUES ( '1', '2' )

Should I assume that the expected procedure is to delete all relationships first and re-set them?
Shouldn't it at least not try to insert an already existing object?
I got as far as figuring that the first_secondList object is treated as a new one because its $_originalId is empty and save() tries to insert it again after exists() returns false, but I have no idea why that isn't correctly filled.

Thanks in advance.

What causes the error in the title and how to fix?
Regards

Based on this code it looks like LessQL does not have any support for composite primary keys. Is this something you've considered adding?

Hiya,

Love your work. I did similar things by extending pdo, but not to this extent. A couple of questions.... Are you still maintaining this? I need a not orm solution, mine is not up to the job and I am trying to decide if lessql or notorm is the way to go.

I see no use for strict dB abstraction when 98% of the time no one ever changes dB, so no need for deep model abstraction.... Just enough is enough, I hate generating models or creating them manually, pdo layer has got me by but I want relations.

So my question, had far is v1? Will this add type forcing on return values? This is not too hard as I did it with my pdo layer, but it can slow down results so needs to be an option maybe, I saw your answer in issues suggest it will be coming.

Also how up to date are your docs. You have missing things and it can be a little sparse on details.... I.e. I saw a stack overflow asking how to do a specific query, the response was to use pdo directly. This is disjointed, you don't want your guys using two tools for the same job.... So I went and looked and you have a query method which looks to do the job. No mention of this on your site though although you hint at it as blending raw SQL.

Cheers mate, great work, a good quick breath of fresh air in a sea of pointless orm abstraction.

Paul

I'm trying out lessql but every time I try to use it on a table without Primary Key (I work with some MSSQL databases, and even information_schema.tables in MYSQL does not have a Primary Key, it gives me an error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' in xxxxxx

There's no Primary Key in the table, and I can't add one to it, period. I've tried to assign some of the other columns using $db->setPrimary but it still doesn't work. Does this mean lessql is useless for all tables without a Primary Key?

I don't know how to do this, but sometimes I have to add a field to the row.

Like:
DATE_FORMAT(mydatefield,'%Y%m%d%k%i%S') as mydatefield_sformat

Hi Morris,
Tell me how multiple insert row in array should be defined?
$row1=[0=>[field1,$field2,$field3],1=>[field1,$field2,$field3]];
$row2=[[field1,$field2,$field3],[field1,$field2,$field3]];
$db->table('tableName')->insert($row1/$row2?,'batch')->save();

Regards,
Roy

Is it planned? Or can a PR be acceptable?

I should add Result::like and Result::likeNot and manage the processing inside Database::is to be compliant with your logic?

Hi,

It seems like the exists method doesn't work, it always returned false !

$this->lessQL->begin();

$dbRow = $this->lessQL
    ->createRow('partners_tmp', $tempRow);

   if (!$dbRow->exists()) {
       $dbRow->save();
       $this->lessQL->commit();
   } else {
        continue;
   }

And I get the following error :

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: 

Integrity constraint violation: 1062 Duplicate entry '[email protected]' for key 'partner_email'' in 

/var/www/public/rbplus/vendor/morris/lessql/src/LessQL/Database.php:662 Stack trace: #0 /var/www/public/rbplus/vendor/morris/lessql/src/LessQL/Database.php(662): PDOStatement->execute() #1
/var/www/public/rbplus/vendor/morris/lessql/src/LessQL/Database.php(571): LessQL\Database->insertDefault('partners_tmp', Array) #2 
/var/www/public/rbplus/vendor/morris/lessql/src/LessQL/Result.php(411): LessQL\Database->insert('partners_tmp', Array, NULL) #3 
/var/www/public/rbplus/vendor/morris/lessql/src/LessQL/Row.php(304): LessQL\Result->insert(Array) #4 
/var/www/public/rbplus/vendor/morris/lessql/src/LessQL/Row.php(345): LessQL\Row->save(false) #5 
/var/www/public/rbplus/views/organisation/OrganisationController.php(133): LessQL\Row->save() #6
/var/www/public/rbplus/views/organisation/OrganisationController.php(100): OrganisationController->populateTempTab in /var/www/public/rbplus/vendor/morris/lessql/src/LessQL/Database.php on line 662

Thanks for your helps !

Steps to reproduce:
1.Execute $row->update(['number' => 0.00003569]);
2.The actual value saved in the database is 0.000036, not 0.00003569

This is because of using sprintf( "%F", $value ) in \LessQL\Database::quote()

How can I do WHERE ... LIKE queries with LessQL (if that's possible)? Nothing on those in the docs

I noticed when I get a Row instance (from fetch(), say), even numeric columns (in the database) are returned to string. I know this isn't LessQL's fault, but seeing other ORMs do the cast-to-native bit for the developer is this something I missed in LessQL or is it something that's yet to be worked on?

Otherwise enjoying working with LessQL!: